 Welcome to the course on Python Interfacing with MySQL. I am Mayur Sonar and in this course we are going to see variety of topics. So in this course we are going to cover following things. First is the introduction to the database programming in which we are going to talk about database programming. Then we will cover why Python is used for database programming in which we learn the different Python features which makes it convenient to use with database. Next we are going to see the different SQL connectors. So to interface Python with various databases we require various SQL connectors. So we will cover that also. After that we are also going to study about the cursor object which is generally used in Python for database operation. So basically we will focus on the MySQL connector, how MySQL is installed and then how the MySQL cursor object is used. And lastly we will cover the most important operations called as a CRUD operations which stands for create, read, update and delete operations which are performed on the database. So once again welcome all of you in this course and hopefully you will understand how to do the Python interfacing with MySQL. So thank you and looking forward for your joining. Thank you. So in this we are going to discuss about the database concepts, the introduction part and why we need databases. So what is a database? So database is nothing but an organized collection of data. When I say organized collection of data it means that it is generally stored into some kind of organized form. Generally when it comes to relational databases the data is stored into a tabular form or in the table form where there will be rows and columns. Rows generally indicates the records whereas the columns generally indicate the type of values which are being inserted. So you can consider any company query whether it is a large or small in the world of globalization all these companies today are storing their data into the database. So they can use various kinds of databases. Here in the diagram you are able to see as we are considering Python as in programming language. So we are able to see the generalized form of interface of Python with the SQL databases. So here we can consider that there is some kind of a Python code which is written and this code or the application is trying to interact with the database. Here we are taking an example of a MySQL database. So there are number of databases but we can consider the MySQL database in our case. So by using a query language by using a query language through the applications we are able to get the data from the database. So there will be a request which is coming from the Python application which will go to the database and database in return or in response will send the data to the Python application. So for that to write a query we use SQL. So SQL is what? SQL is a structured query language which is a query language and it is not a database. So there are various examples of databases. So there are various examples of databases. Oracle, MySQL, MongoDB, PostgreSQL, SQL Server, DB2, etc. So there are a lot of databases in the market. One of the most popular databases is Oracle which is highly used by most of the companies. The second popular database is the MySQL database and that one we are going to focus on. There are other databases also MongoDB, PostgreSQL, generally used in the Linux operating system, SQL Server which is created by Microsoft and DB2 which is the database created by IBM. So there are a lot of databases but for our course we are going to consider MySQL database. The Python standard for database interfaces is the Python DB API. So when you want to make an interface or when you want to interface Python application with the database there are various APIs that is the application programming interface is that we need to use and the standard for the database interface is the Python DB API. So hopefully you have understood what is a database, why we are using the databases, what is SQL which are the different database examples and what is the standard database interface that we are going to use for interfacing. So thank you for watching. We will meet in the next video. Hello everyone. Welcome again. So in this video we are going to talk about why Python is used for database programming. So as we are trying to learn Python interfacing with MySQL should first address a question, a very important question that why should we use Python for database programming. So I have listed some important advantages of using the Python for database programming. Let us see them one by one. So first one is programming is more efficient and faster compared to other languages. So previously you may have heard about the languages programming languages such as C, C++ and Java where programming was easy but it was as compared to Python it was not faster as you have learned some basics about Python that we are able to use the idle we are able to download the Python shell and we are able to execute one line of program also whereas it was not possible in C, C++ and Java where to write any program we need to create or we need to import various files we need to write the main function then compile it and then execute it whereas in Python you can write one line at a time and you can execute it immediately by using the Python shell. So automatically Python becomes more faster than the other languages as it is more faster it is also efficient to write the programs in Python so that is the first advantage the second advantage is portability of the Python programs so Python programs are portable we are able to copy or port the programs from one operating system to another operating system or from one computer to another computer it also supports the platform independent program development so as it is portable it is also platform independent so you can develop it on any platform like windows linux macOS you can use any of the operating systems and you are still able to write programs using Python the next one is Python supports SQL Curses which is one of the most important advantage so as previously said SQL is nothing but the structured query language and it has a concept of cursors and these cursors are being supported by Python and as we are going to see in the coming videos we'll be using these cursors to perform different operations on the Python Python also takes care of open and close of connections so unlike other programming languages where we need to explicitly open and close the connection in Python it can do it automatically also it can open the connection and close the connections so that is one of the biggest advantage of using the Python. Python supports the relational database systems as you are aware that there are various database systems there is a rdbms that is nothing but the relational database system then there is a o rdbms that is object relational database system and there is also o odbms that is object oriented database management system but out of all these three the most famous or the mostly used database system is the relational database systems and Python supports the relational database systems so in the relational database systems we are having we are storing the data into a table form the table is made up of rows and columns of course we will be learning that in the coming videos but that is one of the biggest advantage of using the Python and the last but not the least porting of data from one dbms to other is easily possible as it supports large range of epis for various databases so we can take an example that we have already seen that there are various databases which are available in the market oracle mysql sql server db2 and so on and so on and python is actually supporting all these databases so let us take an example that if i'm having one application which is using the oracle database and the other application which is using the mysql database so if i want to import some data from oracle to mysql so it is generally very difficult but when you are using python which is supporting all these databases it is easy to port the data from oracle to mysql so that is again a very biggest advantage provided by python and because of all these things python is very suitable for database programming hopefully you have understood this thank you everyone welcome again to the course in this video we will talk about sql connectors in brief so what is mean by sql connectors so structured query language connectors are used for retrieving the data from the database as we have already learned that python supports a wide range of databases which includes oracle mysql sql server db2 etc each database is unique to access each database we need to use a database api so when it comes to python we have to download a separate database api module for each database we need to access so let us take an example that if i am using oracle so i need to download a oracle python oracle db api to access the oracle database same goes for mysql database so if i want to work with the mysql database i need to download python mysql db api so through the mysql db api i am able to access the mysql database so please remember this thing that for each of the database you want to use with python you need to install or download a separate database api and as we are going to use the mysql database in our course will be downloading a mysql db api of python so next question comes what these apis that is the application programming interfaces contains so these apis includes following steps so importing the api module so for each of the database we need to import the api module it contains acquiring a connection with the database so through this api we are able to connect to the database by using this api the next thing that we are able to do is we can issue sql statements and stored procedures so through this api let us consider mysql db api we are using so we are able to write the sql statements or we are able to execute the stored procedures and the important part is this db apis contains closing the connection so generally we have to use these apis if we want to perform the different operations on the database so please remember that for each of the database python has a different db api thank you again for watching hello everyone welcome back in this video we are going to talk about the mysql connector in the previous video we have seen that there are various sql connectors available in python as python supports variety of databases one of them is mysql so let us try to learn how mysql connector works how to install it so the mysql connector enables python programs to access mysql database so this mysql connector is enabling us to access the mysql database so if you are using a mysql database so you need to have this mysql connector similarly if you want to use the oracle database you need to install the oracle connector and so on so what are the steps to use the mysql connector first thing which is needed is we need to download the mysql api or the exe file and install it so i have just put in a url over here which i am going to show you so this is an official website to download the mysql api which is the this is the official website for the mysql to install as you are able to see this is the developer dot mysql dot com under which we are coming into downloads and the connectors for python so first thing you need to install is you need to see that which installer is required so depending on the operating system that you are using you need to select the connectors as you are able to see for all the operating systems they have provided the connectors you are having ubuntu debian susai redhat fedora oracle mac os we are going to use the microsoft windows under that it is you can just select the os version whether it is a 64 bit or 32 bit so depending on that also you are able to select so most of the time you are having the 64 bit version so once you come on to this page you need to select the operating system you need to need to select the os version and then it will show you the msi that is the microsoft installer and this you need to download once it is downloaded you have to just click on that and it will install the connector for python so once it is downloaded you need to install the mysql python connector by using opening the command prompt either you can do it by using the graphical user interface which is provided or you can go to the command prompt and execute the following command that is pip install mysql connector so this is also going to install the mysql connector for you and now connect mysql server using the python so once it is done you need to start the mysql server and connect it using the python so write the python statement in python shell so whatever id you are using for python you need to go to the shell and write down this command that is import mysql dot connector if there is no error message coming after this then it is clear that your mysql connector is properly installed so this is generally the way of getting the mysql connector but before that we need to install the mysql always remember that before installing this mysql connector you need to install the mysql so thank you for watching we will make in the next video hello everyone welcome back in this video we will talk about how to establish a connection but before that let us see what we need to do so before establishing the connection there must be a mysql installed on the system also database and a table already created so there are two requirements that we need to do first is we need to install mysql if you have not seen the previous video just see the previous video and you will know how to install a mysql and then we also need to create a database and a table in that mysql so let me show you how that can be created first is you need to start the mysql this is a command prompt where we are you are able to see that i have you need to go to the mysql directory which which is generally stored into c drive program files mysql in that you need to go to the bin folder once you go into the bin folder uh once you have given that path you need to type in this command that is mysql d dash dash console so this is going to start your server mysql server for you and it will show a window like this you need to keep this window open or start uh keep the server running once it is running you need to open another command prompt window by using the cmd command and in that again you need to go to the path where the mysql is installed in that you need to go to the bin directory yes after that you need to write down this particular command that is mysql dash u root dash p so this is generally what this is generally it's a mysql command followed by u which is for user and the generally the user which is created installing mysql is generally named as root you can change of course you can give some another name while installing it as well as dash p represents the password so it will ask you for the password the password is again the same which you have given at the time of installation so at the time of installation my user was root and I have also created with a password as one two three four so I have given a very simple password you can change these user names as well as password and once it is done you now you are able to see that there is a mysql prompt which is created yes so here we are actually creating the we need to create the database as well as the table but we can see that how many already the databases are present by using the command as show databases to run it we are using a semicolon and if you do that so as you are able to see that I have written a command called as a show databases and it is showing all the databases which are currently present in that mysql that is information schema mysql performance schema and sys so we need to create our own user defined database to create our own table so the command which is used to create a database is create database sorry and you can give as let us say I am giving the name as test and ended with semicolon and you just have to press the enter so as you are able to see the query is okay there are no errors one row affected so we have created successfully a database named as test let us see whether it is visible or not again I am using the same command show databases so as you are able to see over here so our new test database is created in mysql now we need to use this database remember that we need to create the database or tables under this test so the command for that is use test so it is going to use or use the test database for us so database is changed as you are able to see this is running successfully and then you are getting the message as database changed so now whatever tables we want to create will be created under this test database so under this now we will see how to create the tables so a very simple command is there create table and you can just create a table called as a student and if you are able to remember the SQL concepts so you can just use it and we will just create two or three columns under this so the command for it is create table I am sorry I have written it wrongly can I change this I don't think so so it is going to give us an error but let me let me just let us create a row number which is having the data type as int then let us create a name which is having wire care suppose 20 so I am creating a trying to create a database I think I am getting some errors I have just erased it and let me try it again so create table student where I am trying to create row number as int and name as wire care 20 and if you try to run this so yes query okay zero rows affected so we have successfully created a table named as student which contains two columns one is the row number and second one is the name so you can see this you can see the description of this table by using the DESC command so as you are able to see there are two fields which are there in this particular table that is row number and name row number is having the type as int name is having the type as wire care 20 this is something that we want to learn null these both columns are null so null value can be inserted over here by default they are also null there is no primary key defined and there is no extra information so this is just to show you that how to create the database in my SQL and how to create the table under that particular database so hopefully you have understood this let us meet in the next video hello everyone welcome back so in this video we are going to talk about the cursor object the object or the cursors that we need to create to do all the SQL operations so basically we have seen that there are four basic operations that we are performing on the databases that is to create a database to read from the database to update the database and to delete the database or the tables inside it yes so in python we need a cursor object so in the previous video we have seen how to establish a connection in this video let us talk about the cursor objects so the my SQL cursor class instantiates objects that can execute operations such as SQL statement so whatever SQL statements you would need to write you need to write through the cursor object of python so how to create this cursor object so very simple steps are there first we need to import the my SQL connector so in the previous video also we have talked that when you import this my SQL connector if it is properly installed then you are not getting any error messages so first thing is you need to import the my SQL dot connector which is the connector for connecting python to the my SQL database once it is done you need to create an object called as a my db or you can give any other name is equal to then you are writing the my SQL dot connector dot connect method you are calling this my SQL dot connector dot connect method to connect to the database so if you are having the database which is created on your local machine so generally there are three parameters it is taking first one is the host host is in our case if you are using the or if you are having the my SQL stored on the same machine so a local host is the name that you are providing so local isn't a host is nothing but the same pc that we are using so I have installed the my SQL on the same pc and and that is why I am writing as local host comma user user is the second parameter that we are passing to the connect method here you need to give the name username of the my SQL so as we have seen in the previous video that the name that we have given was the root and then you need to give the password that is pass wd is the third parameter and for that you need to provide that password which you have given while installing the my SQL so I have told you previously also that I have used a very simple password one two three four and we have seen that before once it is done so it will connect so this connect method will connect to the my SQL by using these three parameters that is the host username and password once that is done you need to create a my cursor object so here we have created a my cursor object by using my db so you are passing this connection object and you are calling the cursor on the same connection object and then you need to create a my cursor dot execute so either if you have created a database earlier so you can just use the same or you can use this particular command also that my cursor dot execute so this will execute this particular query that is create database if not exist test so this particular command is used to create a database called as a test we have already created a database called as a test in the my SQL but from python also you are able to create a database using this command so what this command does it will create the database test for you but when when if it is not existing so if it is existing so this particular query will not have any effect the test database will remain as it is it is not overwritten or something like that so please remember that this command is just to ensure that if there is no test database then it will create a database test for you then as previously we have seen here also through this also we are able to see all the databases which are present in the mysql so the command for that is for my cursor dot execute show the databases so as we have seen already there can be number of databases which are present in mysql so this command will show you all the databases to display all these databases you need to use the for loop for x in my cursor so here number of rows will be coming and this rows will be printed one by one by using the print x yes so here through this cursor object what you can do is you can just check for the uh connection you can check for the database creation and you can just see all the databases which are present in the mysql so thank you for watching hello everyone welcome back so in the previous video we have seen how to create a cursor object and how this cursor object in python can be used to write various sql queries so in this video we will talk about how to create a table through a python yes we have already seen how to create a table in mysql by going to the command prompt and writing the command so let us see how to create a table through python so the first step is we must open the database before table creation so so to perform any operation on database in python we should always open the database before doing or before writing any sql query so the steps now you should be able to remember always the first step is to import the mysql dot connector so we have talked many times about this followed by you are creating an object for the connection that is mydb mysql dot connector dot connect host here something it changed as we are seeing how to create a table see now we are passing the four parameters here one is the host second one is the user third one is the password and the fourth one is the database so here what we are changing is as we need to create a table we need to create a table under a particular database and to create a table under a particular database we need to provide the database name also so as we have seen we have created a test database previously so let us see again this host is local host as we are using the same machine or for storing the database then user is nothing but the username username of mysql that is root password while installing the mysql i have given as one two three four and then in the previous video we have seen that we have created a database called as test so now what will happen after this is whatever table we are going to create will be created under the database test next is to create a cursor object so we have created a cursor object called as a my cursor and we are just calling that so mydb.cursor then to execute any SQL statement we are using this my cursor object so my cursor.execute execute is going to execute this particular query and the query is really simple so create table followed by the name of the table that you want to give so here in this case we have given that as student so create table student followed by we are creating the two columns under it roll number which is having the data type as int three which is the size of that then as you are able to see something it changed over here we have written it as a primary key so while creating the table it will create the roll number which will have the integer values and which is also a primary key of that particular table and the second column that we are creating is the name which is having the data type as where care 20 so once this my cursor.execute will run this particular table named as student will be created in the mysql which is having two columns as roll number and name and roll number is nothing but a primary key so in the next video we will see how to display the records of the table thank you hello everyone welcome back in this video we will talk about how to display the records of a table so in the previous video we have seen that how to create a student table by using python so here we will see that if some records are inserted how to display the records of the table so mysql cursor is providing a fetch all method as the name itself is suggesting it is going to fetch all the rows from the table for you so this method is going to return all the rows which are present in the table so the method fetches all or all remaining so whatever is there so either it can return all or if you have previously fetched some of the rows so it will fetch all the remaining rows of a query result set and returns a list of tuples so this is very important part so it is returning a list of tuples so this as you have already known that there are various data types which are provided by python one is the number second one is the string third is the list then dictionaries tuples sets so here when we try to take all the records from the table so it is returning a list of tuples so that you have to remember so it's a list and if there are no more rows available in the table so it returns an empty list yes so in both cases it is going to return the list but if there are some records so it is going to return the list of tuples in the tuple format it will come and otherwise it will return an empty list so how to do that let us see the commands that we are writing on the shell so this is a this is a common command that we are writing everywhere import mysql.connector then all the other things are same localhost username is root password is 1234 and database is test yes and under this we have created a student table if you are able to remember so my cursor then you are creating the cursor object then my cursor.execute so this is one of the ways how you are writing the or executing the queries and here you are just simply writing as select star from students so if you are able to remember the SQL basics so select star from student is going to return you all the rows which are present in the student table now once you have executed this then how to take all these rows so now you are creating one more object called as a my records is equal to my cursor dot fetch all so fetch all method is going to fetch all the rows from the table and that will be stored into my records and to display them one by one if you want to display them one by one you are using a for loop for x in my records so it will take one row at a time from the my records and you can just use the print command print x so this is going to print one row at a time for you from the student table so this is a general way of displaying all the records of a table thank you hello everyone welcome back so we have just started with the crud operations so previously we have seen how to create a database and a table by python in my sql by using python in my sql we have also seen in the previous video that how to display the records of the table or we can simply call it as reading from the table and now we are focusing on how to update the record of a table so to update the record of a table one of the important condition is there should be some records in the table which is already present if the records are already present let us say that you are having a record of a student who is having the roll number as one and his name is akash and suppose now a new person is coming and he is in having the name as amit and you want to assign roll number one to the amit then what you can do is you can just update the record of a table so that is one example so how to do that by using python through python so again the some of the steps are common that you need to import the mysql.connector then you need to do the connection by using the host username password and database then you need to create a mycursor object so mycursor is equal to mydb.cursor and then you need to execute a command sql command by using mycursor.execute so here i have taken another example actually we are not having any marks column in the student table but what we can do is we can just add a new column by using the alter table command if you are able to remember the alter table from sql so by using the alter table command you are able to alter the table and you can add the new column as marks and here what we are trying to do is we are updating a student table so the command for updating is to update followed by table name that is student set is the keyword marks marks is the column name suppose we are having some kind of a marks column and you are assigning the marks as 99 where roll number is equal to 2 so here we are trying to update the record of a roll number to student and his marks are set to 99 so this is how you are able to update the record of a table and to see the change what you can do is you can again fetch all the records from the table by using the fetchall method and you can just print it and see whether updation has been done or not thank you hello everyone welcome back so one of the last operation that we need to perform on the database is the delete operation so out of the four important operations that I have told you out of the four credit operation that was create a read update and delete yes so most of the time it happens that you there are we are number of records which are present in the database and you need to read a particular record from the database or from the table so for that you are using a delete command in sql so if you want to delete a particular row by using a python let us see how it is so import mysql.connector this is all these are all our common statements then mydb mysql.connector.connect again you are using four of the important parameters localhost then user is username is root password is 1234 and databases test and then you are using the mycursor is equal to mydb.cursor and then you are using the mycursor.execute command and here what you are trying to do is you are trying to delete a particular record from the table so delete from student student is nothing but our table name where row number is equal to one so what will happen after this particular command so it is going to delete the entry from the student table where row number is equal to one so to check this again what you need to do is you need to use the fetch all method and in that cursor you need to write down the simple command select star from student and it will display you all the records which are remaining and it will definitely show you that this particular entry is deleted from the table where row number was equal to one so this is how generally you are able to use python with the databases as you are able to see in our course that it is really easy to use the mysql database with python and python also makes it very easier to write the programs or to do the operations on the database so hopefully you have understood how to write programs using python and how to interface python with mysql and how to perform different operations using python hopefully you have liked this course if you have liked this course please write a review for it and let me know that what changes we can make in this course so thank you for attending this particular course