How to create a MySQL-Python connector, cursor, and run CRUD commands with MySQL

If you need a straightforward tutorial to get MySQL-Python connected and working to build a simple CRUD app, this is for you.

I already have MySql 8.0 installed and it runs automatically on startup. I configured it to connect with a password.
I’m using Python 2.7.16, and the MySQL connector is version 8.

On the command line:

1. Start Python

python 

2. Import the aforementioned (downloaded and hopefully functional) MySQL-Python connector

>>> import mysql.connector

3. Create a connector object, which is the connector + connect command, which takes 4 arguments, (3 if you don’t want to connect to a DB right off the bat), the user, password, host, and database.

>>> connector_object = mysql.connector.connect(user=’root’, password=’mypassword’, host=’localhost’, database=’mysql_python’)

4. Print out the connector object to see if it actually created. You should see a return value of the object.

>>> print connector_object
<mysql.connector.connection_cext.CMySQLConnection object at 0x10394df50>

Woot! There’s the object. That’s all done.

Now, a cursor object, the Hermes of this whole shebang. They carry messages (commands) written Python to the MySQL database.

To create a cursor:

1. Call the cursor command (hey, that’s handy) on your connector object and store it in a variable.

>>> mycursor=connector_object.cursor()

2. Make sure it connected, fun fact, the cursor object tells us what’s been executed, in this case, nothing so far

>>> print mycursor
CMySQLCursor: (Nothing executed yet)

Yay! Simple. The cursor object exists. Now I can run commands to CRUD data in MySQL, utilizing the cursor.

LET’S CRUD.

Ok, we are in the mysql_python database, since I connected to it when I created my connection object above. If I didn’t select or create a database yet, here is when I would do so.

But, since I did, let's create a table in my database, ‘mysql_python’.

To create a table:

1. Call the execute command on our cursor object, which takes an argument of SQL commands.

>>> mycursor.execute(“””CREATE TABLE customers
… (
… pid int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
… uuid varchar(56) DEFAULT NULL,
… username varchar(255) DEFAULT NULL,
… name varchar(255) DEFAULT NULL,
… email varchar(255) DEFAULT NULL,
… age int(11) DEFAULT NULL,
… lastseen int(11) DEFAULT NULL,
… phone varchar(20) DEFAULT NUll)”””)

This may be different SQL syntax depending on which version of MySQL you are running.

Then I used this bit of interesting code I found, where you can run the SQL command SHOW TABLES, and a Python command “fetchall” on the cursor, to make sure a table was created. Its a quick command line check to see that a table was created, it will show up as a string object inside of an array. It’s clunky, but for now, it gives me some command line peace of mind. I also like to check on MySQL Workbench that my tables are showing up, but I think doing it via the command line trains me to think more in-depth about what is going on and integrate logical thinking (aka, ok, well, I want to print the tables on my screen after they are shown via SQL, but I have to do that through the cursor because I’m using the cursor to get messages back and forth, oh, now I see how this is all integrating, etc, etc.) I’m not breaking this down fully, but this isn’t meant to be in-depth right now.

>>> mycursor.execute(“SHOW TABLES”)
>>> print(mycursor.fetchall())
[(u’users’,)]

woot! Lets put data in our user table.

So, by now, I realize I must run the <execute> command on my cursor object and then put the SQL statements as the argument every time I wish to CRUD data. So, let’s create something in this table.

>>> mycursor.execute("""INSERT INTO customers VALUES (null, u'123456', u'j_son', u'jason', u'jason@aol.com', 23, 32, u'3872981234')""")

Then, and this is VERY IMPORTANT, you must save the changes into your database via the connector object!

>>> connector_object.commit

Hey, how about doing some of that fancy-dancy READ of the CRUD actions?

Let’s do a basic read of everything in your table, then print out the results using the fetchall() command on your cursor.

>>>mycursor.execute("SELECT * FROM customers")
>>>print(mycursor.fetchall())
[(1, u'123456', u'j_son', u'jason', u'jason@aol.com', 23, 32, u'3872981234')]

Woot! An array with one object. Something I’ve discovered, you can’t just run the print command over and over. Your terminal will spit this out:

mysql.connector.errors.InterfaceError: No result set to fetch from.

