Basic tutorial on how to operate Mysql in Python

Basic tutorial on how to operate Mysql in Python
Python for Mysql

Recently I have been studying python. This scripting language is undoubtedly associated with the database. So I will introduce how to use python to operate mysql databases. I am also a beginner of python, so this blog is intended for beginners of python. You can choose to bypass python.

In addition, this blog is based on Ubuntu13.10 and uses python 2.7.5.

MYSQL database

MYSQL is a world-leading open-source database management system. It is a multi-user, multi-thread database management system. It is a LAMP platform with Apache, PHP, and Linux and is widely used in web applications, such as Wikipedia and YouTube. MYSQL has two versions: Server System and embedded system.

Environment Configuration

Before learning the syntax, we also need to install mysql and python modules for mysql operations.

Install mysql:

sudo apt-get install mysql-server

During the installation process, you will be prompted to enter the password of the root account to comply with the password specifications.

Next, you need to install python's mysql operation module:

sudo apt-get install python-mysqldb

Note: After installing python-mysqldb, two python operation modules are installed by default, namely _ mysql that supports C language APIs and MYSQLdb that supports Python APIs. The usage of the MYSQLdb module will be explained later.

Next, go to MYSQL and create a test database named testdb. CREATE command:

create database testdb;

Then, create a test account to operate the testdb database. The command for creating and authorizing the database is as follows:

create user 'testuser'@'127.0.0.1' identified by 'test123';grant all privileges on testdb.* to 'testuser'@'127.0.0.1';

_ Mysql module

_ The mysql module directly encapsulates mysql c language API functions, which are incompatible with the python standard database API interfaces. I recommend that you use the object-oriented MYSQLdb module to operate mysql. Here we only provide an example of using the _ mysql module. This module is not the focus of our study, we only need to know that this module is available.

#!/usr/bin/python# -*- coding: utf-8 -*-import _mysqlimport systry:    con = _mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb')    con.query("SELECT VERSION()")    result = con.use_result()    print "MYSQL version : %s " % result.fetch_row()[0]except _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 used to obtain the current mysql version. You can simulate this part of the code and then run it.

MYSQLdb module

MYSQLdb is further encapsulated on the basis of the _ mysql module and compatible with the python standard database API interface, which makes the code easier to be transplanted. For Python, we recommend that you use 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 imported the MySQLdb module and renamed it as mysql. Then, we called the API method provided by the MySQLdb module to operate the database. Obtain the mysql version number installed on the current host.

Create a new table

Next, we create a table through the MySQLdb module and fill in 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', 'testdb');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(25))")    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 executesEnter ()And the _ exit () method, saving yourself from writing try/catch/finally.

After the execution is complete, you can use the mysql-client to check whether the insertion is successful. The query statement is as follows:

select * from writers;

The query result is as follows:

Id	Name
1	Wangzhengyi
2	Bululu
3	Chenshan

Query data

I inserted some data to the table just now. Next, we will extract the inserted data from the table. The Code is as follows:

#!/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 result is as follows:

(1L, 'wangzhengyi')(2L, 'bululu')(3L, 'chenshan')

Dictionary cursor

Cursor is used for both database creation and database query. There are many cursor types in the MySQLdb module. The default cursor returns data in the form of tuples. When we use dictionary cursor, the data is returned in the form of a python dictionary. In this way, we can get the query data through the column name.

You can modify the Data Query code to "dictionary cursor" by modifying the code line 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'])

The query result is as follows:

id is 1, name is wangzhengyiid is 2, name is bululuid is 3, name is chenshan

Pre-compile

Those who have previously written php should be familiar with pre-compilation. Pre-compilation can help us prevent SQL injection and other web attacks and improve performance. Of course, python must also support pre-compilation. The pre-compilation implementation is also relatively simple, that is, replace the real variables with placeholders such as %. For example, to query user information with id 3, use the pre-compiled 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.fetchone()    print "id is %d, name is %s" % (rows['id'], rows['name'])

Here I use a placeholder of % s to replace "3", which indicates that the input is a string type. If the input is not of the string type, an error is returned.

Transactions

Transactions refer to atomic operations on data in one or more databases. In a transaction, the impact of all SQL statements is either committed to the database or rolled back.

For databases that support the transaction mechanism, the python interface starts a transaction when creating the cursor. You can use the commit () method of the cursor object to submit all the changes, or you can use the rollback method of the cursor object to roll back and forth all the changes.

Here I write a code to insert a non-existent table. When an exception is thrown, I call rollback for 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 execution result is as follows:

Error happens, rollback is call

Because the first two pieces of data are correct insertion operations, but because of the overall rollback, the existence of wangzhengyi4 and bululu5 does not exist in the database.