Instructobit
With so many alternative Python MySQL connector packages available on Python, it can sometimes be difficult to figure out the correct way to install the correct package. You may see different sources quoting different packages which you should install but according to the official documentation there is a specific package which should be acquired using pip.
Installing MySQL connector driver for Python 3
The correct package to install for Python 3 is mysql-connector-python which can be found on the Python package index. To install the package simply enter this on your command line:
pip install mysql-connector-python
This will work if you are within a virtual environment, or if you have pip correctly linked to the binary in your system.
If you are having trouble with the above installation command, you can try specifying the correct Python interpreter and using the -m argument to refer to pip:
python3 -m pip install mysql-connector-python
Importing the MySQL connector
The connector should be imported into your Python project like so:
import mysql.connector
If there are no errors after executing the above then you're all good to go!
Basic usage of the Python MySQL connector
In this short example we'll do a quick select and insert into a test table to show how you would go about retrieving data from a table, and commiting data to a table.
import mysql.connector
# Connect to your MySQL server
con = mysql.connector.connect(host='localhost', user='testuser', password='testpass', database='temp')
# To execute queries you will need a cursor, which handles execution and fetching results
cursor = con.cursor()
# First let's try a simple insert
sql = "INSERT INTO test(name, age) VALUES('Bob', 32);"
cursor.execute(sql) # Executes the query within a transaction (so basically like running start transaction; ...)
con.commit() # Actually commits the transaction, note how this uses the connection and not the cursor
# Now let's try a simple select from that same table
sql = "SELECT name, age FROM test;"
cursor.execute(sql)
# This time instead of commiting (because we have no changed) we can just fetch all the results
# generated by the execute
# Other options for fetching results are fetchone() and fetchmany(x: int)
results = cursor.fetchall()
# Will return a list of tuples (eg. [("Bob", 32), ("John", 43)]
print(results)
If you are having trouble installing the connector, would like to ask something or have anything else you'd like to add to this tutorial, feel free to leave a comment below!
