Python is a programming language often used in developing desktop and web applications. Whereas MySQL is a relational database management system. Where there are desktop or web applications, databases are bound to come up at some point, and that brings us to the Python MySQL library. MySQL has now become a industry standard in the database world.
SQL is the most popular language for adding, accessing and managing content in a database. It is well known for its quick processing, reliability, flexibility and easy to understand syntax.
Note that MySQL and SQL are not the same. SQL is simply the language that MySQL uses.
Setting up Python MySQL
Setting up MySQL is harder than installing a regular python library. To be able to use MySQL with python there are two perquisites.
- You must have a MySQL database set up on your computer
- You must have the MySQL connector library installed in python. This library is used to communicate between the database and python.
The first perquisite is the difficult part.
We’ll be installing the database using WAMP stack. (XAMP stack is another such option) You can download it from this link. WAMP stack will also download for us phpmyadmin which provides a web based front-end to access the database.
In order for a database to exist there needs to be a server. What WAMP stack does is to create a server on your computer, which we refer to as the local host.
Though due to the 24/7 need of a server to be online, we do not use our own computers as servers, instead purchasing the services of companies with specially designed computers that can run 24/7.
During this process you will have to create an account, giving both a username and password. It is of utmost importance that you do not forget this password. There is no “forget password” option here, if you forget your password, you will have no choice but to re-install the whole thing. I highly recommend writing the password down somewhere.
Testing your Database connection
First install the library mysql.connector library. We’ll be using this to communicate between the python code and the database. You’ll likely have to download this library separately as it does not come inbuilt with python.
You can do so by using the command prompt to navigate to the folder where your python.exe is located and then using the following command.
pip install mysql.connectorOnce you’ve you accomplished this, use the following code to check whether the connection has been accomplished. If the code runs without any error, and an object is returned, your connection has been established.
import mysql.connector
check = mysql.connector.connect(
host= 'localhost',
user = 'root',
password = '12345678')
print(check)
Make sure you have entered the correct user name and password!
You can add further parameters into the connect function such as database name. However, we’re assuming for now that this is a brand new installation and there are no pre-existing databases.
Once you’ve reached this point, you’re now ready to begin learning Python MySQL See the list below for a list of all the various functions in MySQL.
You can also check out the parent article of this section here: Setting up a Database. It goes into more detail and breaks down the steps further.
MySQL cursor
To use MySQL in python, it is necessary to first create this cursor object. The cursor object is what you use to execute commands in MySQL. The cursor object is created using the returned MySQL object from when your connection was established.
The execute() function is used with the cursor object to carry out the required command.
import mysql.connector
db = mysql.connector.connect(
host= 'localhost',
user = 'root',
password = '12345678')
cursor = db.cursor()
cursor.execute("SQL command to be executed")
MySQL Commands
| Statement | Description |
|---|---|
| CREATE DATABASE | Used to Create a new Database |
| CREATE TABLE | Used to Create a table within a Database |
| INSERT | Used to insert a new record into a table |
| SELECT | Used to select certain or all records from a table |
| WHERE | Used to specify certain conditions to be met while executing commands. |
| DELETE FROM | Used to Delete records from a table |
| UPDATE | Used to Update the values or records |
| ORDER BY | Used to Order the records in a table |
| DROP TABLE | Used to remove tables |
| JOIN | Used to join two tables together when performing tasks. |
Learning how to interface with Databases is a great tool. However, you should also properly understand what databases are, how relational databases work and how they are designed (so you can take advantage of their features).
Refer to this compilation of great books on Databases to learn more!
This marks the end of our Python MySQL Tutorial. Let us know if you have any suggestions or contributions to make. Any contributions to help improve CodersLegacy will be greatly appreciated.