You have to do the SQL command over again. Why? Because the return value of the print command is NULL. If you are running the command twice in a row, it’s calling a command on…you guessed it…nothing!

Shoutout to Megan and Brian in Mod 1 of Turing. #returnvalues.

The ‘u’ in front of my strings means that the strings are being represented as Unicode. It's a Python feature. I don’t know much about it yet, but since it seems to not be causing a problem right now and it's well-documented as A Thing ®, I’m not going to sweat it.

Alright, so I put a created a bunch of entries in the database using the aforementioned methods. One of them was named John. We did a very basic read function, so how about an update function? Let’s change a value in the name table!

>>> mycursor.execute(“SELECT * FROM customers WHERE name = ‘john’”)
>>> print (mycursor.fetchall())
[(1, u’af065405–7d27–4541–8e24–9d6a5757da06', u’john doe’, u’john’, u’john@john.com’, 13, 12, u’0icvgawjx6haeogi95nf’)]

Cool, he exists. Let’s change his name to Jim.

>>> mycursor.execute(“UPDATE customers SET name = ‘Jim’ WHERE pid =1”)

Now lets see if it updated by selecting all from users where the name is Jim, and seeing if the PID matches. I could also do an “and” statement, (WHERE name = ‘Jim’ AND pid = ‘1’), among other things.

>>> mycursor.execute(“SELECT * FROM customers WHERE name = ‘Jim’”)
>>> print (mycursor.fetchall())
[(1, u’af065405–7d27–4541–8e24–9d6a5757da06', u’john doe’, u’Jim’, u’john@john.com’, 13, 12, u’0icvgawjx6haeogi95nf’)]

Huzzah! I updated a single column. I can do all kinds of things, like making changes to the entire database, getting fancy with SQL, etc, but the most important thing is that things are *working* with making CRUD calls using this connector.

Last but not least, delete. Let’s get rid of John, er, Jim. Let’s be very precise about who we are deleting, and make sure it's not just any old Jim. It’s the Jim I just updated, and I’m going to be specific-I want to delete THIS Jim, and I’ll use unique parameters to make sure it's him. He has a unique primary key, so I will use that.

>>>mycursor.execute(“DELETE FROM customers WHERE name = ‘Jim’ AND pid = 1”)

Let’s check and see if he is gone….

>>> mycursor.execute("SELECT * FROM customers")
>>> print (mycursor.fetchall())
[(2, u’71aa5b40-eecd-11e9–81b4–2a2ae2dbcce4', u’jane doe’, u’jane’, u’jane@aol.com’, 23, 511, u’kfyy7ex4itd0je3h8lkr’), (3, u’71aa5b90-eeed-11e9–71b4–2a2ae2dsefce4', u’bob smith’, u’bob’, u’bob@aol.com’, 3, 901, u’ug63mewiz9bexpx6oi7f’), (4, u’71aooioo0-eeed-32j9–71b4–2a2ae2ruyhce8', u’claudia brand’, u’claudia’, u’claudia@aol.com’, 999, 132, u’ug63mewiz9yu7x6oi7f’), (5, u’71ao00920-eeed-32j9–71b4–4a2ae2olphce8', u’maria jones’, u’maria’, u’maria@aol.com’, 19, 1052, u’ug63mewiz9yu7x7sj7f’), (6, u’71ao00920-eeed-32j9–71b4–4a9kol30phce8', u’mark johnson’, u’mark’, u’mark@aol.com’, 11, 152, u’ug63mewiz9yu7x7sj7f’)]

He is!

I commit my changes to the database, and closeout my session.

>>> connection_object.commit
<bound method CMySQLConnection.commit of <mysql.connector.connection_cext.CMySQLConnection object at 0x103a18f50>>
mycursor.close()
connector_object.close()

So there you have it! From start to finish, full CRUD functionality with the MySQL-Python connector. You can do all kinds of stuff, like store the items you fetch into variables and manipulate them, etc etc. I love that I can work in more than one language at the same time with this tool and use the Python libraries as well, which are very handy to have on board! I ran into a couple of issues with having my data store into my database after I closed everything and started completely over, so that is something to look into for tomorrow.

Happy CRUD-ing :)

-Akira