Tutorial on connecting Python program to MySQL-Python tutorial

This article describes how to connect a Python program to MySQL. MySQL, as the most popular database, has become an almost essential knowledge in today's Python learning, for more information, see MySQL, which is the most widely used database server in the Web world. SQLite features lightweight, embedded, but not highly concurrent access, and is suitable for desktop and mobile applications. MySQL is a database designed for the server. it can withstand high concurrency access and occupy much larger memory than SQLite.

In addition, MySQL has multiple internal database engines, and the most common engine is InnoDB that supports database transactions.
Install MySQL

You can download the latest version of Community Server 5.6.x from the MySQL official website. MySQL is cross-platform. select the corresponding platform to download and install the installation file.

During installation, MySQL will prompt you to enter the password of the root user. please note clearly. If you cannot remember it, set the password to password.

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

On Mac or Linux, you need to edit the MySQL configuration file and change all the default database encoding to UTF-8. MySQL configuration files are stored in/etc/my. cnf or/etc/mysql/my. cnf by default:

The code is as follows:

[Client]
Default-character-set = utf8

[Mysqld]
Default-storage-engine = INNODB
Character-set-server = utf8
Collation-server = utf8_general_ci

After you restart MySQL, you can use the MySQL client command line to check the encoding:

$ mysql -u root -pEnter 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)

When utf8 is displayed, the encoding is set correctly.
Install the MySQL driver

Because the MySQL server runs in an independent process and serves externally through the network, you need to support the Python MySQL driver to connect to the MySQL server.

Currently, there are two MySQL drivers:

1. Mysql-connector-python: it is the official pure Python driver of MySQL;
2. MySQL-python: A Python driver that encapsulates the MySQL C driver.

You can install both of them and decide which one to use when using them:

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

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

# Import the MySQL driver: >>> import mysql. connector # set password to your root password: >>> conn = mysql. connector. connect (user = 'root', password = 'password', database = 'test', use_unicode = True) >>> cursor = conn. cursor () # create a user table: >>> cursor.exe cute ('create table user (id varchar (20) primary key, name varchar (20) ') # insert a row of records, note that the placeholder for MySQL is % s: >>> cursor.exe cute ('Insert into user (id, name) values (% s, % s) ', ['1 ', 'Michael '])> cursor. rowcount1 # submit a transaction: >>> conn. commit () >>> cursor. close () # run the query: >>> cursor = conn. cursor () >>> cursor.exe cute ('select * from user where id = % s', '1') >>> values = cursor. fetchall () >>> values [(u'1', u'michael ')] # Disable Cursor and Connection: >>> cursor. close () True >>> conn. close ()

Since the DB-API definition of Python is generic, the database code for operating MySQL is similar to that of SQLite.
Summary

The placeholder SQL for MySQL is % s;

We usually pass use_unicode = True when connecting to MySQL so that MySQL's DB-API always returns Unicode.