Python operation MySQL Instance code tutorial online (search manual) _python

Instance 1, get the version of MySQL

Installing MySQL modules in a Windows environment for Python development

Mysql-python Windows exe installation file download

Copy Code code 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 ');

#所有的查询, is running on a module cursor that connects 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 results:

Copy Code code as follows:

Database version:5.5.25

Instance 2, creating a table and inserting data

Mainly in the cursor above execute Execute method to proceed, see Source code:

Copy Code code as follows:

#-*-Coding:utf-8-*-
# comes from summary finishing
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 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 results (see in phpMyAdmin, see results on MySQL command line):



Instance 3, Python uses slect to get MySQL data and iterate through

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

Copy Code code as follows:

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

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

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

#使用fetchall函数, the result set (multidimensional Group) is deposited in rows
rows = Cur.fetchall ()

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

Run Result:

Copy Code code as follows:

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

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

Copy Code code as follows:

#-*-Coding:utf-8-*-
# Source: Crazy Ant's Blog summary Finishing

Import MySQLdb as MDB
Import Sys

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

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

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

#使用cur. RowCount to get the number of result sets
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]

Run Result:

Copy Code code as follows:

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

numrows = Int (cur.rowcount) is used to get the number of result sets
row = Cur.fetchone () fetch one row of data at a time, and the pointer to the recordset executes the next line

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

Copy Code code as follows:

#-*-Coding:utf-8-*-
# Source: Crazy Ant's Blog summary Finishing

Import MySQLdb as MDB
Import Sys

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

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

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

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

#遍历数据也不变 (a little more direct 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 '% (row["Id"], row["Name")

Instance 5, how to get field names and information for a single table

Copy Code code as follows:

#-*-Coding:utf-8-*-
# Source: Crazy Ant's Blog summary Finishing

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 field name.
print '%s%3s '% (desc[0][0], desc[1][0])

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

Run Result:

Copy Code code as follows:

Cur.description: (' IDs ', 3, 1, 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

Instance 6, using prepared statements to execute a query (more secure and convenient)

Copy Code code as follows:

#-*-Coding:utf-8-*-
# Source: Crazy Ant's Blog summary Finishing
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 the number of rows affected
Print "Number of rows updated:%d"% cur.rowcount

Results:

Copy Code code as follows:

Number of rows Updated:1

Example 7, put the picture into MySQL with binary
Some people like to store pictures in MySQL (which seems very rare), I see most of the programs, pictures are stored in the server files, the database is only the image of the address, but MySQL is supporting the image into the database, but also corresponding to a special field blob (Binary Large object, that is, a larger binary object field, see the following procedure, note that the test picture yourself to find one, address to be correct:

First, create a table in the database that holds the picture:

Copy Code code as follows:

CREATE TABLE Images (Id INT PRIMARY KEY auto_increment, Data Mediumblob);

Then run the following Python code:

Copy Code code as follows:

#-*-Coding:utf-8-*-
# Source: Crazy Ant's Blog summary Finishing
Import MySQLdb as MDB
Import Sys

Try
#用读文件模式打开图片
Fin = Open (".. /web.jpg ")
#将文本读入img对象中
img = Fin.read ()
#关闭文件
Fin.close ()

Except IOError, E:
#如果出错, printing error messages
Print "Error%d:%s"% (E.args[0],e.args[1])
Sys.exit (1)

Try
#链接mysql, getting objects
conn = mdb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' test ')
#获取执行cursor
cursor = Conn.cursor ()
#直接将数据作为字符串, inserting a database
Cursor.execute ("INSERT into Images SET data= '%s '"% mdb.escape_string (IMG))

#提交数据
Conn.commit ()

#提交之后, and then close cursor and links
Cursor.close ()
Conn.close ()

Except MDB. Error, E:
#若出现异常, printing information
Print "Error%d:%s"% (E.args[0],e.args[1])
Sys.exit (1)

Results:

The Escape_string function escapes the string that is inserted into the database, which means that some SQL injection attacks

Example 8, read the picture from the database

Copy Code code as follows:

#-*-Coding:utf-8-*-
# Source: Crazy Ant's Blog summary Finishing
Import MySQLdb as MDB
Import Sys

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

cursor = Conn.cursor ()

#执行查询该图片字段的SQL
Cursor.execute ("Select Data from Images LIMIT 1")

#使用二进制写文件的方法, open a picture file and automatically create it if it does not exist
Fout = open (' Image.png ', ' WB ')
#直接将数据如文件
Fout.write (Cursor.fetchone () [0])
#关闭写入的文件
Fout.close ()

#释放查询数据的资源
Cursor.close ()
Conn.close ()

Except IOError, E:
#捕获IO的异常, the main problem is that file writes occur
Print "Error%d:%s"% (E.args[0],e.args[1])
Sys.exit (1)

Real Example 9, using transaction as a transaction (manual submission, automatic rollback)

Copy Code code as follows:

#-*-Coding:utf-8-*-
# Source: Crazy Ant's Blog summary Finishing
Import MySQLdb as MDB
Import Sys

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

cursor = Conn.cursor ()
#如果某个数据库支持事务, will automatically open
#这里用的是MYSQL, the transaction is automatically opened (if the Myism engine does 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 the three statements above 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 not executed, the result is unchanged
3, if this code into a MyISAM engine table, the first two sentences will be executed, the third sentence will not be, if the INNDB engine, then will not be executed.

For the convenience of local browsing special offers a PDF version download

After the full text, this article translates from: http://zetcode.com/databases/mysqlpythontutorial/