Tutorial on connecting Python program to MySQL-Python tutorial

Source: Internet
Author: User
Tags most popular database python mysql
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:

Default-character-set = utf8

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.

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.