Tutorial on connecting Python program with MySQL

MySQL is the most widely used database server in the web world.SQLite is characterized by being lightweight and embeddable, but cannot withstand high concurrent access and is suitable for desktop and mobile applications. MySQL, on the other hand, is a database designed for server-side use and can withstand high concurrent access while taking up far more memory than SQLite.

In addition, there are multiple database engines within MySQL, with the most commonly used engine being InnoDB, which supports database transactions.
Installing MySQL

You can download the latest version of Community Server 5.0 directly from the official MySQL website.MySQL is cross-platform, so choose the corresponding platform to download the installation file and install it.

During installation, MySQL will prompt for the root user's password, so be sure to memorize it. If you can't remember it, set the password to password.

On Windows, please select UTF-8 encoding during installation to handle Chinese correctly.

On Mac or Linux, you need to edit the MySQL configuration file to change all the database default encodings to UTF-8. The MySQL configuration file is stored in /etc/ or /etc/mysql/ by default:

After restarting MySQL, you can check the encoding via MySQL's client command line:

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor...
...

mysql> show variables like '%char%';
+--------------------------+--------------------------------------------------------+
| Variable_name      | Value                         |
+--------------------------+--------------------------------------------------------+
| character_set_client   | utf8                          |
| character_set_connection | utf8                          |
| character_set_database  | utf8                          |
| character_set_filesystem | binary                         |
| character_set_results  | utf8                          |
| character_set_server   | utf8                          |
| character_set_system   | utf8                          |
| character_sets_dir    | /usr/local/mysql-5.1.65-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.00 sec)

Seeing the word utf8 means the encoding is set correctly.
Installing the MySQL Driver

Since the MySQL server runs as a separate process and is served externally over the network, a MySQL driver that supports Python is required to connect to the MySQL server.

Currently, there are two MySQL drivers:

1. mysql-connector-python: is the official pure Python driver for MySQL;
2. MySQL-python: is a Python driver that encapsulates the MySQL C driver.

You can put both on and decide which one to use when you use it:

$ easy_install mysql-connector-python
$ easy_install MySQL-python

Let's take mysql-connector-python as an example to demonstrate how to connect to the MySQL server's test database:

# Importing the MySQL driver.
>>> import 
# Be careful to set password to your root password: #
>>> conn = (user='root', password='password', database='test', use_unicode=True)
>>> cursor = ()
# Create the users table.
>>> ('create table user (id varchar(20) primary key, name varchar(20))')
# Insert a row, note that MySQL's placeholder is %s:
>>> ('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])
>>> 
1
# Submission of transactions.
>>> ()
>>> ()
# Running queries.
>>> cursor = ()
>>> ('select * from user where id = %s', '1')
>>> values = ()
>>> values
[(u'1', u'Michael')]
# Close Cursor and Connection.
>>> ()
True
>>> ()

Since Python's DB-API definitions are all generic, the database code to manipulate MySQL is similar to SQLite.
wrap-up

MySQL's SQL placeholder is %s;

Normally we pass in use_unicode=True when connecting to MySQL so that MySQL's DB-API always returns Unicode.