MySQLdb - accessing MySQL databases from Python
MySQL is a popular open-source database engine, and Python interfaces quite nicely with MySQL with the MySQLdb package. For more on why you would want to use a database for your data, check out this post. Here I’ll show you how to connect to your existing MySQL database with Python.
Assumptions
- I’m assuming you have a MySQL database running. More info here
- you have the MySQLdb package installed for Python.
- The database is running on localhost, the user is root, and the password is p@55w0rd.
- You know some SQL (at least enough to appreciate some of these examples)
Caveats
While the code below is specific to MySQLdb, no matter what database API you use you should be able to use the same syntax (as outlined in PEP 249).
For more details, see the official documentation for MySQLdb. Here I’m just trying to explain things slightly differently.
Example usage
Import MySQLdb, and connect to the database
import MySQLdb
my_connection = MySQLdb.connect(host='localhost', user='root', passwd='p@55w0rd')
cursor = my_connection.cursor()That’s it! You’re ready to start sending SQL statements to your MySQL database!
The cursor is everything!
The cursor now contains all the information it needs to send information to and get information from the running MySQL server. It’s your key to the database.
The two most often-used methods of the MySQLdb cursor are
- cursor.execute(), which executes a query (but doesn’t return the data)
- cursor.fetchall(), which fetches the data from the most recently executed query.
You send commands to MySQL by passing strings of SQL statements to cursor.execute(). When doing so, you can take advantage of Python’s multi-line string (delimited by triple quotes (“”")) and the fact that SQL syntax doesn’t care that there are newlines in the query. Furthermore, MySQLdb automatically adds semicolons to the end of SQL statements if you forget them.
Interacting with the database
Create the database and a table
Make a new database by sending the standard SQL query, ‘CREATE DATABASE testdb’, to the database you connected to. Note that MySQLdb automatically adds semicolons to the end of statments if you don’t add them yourself.
cursor.execute('CREATE DATABASE testdb')If you do this in an interactive session, you will notice that this method returned a long format integer (1L). This is the number of lines returned by the cursor. Don’t worry about it quite yet.
Now make that new database the active one:
cursor.execute('USE testdb')Now create a table in the testdb database to hold some addresses:
cursor.execute('''CREATE TABLE addresses (
name VARCHAR(20),
street VARCHAR(20),
zipcode INT,
city VARCHAR(20),
state CHAR(2)
)
''')Note the use of triple quotes so that you can visually organize the SQL query for clarity.
Import data from Python into MySQL
The general syntax for passing Python data to an SQL query through the cursor is:
cursor.execute(SQL,tuple)
where SQL is a valid SQL statement. If SQL has N placeholders of the form %s, then tuple must have length N. Hopefully an example will make more sense.
Let’s create some Python lists that we’ll import into this table. The beauty of it is that these data could have been parsed from a text file with hundreds or thousands of names, and we can import them into the database automatically. For now we’ll just enter three records though.
Here’s the data that will go into the database:
names = ['Bob', 'Alfred', 'Jen']
streets = ['123 Elm Street', '55 Ninth Ave', '1 Paved Rd']
zips = [00123, 34565, 30094]
cities = ['Newark', 'Salinas', 'Los Angeles']
states = ['NJ', 'CA', 'CA']And here’s how to get that data into the addresses table:
cursor.executemany('''INSERT INTO addresses
(name, street, zipcode, city, state)
VALUES
(%s, %s, %s, %s, %s)''',
zip(names, streets, zips, cities, states))A couple of things to note here:
- This time we used cursor.executemany(), which will accept a list of lists as input, instead of cursor.execute().
- There were 5 fields into which we inserted data (name, street, zipcode, cities, and state)
- There were 5 %s placeholders in the SQL query.
- Even though zipcode is an INT field and not a string, we used %s. This will always be the case: use %s as a placeholder no matter what the datatype.
- There were 5 lists that were zipped together. They need to be zipped so that the result is a list of lists, and the length of each item in the list = 5.
- The order in which these lists were zipped corresponded to the fields into which they were to be inserted.
Retrieving data from the database
There are two steps to retrieving data: executing the query, then fetching the results.
To select all addresses in California, first execute this query (it’s a one-liner so triple quoting isn’t really needed)
cursor.execute("SELECT * FROM addresses WHERE state = 'CA' ")Alternatively . . . often you will want to feed Python variables into the query. Say the state abbreviation ‘CA’ is saved in a Python variable called my_state. Then this query will do the same thing as the one above:
cursor.execute('''SELECT * FROM addresses WHERE state = %s''', my_state)By the way, my_state is not a tuple, but that’s OK since there is only one %s placeholder in the query. MySQLdb knows where it should go.
Now to retrieve the results:
results = cursor.fetchall()Note that a cursor object is similar to a file object or an iterator: once you fetch everything, there is nothing left in the cursor to retrieve. So executing the command above a second time would result in an empty list until the query is executed again.
results is a tuple of tuples and looks like this:
(('Alfred', '55 Ninth Ave', 34565L, 'Salinas', 'CA'),
('Jen', '1 Paved Rd', 30094L, 'Los Angeles', 'CA'))That’s all there is to it! Armed with this knowledge, now you can execute queries from Python to import, retrieve, and plot data from your database. This was a simple demo of what MySQL and Python can do, but you can construct ever-larger databases and ever-more-sophisticated queries to manipulate data in ways that would be impossible without these tools.
Tags: data management, databases, MySQLdb, Python, syntax