Python operation MySQL Instance code

Example 1, get MySQL version

Installing the MySQL module in a Windows environment for Python development

Mysql-python windows under EXE installation file download

The code is as follows:

#-*-Coding:utf-8-*-

#安装MYSQL DB for Python
Import MySQLdb as MDB

con = None

Try
#连接mysql的方法: Connect (' IP ', ' user ', ' password ', ' dbname ')
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

#所有的查询, all running on the cursor of a module connected to con
cur = con.cursor ()

#执行一个查询
Cur.execute ("Select VERSION ()")

#取得上个查询的结果, is a single result
data = Cur.fetchone ()
Print "Database version:%s"% data
Finally
If con:
#无论如何, connect remember to close
Con.close ()

Execution Result:

Database version:5.5.25

Example 2, creating a table and inserting data

The main or the cursor above the Execute method to do, see the Source:

The code is as follows:

#-*-Coding:utf-8-*-
Import MySQLdb as MDB
Import Sys

#将con设定为全局连接
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

With Con:

#获取连接的cursor, we can do all kinds of things only if we get the cursor.
cur = con.cursor ()
#创建一个数据表 Writers (id,name)
Cur.execute ("CREATE TABLE IF not EXISTS \
Writers (Id INT PRIMARY KEY auto_increment, Name VARCHAR (25)) ")
#以下插入了5条数据
Cur.execute ("INSERT into writers (Name) VALUES (' Jack London ')")
Cur.execute ("INSERT into writers (Name) VALUES (' Honore de Balzac ')")
Cur.execute ("INSERT into writers (Name) VALUES (' Lion Feuchtwanger ')")
Cur.execute ("INSERT into writers (Name) VALUES (' Emile Zola ')")
Cur.execute ("INSERT into writers (Name) VALUES (' Truman Capote ')")

Run the results (viewed in phpmyadmin, the results are the same on the MySQL command line):

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/22/wKioL1SSiWORzXcoAAGaYGsYBFA410.jpg "title=" 1.png " alt= "Wkiol1ssiworzxcoaagaygsybfa410.jpg"/>

Example 3, Python uses slect to get MySQL data and traverse

This is probably the most used, please read the code quickly:

The code is as follows:

#-*-Coding:utf-8-*-
Import MySQLdb as MDB
Import Sys

#连接mysql, gets the connected object
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

With Con:
#仍然是, the first step is to get a connected cursor object for executing the query
cur = con.cursor ()
#类似于其他语言的query函数, execute is the Execute query function in Python
Cur.execute ("SELECT * From writers")

#使用fetchall函数, Save the result set (multidimensional tuples) inside the rows
rows = Cur.fetchall ()

#依次遍历结果集, each element is found to be a record in the table, with a tuple to display
For row in rows:
Print row

Operation Result:

(1L, ' Jack London ')
(2L, ' Honore de Balzac ')
(3L, ' Lion Feuchtwanger ')
(4L, ' Emile Zola ')
(5L, ' Truman Capote ')

The above code is used to get all the results out, but when printing is a meta-ancestor print per line, now we use the method to take out the individual data:

The code is as follows:

#-*-Coding:utf-8-*-

Import MySQLdb as MDB
Import Sys

#获取mysql的链接对象
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

With Con:
#获取执行查询的对象
cur = con.cursor ()

#执行那个查询, here's a SELECT statement.
Cur.execute ("SELECT * From writers")

#使用cur. RowCount gets the number of result set bars
numrows = Int (cur.rowcount)

#循环numrows次, fetch one row of data at a time
For I in Range (NumRows):
#每次取出一行, put it in the row, this is a tuple (id,name)
row = Cur.fetchone ()
#直接输出两个元素
Print row[0], row[1]

Operation Result:

1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

numrows = Int (cur.rowcount) to get the number of result sets
row = Cur.fetchone () extracts one row of data at a time, while the recordset's pointer executes the next line

Example 4, using the dictionary cursor to get the result set (you can use the table field name to access the value)

The code is as follows:

#-*-Coding:utf-8-*-

Import MySQLdb as MDB
Import Sys

#获得mysql查询的链接对象
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ')

With Con:
#获取连接上的字典cursor, pay attention to the method obtained,
#每一个cursor其实都是cursor的子类
cur = con.cursor (mdb.cursors.DictCursor)

#执行语句不变
Cur.execute ("SELECT * From writers")

#获取数据方法不变
rows = Cur.fetchall ()

#遍历数据也不变 (a little more directly than the previous one)
For row in rows:
#这里, you can use the method of key-value pairs to get the data by the key name
Print "%s%s"% (row["Id"], row["Name"])

Example 5, methods for obtaining field names and information for a single table

The code is as follows:

#-*-Coding:utf-8-*-

Import MySQLdb as MDB
Import Sys

#获取数据库的链接对象
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ')

With Con:
#获取普通的查询cursor
cur = con.cursor ()
Cur.execute ("SELECT * From writers")

rows = Cur.fetchall ()

#获取连接对象的描述信息
desc = cur.description
print ' cur.description: ', desc

#打印表头, that's the name of the field
Print "%s%3s"% (Desc[0][0], desc[1][0])

For row in rows:
#打印结果
Print "%2s%3s"% row

Operation Result:

Cur.description: (' Id ', 3, 1, one, one, 0, 0), (' Name ', 253, 17, 25, 25, 0, 1))
Id Name
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

Example 6, using prepared statements to execute the query (more secure and convenient)

The code is as follows:

#-*-Coding:utf-8-*-
Import MySQLdb as MDB
Import Sys

con = mdb.connect (' localhost ', ' root ', ' root ', ' test ')

With Con:

cur = con.cursor ()
#我们看到, this can be done by writing a SQL statement that can be assembled
Cur.execute ("UPDATE writers SET Name =%s WHERE Id =%s",
("Guy de maupasant", "4"))
#使用cur. RowCount gets how many rows are affected
Print "Number of rows updated:%d"% cur.rowcount

Results:

Number of rows Updated:1

Real example 7, using Transaction as transaction (manual commit, automatic rollback)

The code is as follows:

#-*-Coding:utf-8-*-
Import MySQLdb as MDB
Import Sys

Try
#连接mysql, gets the connected object
conn = Mdb.connect (' localhost ', ' root ', ' root ', ' test ');

cursor = Conn.cursor ()
#如果某个数据库支持事务, it will open automatically
#这里用的是MYSQL, so the transaction is turned on automatically (if the Myism engine is not)
Cursor.execute ("UPDATE writers SET Name =%s WHERE Id =%s",
("Leo Tolstoy", "1"))
Cursor.execute ("UPDATE writers SET Name =%s WHERE Id =%s",
("Boris Pasternak", "2"))
Cursor.execute ("UPDATE Writer SET Name =%s WHERE Id =%s",
("Leonid Leonov", "3"))

#事务的特性1, Atomic manual submission
Conn.commit ()

Cursor.close ()
Conn.close ()

Except MDB. Error, E:
#如果出现了错误, you can roll back, that is, the above three statements are either executed or not executed
Conn.rollback ()
Print "Error%d:%s"% (E.args[0],e.args[1])

Results:

1, because there is no writer table (SQL third statement), there is an error:
Error 1146:table ' test.writer ' doesn ' t exist
2, error, departure exception handling, the first two of the 3 statements will automatically become no execution, the result is unchanged
3, if the code is placed in a MyISAM engine table, the first two sentences will be executed, the third sentence will not, if it is the INNDB engine, will not be executed.

This article is from the "XFICC" blog, make sure to keep this source http://xficc.blog.51cto.com/1189288/1591418

Python operation MySQL Instance code