How to connect python to MySQL server
An application without a database is like a human without a memory. If you have created a python app (any python program) and you don’t know how to connect that app with a database, so that you can store and retrieve the data that your app produces or if you are just curious to know that how it works, then I will show you how to do that step-by-step. I assume that you have some basic knowledge of python programming and MySQL or any other DBMS.
Suppose you want to create a python app that authenticates a user. If a user is a registered user and he tries to login, the app will display a message ‘successful login’ on the terminal. If the user is not registered and tries to login, the app will display a message ‘login failed’ on the terminal.
In order to do that, first you need a database which stores the data about your registered users. when a user attempts a login, the app will get the account credentials (username and password) from the user, generate an SQL query using those credentials, and query the database. If a user with that username and password exists in the database, login will be successful, otherwise not.
suppose, the ‘myusers’ table below is the database table in your MySQL server that holds the data of your registered users.
In order to connect your python app with the MySQL server (or the database), you need to import a module called mysql.connector in your python file. This module is not available by default, but you can get it by installing a python package called mysql-connector-python.
Once you have imported the required module in your python file, you can then make a connection with the database, by using a function called connect() in the mysql.connector module. The function takes some arguments such as hostname (the server machine running MySQL server), the user of the database that you want to connect to, the user’s password, and the database name.
Suppose you have a MySQL server running on your local machine, you want to connect to the root user, and your database name is ‘python_app_db’. Then you will make a connection like this
conn = mysql.connector.connect(host = ’localhost’, user = ’root’, passwd = ‘***’, database = ‘python_app_db’)
The connect() function returns a connection object that you need to store in some variable (because you need the connection object).
Then you have to create a cursor object. you need this object to execute SQL queries from python. And it’s very simple to create this object. just invoke a method called cursor() on conn (the connection object) like this
mycursor = conn.cursor()
Now mycursor is our cursor object, which can execute SQL queries by invoking execute() method. The data about the registered users are present in a table called ‘myusers’ (fig 1). To select all the records in ‘myusers’ from your python file, you will invoke execute() method on mycursor and pass the SQL query to it. like this
mycursor.execute(“select * from myusers”)
Note: you don’t need to add a semicolon at the end of the query in python.
The cursor object (mycursor) will execute the query. Now, to read the results of the query mycursor has just executed, you can invoke fetch*() methods on mycursor or you can also iterate on mycursor and print (or do something else) the result.
fetchall()
If you invoke this method on mycursor, it reads all the results that the above select query has produced. The return of this method is a list that contains tuples. here, a tuple corresponds to one row in your database table (myuser). The fetch*() methods reads the result produced by the last executed query. This means that if you have executed multiple queries, the fetch*() methods will read the result of the last executed query. If you use fetchall() method to read data from mycursor which has executed the above select query on ‘myusers’ table, then it will read all the five rows in that table.
fetchone()
This method is used in the same way as fetchall() is used. It also reads the result from mycursor but the difference is that it only reads one row of the entire result. In our case, if we use fetchone() to read mycursor, then it will only read the first row (the record of ‘Tom’).
fetchmany()
This is just like the other fetch*() methods but here you have a choice to read as many rows as you want from the result of the query. Suppose, we execute our favourite ‘select’ query again to select all the rows of the ‘myusers’. And then we want to read only the top 3 rows in the table. Here, we will use fetchmany() and pass it the argument of ‘3’. like this
rows = mycursor.fetchmany(3)
now, the rows variable will contain a list that contains the first three rows in ‘myusers’ table.
As I have already said that the return of fetch*() is a list of tuples. This list is empty in two cases. one is when you execute a query that is not supposed to return anything (i.e., insert, delete etc). The second is when the selection criteria is not met. This is the case when the user who is trying to login is not a registered user. In this case, the select query will produce no result and fetchall() method will return an empty list.
So, on the basis of what fetchall() returns, you can authenticate the user. If the return is an empty list, the user is not registered and hence login fails. If the list is a non-empty list, the user is registered and hence the login succeeds.
The following image shows the complete python code to authenticate a user. please make sure that your MySQL server is running before you run your python code.
When the above code is executed, it will ask the user to give his username and password first. Then it will incorporate those inputs into an SQL query and send it to the MySQL server which is connected to the app. The server will return a row if the user exists in the ‘myusers’ table and it will return nothing if the user doesn’t exist in the table. On that basis, the app prints the message ‘success’ or ‘failure’ on the terminal.
Extra Tip: Use the above-described way of passing the user input to the database server (fig 2) because this way of sending a user input to the database server by passing it to the execute() method is secure because it prevents sql injection attack. If you directly incorporate the user input into the sql string, it can contain malicious code and can compromise your database.
Note: If you have followed this tutorial to the end, in addition to authenticate a user, you should also be able to create a MYSQL database from your python app and also provide a feature to register in your app, to your new users.
