Need to connect Python to MySQL?
If so, I’ll show you how to establish such a connection from scratch!
But before we start, here is the generic syntax that you may use to connect Python to MySQL:
import MySQLdb db = MySQLdb.connect(host='your host name', # your host name is often 'localhost' user='your username', passwd='your password', db='your database') cur = db.cursor() # to apply SQL cur.execute('SELECT * FROM your table') for row in cur.fetchall(): print row db.close()
In the next section, I’ll show you how to apply the above syntax using a live example.
How to Connect Python to MySQL from Scratch
Let’s say that you want to create a database in MySQL, where:
- the name of the database is: test_database
- the database will contain a single table called: names_table
- the names_table will have 3 columns with the following information:
First, you’ll need to create the test_database using the following query in MySQL:
CREATE DATABASE test_database
If you’re using MySQL Workbench, you’ll see the database name on the left hand side (under SCHEMAS):
Next, create the names_table using this query:
CREATE TABLE test_database.names_table (First_Name varchar(20), Last_Name varchar(20), Age varchar(3))
Your new table would also appear on the left hand side (under SCHEMAS):
Finally, add the records into the names_table using the following query:
INSERT INTO test_database.names_table (First_Name, Last_Name, Age) VALUES ('Jon', 'Snow',22), ('Maria', 'Smith',34), ('Emma', 'Jones',51), ('Bill', 'Yu',63), ('Jack', 'Green',27)
To check that the data is indeed stored in the names_table, you can run this simple SELECT query:
SELECT * FROM test_database.names_table
Once you run the above query, you would see:
So now that you have the database ready, and all the records are stored in the names_table, you’ll need to install MySQLdb to be used in Python.
If you’re using Windows, you can download and install MySQL for Python. Make sure that the version you download match with the Python version. In our example, the Python version is 2.7:
Next, obtain your host name, user and password.
In most cases, your host name would be ‘localhost‘ for local instances.
You can see the user name in MySQL Workbench, by going to the Database tab, and then selecting Manage Connections…
Then, look for the Username for you instance. In my case, the Username is root, while my password is 1q2w3e4r
Can we now connect Python to MySQL?
Absolutely! You should have all the information needed to establish this connection.
For our example, the following information can be entered in Python 2.7 (you’ll need to modify the below code to correspond to your connection information):
import MySQLdb db = MySQLdb.connect(host='localhost', user='root', passwd='1q2w3e4r', db='test_database') cur = db.cursor() cur.execute('SELECT * FROM test_database.names_table') for row in cur.fetchall(): print row db.close()
Press F5 to run the code in Python, and you’ll get the exact same results as stored in the names_table in MySQL:
Once you established a connection between Python and MySQL, you may start applying SQL in Python.
You may also want to check the following source that explains how to insert values into MySQL table using Python.
Finally, the tutorials below explain how to establish a connection between Python and: