Connect a python program with MySQL tutorial

MySQL is the most widely used database server in the Web world. SQLite is characterized by its lightweight, embeddable, but not highly concurrent access, for desktop and mobile applications. MySQL is designed for server-side database, can withstand high concurrent access, while occupying a much larger memory than SQLite.

In addition, MySQL has a variety of database engine, the most common engine is to support database transaction InnoDB.
Install MySQL

The latest version of Community Server 5.6.x can be downloaded directly from the MySQL official website. MySQL is cross-platform, select the corresponding platform to download the installation files, installation can be.

When installing, MySQL will prompt you to enter the root user's password, please remember clearly. If you are afraid to remember, set the password to password.

On Windows, choose UTF-8 encoding when installing, so that you can handle Chinese correctly.

On Mac or Linux, you need to edit the MySQL configuration file and change the default encoding of the database to UTF-8. The MySQL configuration file is stored by default in/ETC/MY.CNF or/ETC/MYSQL/MY.CNF:

Copy the Code code as follows:

[Client]
Default-character-set = UTF8

[Mysqld]
Default-storage-engine = INNODB
Character-set-server = UTF8
Collation-server = Utf8_general_ci

After restarting MySQL, the code can be checked through the MySQL client command line:

$ 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)

See the UTF8 typeface to indicate that the encoding is set correctly.
Install MySQL Driver

Because the MySQL server runs in a separate process and serves the network externally, it is necessary to support the MySQL driver for Python to connect to the MySQL server.

Currently, there are two MySQL drivers:

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

You can put two of them on, and then decide which one to use:

$ easy_install mysql-connector-python$ Easy_install Mysql-python

Let's take Mysql-connector-python as an example of how to connect to the test database of a MySQL server:

# import MySQL driver:>>> import mysql.connector# Note Password Set as your root password:>>> conn = Mysql.connector.connect ( User= ' root ', password= ' password ', database= ' test ', use_unicode=true) >>> cursor = conn.cursor () # Create user table:> >> cursor.execute (' CREATE table user (ID varchar (primary key, name varchar (20)) ') # Insert a row of records, note that the MySQL placeholder is%s:>& gt;> Cursor.execute (' INSERT into user (ID, name) values (%s,%s) ', [' 1 ', ' Michael ']) >>> cursor.rowcount1# COMMIT Transaction :>>> conn.commit () >>> cursor.close () # run query:>>> cursor = conn.cursor () >>> Cursor.execute (' select * from user where id =%s ', ' 1 ') >>> values = Cursor.fetchall () >>> values[(U ' 1 ', u ' Michael ')]# close cursor and connection:>>> cursor.close () true>>> conn.close ()

Because Python's db-api definition is generic, the database code that operates MySQL is similar to SQLite.
Summary

The SQL placeholder for MySQL is%s;

Usually we pass in use_unicode=true when we connect to MySQL, so that MySQL Db-api always returns Unicode.

