How to create a MySQL connection and run queries with Python3

We will use pymysql to connect to MySQL in Python3. First, you have to install the module using pip or pip3:

pip install -U pymysql pip3 install -U pymysql

Then you can use this code to connect to your database and do a simple query:

import pymysql

conn = pymysql.connect(host='127.0.0.1', unix_socket='/tmp/mysql.sock', user='root', passwd="your_pw", db='your_db')
cur = conn.cursor()
cur.execute("SELECT * FROM your_table")
for r in cur:
    print(r) # print whole row
    print(r[0]) # print first column of result

print(cur.description) # gives a description of the table including all columns

cur.close()
conn.close()

If you don’t want to access columns by index, but by name, you need to pass cursorclass=pymysql.cursors.DictCursor to the connection.

import pymysql

# add cursorclass to access columns by name
conn = pymysql.connect(host='127.0.0.1', unix_socket='/tmp/mysql.sock', user='root', passwd="12345678", db='podcast', cursorclass=pymysql.cursors.DictCursor)
cur = conn.cursor()
cur.execute("SELECT * FROM your_table")
for r in cur:
    print(r["id"]) # now you can access columns by name

cur.close()
conn.close()