Basic ways to manipulate MySQL in a python program

python operation MySQL

Recently learning Python, this scripting language is undoubtedly associated with databases, so here's how to use Python to manipulate MySQL databases. I Python is also 0 basic learning, so this blog is for beginners python, Daniel can choose to bypass.

In addition, the environment that this article is based on is Ubuntu13.10, the Python version used is 2.7.5.
MySQL Database

MySQL is a world-leading open source database management system. It is a multi-user, multi-threaded database management system, and Apache, PHP, Linux together to form a lamp platform, widely used in Web applications, such as Wikipedia and YouTube. MySQL consists of two versions: Server system and Embedded system.
Environment configuration

Before we start grammar learning, we also need to install MySQL and Python modules for MySQL operations.

Install MySQL:

sudo apt-get install Mysql-server

During the installation, you will be prompted to enter the root account password, in accordance with the password specification.

Next, you need to install the Python operation module for MySQL:

sudo apt-get install Python-mysqldb

Note: After installing PYTHON-MYSQLDB, we have installed two Python operating modules by default, namely the _mysql supporting the C language API and the MYSQLDB that support the Python API. We will focus on the use of the MySQLdb module later.

Next, we go into MySQL and create a test database called TestDB. The Create command is:

Create DATABASE TestDB;

We then create a test account to manipulate the TestDB database, creating and authorizing commands such as the following:

Create user ' testuser ' @ ' 127.0.0.1 ' identified by ' test123 ', grant all privileges on testdb.* to ' testuser ' @ ' 127.0.0.1 '; _my SQL module

The _mysql module directly encapsulates the C language API function of MySQL, which is incompatible with the Python standard database API interface. I recommend that you use the object-oriented MYSQLDB module only to operate MySQL, here is only one example of using the _mysql module, this module is not the focus of our study, we just need to understand that the module is good.

#!/usr/bin/python#-*-coding:utf-8-*-import _mysqlimport systry:con = _mysql.connect (' 127.0.0.1 ', ' testuser ', ' test12 3 ', ' TestDB ') con.query ("Select VERSION ()") result = Con.use_result () print "MYSQL VERSION:%s"% Result.fetch_row () [0]e Xcept _mysql. Error, E:print "error%d:%s%s"% (E.args[0], e.args[1]) sys.exit (1) finally:if con:  con.close ()

This code is mainly to get the current version of MySQL, you can simulate knocking this part of the code and then run it.
MYSQLDB Module

MYSQLDB is further encapsulated on the basis of the _mysql module and is compatible with the Python standard database API interface, which makes the code easier to migrate. Python also recommends using this MYSQLDB module for MySQL operations.

#!/usr/bin/python#-*-coding:utf-8-*-import mysqldb as Mysqltry:conn = Mysql.connect (' 127.0.0.1 ', ' testuser ', ' test123 ', ' testdb ') cur = conn.cursor () cur.execute ("Select version ()") VERSION = Cur.fetchone () print "Database VERSION:%s"% Versionexcept MySQL. Error, E:print "error%d:%s"% (E.args[0], e.args[1]) exit (1) finally:if conn:  Conn.close ()

We import the MySQLdb module and rename it to MySQL, and then call the MYSQLDB module's provided API method to manipulate the database. It is also the MySQL version number that gets the installation of the current host.
Create a new table

Next, we create a table from the MySQLdb module and populate it with some data. The implementation code is as follows:

#!/usr/bin/python#-*-coding:utf-8-*-import mysqldb as Mysqlconn = Mysql.connect (' 127.0.0.1 ', ' testuser ', ' test123 ', ' t Estdb '); with conn:cur = Conn.cursor () cur.execute ("DROP TABLE IF EXISTS writers"); Cur.execute ("CREATE TABLE writers (id INT PRIMARY KEY auto_increment, name varchar)") Cur.execute ("INSERT INTO writers (name) VALUES (' Wangzhengyi ') cur.execute ("INSERT into writers (name) values (' Bululu ')") Cur.execute ("INSERT INTO Writers (name) values (' Chenshan ') ")

