MySQLdb

Python MySQLdb Tutorial

Here are some examples on using the MySQL python module. In each example below, we're assuming that we have a mysql server running on localhost, and connecting to a database named beerdb with the username/password beeruser/beerpw.

Install MySQLdb python module

First thing we need to do is install the python module. It's called MySQL-python in pip. 

pip install MySQL-python 

PyMySQL for Python 3

The MySQL-python module is not supported by Python 3, alternatively, you can use PyMySQL.

pip install PyMySQL

Create a table and insert into it

We're connecting to the aforementioned beerdb, creating a table called beers, and inserting a few beers into it. The other column is the id which is going to be the primary key.

#!/usr/bin/env python

# Connect to db and create a table called beers that each have a name field and insert a couple beers

import MySQLdb as mysqldb

connection = mysqldb.connect('localhost', 'beeruser', 'beerpw', 'beerdb');

with connection:
cursor = connection.cursor()
cursor.execute("create table beers(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25))")
cursor.execute("insert into beers(name) values('Budweiser')")
cursor.execute("insert into beers(name) values('Mystic')")
cursor.execute("insert into beers(name) values('Sam Adams')")

We can run this script as a file from the command line, or open a python shell.

We can verify this on the MySQL side after running

mysql> select * from beers;
+----+-----------+
| id | name |
+----+-----------+
| 2 | Budweiser |
| 3 | Mystic |
| 4 | Sam Adams |
+----+-----------+
3 rows in set (0.00 sec)

Select data from a table

We will run the same select as above, but from python, first we'll connect to the database same was as when we did the create table and insert.

#!/usr/bin/env python

# Connect to db and create a table called beers that each have a name field and insert a couple beers

import MySQLdb as mysqldb

connection = mysqldb.connect('localhost', 'beeruser', 'beerpw', 'beerdb');

with connection:
cursor = connection.cursor()
cursor.execute("select * from beers")

beers = cursor.fetchall()
for beer in beers:
print(beer)

(Comments)

Comments