Don‘t you hate it when you close a file just to, later on, realize that you forgot to save it? It‘s always an unpleasant feeling, but it does happen from time to time, regardless of your experience. This is what happens when you don‘t connect your project to a database.
In this article I’ll show you how to connect Python to MySQL and how to create your first database.
Well, you already have your project up and running. Why would you need a database connected to it?
Unless you don’t need your project to save any info, you don’t really need it. A database goal is to store information, so we can later on use it instead of restarting everything from the ground again.
Let’s do a step-by-step guide of how to connect Python to Mysql being able to create our first database using only Python.
A database stores data in a organised way, so you can search and retrieve it afterwards.
A database must contain at least one table. It looks pretty much like a spreadsheet: It’s basically a set of rows and columns.
Every row has the same columns, and each column has its data. If it may help, you can think of these tables as Excel tables.
The data can be inserted, read, updated and deleted from a table. Although we often say ‘inserted’ in the database, the most correct term is ‘created’; And, with these operations, we have our lovely acronym: CRUD (Create, Read, Update and Delete).
A relational database organizes it’s data in tables, and binds them, based on defined relations. These relations allow data to be retrieved and combined, from one or more tables in just one query.
A database will be extremely useful if you have followed my tutorial, where I showed how to create a login page from scratch.
But this was just a load of words. If you truly wish to understand the relational database, you must create your own. So, let’s go.
The first thing you need to do, if you haven’t already, is to go to MySQL’s site, then download and install the latest version of MySQL into your computer. The version I’m using here is MySQL Community Server 8.0.19
I also strongly advise that you install a graphical interface to see your database, like MySQLWorkbench or phpMyAdmin.
When you’re done, just check if your database is up and running with any of the tools above, using the login and password you created while setting it up.
Now, let’s connect Python to MySQL, and create our very first database.
By default, Python doesn’t come with this connector installed. You must first download it. A connector is nothing more than a module, that helps the communication with Python to MySQL. There are at least 5 of these available, but here we are just going to use the one given by MySQL itself.
To install the connector, the simplest (and most advised) way is by using pip (the Package Installer for Python). I already mentioned pip in another post where How to create a login page with Python . If you already have pip installed, just run the code below to install the Python to MySQL Connector.
pip install mysql-connector-python
It’s very easy and simple to connect Python to MySQL using our recently installed Connector.
First things first, we need to import our Connector. Then, we use the function connect() with the credentials you generated during MySQL setup. The host can either be localhost or 127.0.0.1. They are synonyms.
In this test we are just gonna open up a connection and close it afterwards, with the function close().
import mysql.connector
cnx = mysql.connector.connect(user='root', password='********', host='127.0.0.1')
cnx.close()
Copy and paste the code above, then save it on a file called connection.py (or any other name you want). Open up a window in your terminal, and go to the folder of the file you just created. Then just run the command: python connection.py
If the code above runs without any problems, it means that it worked and you can already use MySQL.
Now it‘s time to create our very first database. Let‘s just change the code above a bit. Let’s create an instance of a MySQLCursor class object instead of just closing the connection. The object interacts with the MySQL serve using the object MySQLConnection.
To create the cursor, just use the method cursor() on an already existing connection. Now, we write our SQL Query and run it, using the method execute() in the object mycursor, from the connection we just made.
Below, is the SQL command to create a Database:
CREATE DATABASE mydatabase
Putting it all together, we have the following code:
import mysql.connector
cnx = mysql.connector.connect(user='root', password='********', host='127.0.0.1')
mycursor = cnx.cursor()
mycursor.execute("CREATE DATABASE mydatabase")
Just run this file with the same command I showed you at the start: python connection.py. If it also runs without any problems, Congratulations! You just created your first database with Python.
To check on your recently created database, you can use one of the tools I’ve mentioned before. While using MySQLWorkbench, I’ve got the following results:
Now, a recap of everything we saw:
In this article we learned how to connect Python to MySQL using Python MySQL connector for that,
Well, I hope this made clear as to why you should have a database in your project and how simple it is to create a connection.
Until next time.
Leave a Reply