The WITH statement is used here. The WITH statement executes the ENTER () and __exit () methods of the Conn object, saving itself from writing try/catch/finally.

When execution is complete, we can see whether the insert succeeds through the Mysql-client client, query statement:

SELECT * from writers;

The query results are as follows:

Querying data

We just inserted some data into the table, and then we took the inserted data from the tables with the following code:

#!/usr/bin/pythonimport mysqldb as Mysqlconn = Mysql.connect (' 127.0.0.1 ', ' testuser ', ' test123 ', ' TestDB '); with Conn: cursor = Conn.cursor () cursor.execute ("SELECT * from Writers"), rows = Cursor.fetchall () for row in rows:  print row

The query results are as follows:

(1L, ' Wangzhengyi ') (2L, ' Bululu ') (3L, ' Chenshan ')

Dictionary cursor

We've just used the cursor either to create a database or to query the database. There are many types of cursor in the MYSQLDB module, and the default cursor is to return the data in the tuple's tuple form. When we use the dictionary cursor, the data is returned in the form of a Python dictionary. So we can get the query data by the column name.

Or the code that just queried the data, instead dictionary the cursor only needs to modify one line of code, as follows:

#!/usr/bin/pythonimport mysqldb as Mysqlconn = Mysql.connect (' 127.0.0.1 ', ' testuser ', ' test123 ', ' TestDB '); with Conn:  cursor = conn.cursor (mysql.cursors.DictCursor) cursor.execute ("SELECT * from Writers"), rows = Cursor.fetchall () for row in Rows:  print "ID is%s, name is%s"% (row[' id '), row[' name '])

Using the dictionary cursor, the query results are as follows:

ID is 1, name is WANGZHENGYIID are 2, name is Bululuid are 3, name is Chenshan

Pre-compilation

Students who have previously written PHP should be aware of precompilation, and precompiling can help us prevent web attacks such as SQL injection from also helping to improve performance. Of course, Python must also support precompilation. The precompiled implementation is also relatively simple to replace the real variables with placeholders such as%. For example, to query the information for a user with ID 3, use the precompiled code as follows:

#!/usr/bin/pythonimport mysqldb as Mysqlconn = Mysql.connect (' 127.0.0.1 ', ' testuser ', ' test123 ', ' TestDB '); with Conn: cursor = conn.cursor (mysql.cursors.DictCursor) cursor.execute ("SELECT * from writers WHERE id =%s", "3") rows = Cursor.fe Tchone () print "ID is%d, name is%s"% (rows[' id '), rows[' name '])

I have used a placeholder for%s to replace "3", which represents a string type that needs to be passed in. If the incoming string is not of type, an error is run.
Transaction

A transaction is an atomic operation of data in one or more databases. In one transaction, all the effects of the SQL statements are either committed to the database or all are rolled back.

For a database that supports transactional mechanisms, the Python interface begins a transaction when the cursor is created. You can commit all the changes through the cursor object's commit () method, or you can use the Rollback method of the cursor object to roll back all the changes.

I write a code here to insert the non-existent table, when the exception is thrown, call rollback to rollback, the implementation code is as follows:

#!/usr/bin/python#-*-coding:utf-8-*-import mysqldb as Mysqltry:conn = Mysql.connect (' 127.0.0.1 ', ' testuser ', ' test123 ', ' TestDB '); cur = conn.cursor () cur.execute ("INSERT into writers (name) values (' Wangzhengyi4 ')") Cur.execute ("INSERT INTO writers ( Name) VALUES (' Bululu5 ') cur.execute ("INSERT into WRITERSS (name) VALUES (' Chenshan6 ')") conn.commit () except MySQL. Error, E:if conn:  conn.rollback ()  print "Error happens, rollback is call" Finally:if Conn:  Conn.close ()

The results of the implementation are as follows:

Error happens, rollback is call

Because the first two data is the correct insert operation, but because of the overall rollback, so the database also does not have WANGZHENGYI4 and bululu5 the existence of these two data.

