9 Practical examples of Python operating MySQL database

Installing the MySQL module on the Windows platform for Python development

When using Python to connect to MySQL, you need to use the installation version, the source code version is prone to error prompts. The bottom is packaged in 32 and 64 versions.
Mysql-python-1.2.3.win32-py2.7.exe
Mysql-python-1.2.3.win-amd64-py2.7.exe
Example 1, Get MYSQL version

#-*-coding:utf-8-*-#安装 mysql DB for pythonimport mysqldb as Mdbcon = nonetry:  #连接 MYSQL method: Connect (' IP ', ' user ', ' Password ', ' dbname ')  con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');  #所有的查询,  cur = con.cursor ()  #执行一个查询  cur.execute ("Select VERSION ()")  that is running on the cursor of a module that is connected to con The result of getting the last query is a single result  data = Cur.fetchone ()  print "Database version:%s"% datafinally:  if con:    #无论如何, Connect remember to close    Con.close ()

Example 2, creating a table and inserting data

Import mysqldb as Mdbimport sys# con is set to global connection con = mdb.connect (' localhost ', ' root ', ' root ', ' test '); with con: #获取连接的 cursor , we can do all sorts 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) ") #以下插入了 5 data 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 ') ")

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

Import MySQLdb as Mdbimport sys# connect MySQL, get connected object con = mdb.connect (' localhost ', ' root ', ' root ', ' test '); with con: #仍然是, the first step is to Gets the connected Cursor object, which is used to execute the query cur = con.cursor () #类似于其他语言的 the query function, execute is the executing function in Python cur.execute ("select * From writers") # Using the Fetchall function, the result set (multidimensional tuple) is stored in rows with rows = Cur.fetchall () #依次遍历结果集, each element is found to be a record in the table, and a tuple is used to display the 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:

Import MySQLdb as mdbimport sys# get MySQL link object con = mdb.connect (' localhost ', ' root ', ' root ', ' test '); with con: #获取执行查询的对象cu R = Con.cursor () #执行那个查询, here is the SELECT statement Cur.execute ("SELECT * from writers") #使用 Cur.rowcount get the number of results set numrows = Int (cur.row count) #循环 numrows times, removing one row of data for I in range (numrows): #每次取出一行, placed in row, which 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
Example 4, using the dictionary cursor to get the result set (you can use the table field name to access the value)

Import MySQLdb as Mdbimport sys# get the link object for MySQL query con = mdb.connect (' localhost ', ' root ', ' root ', ' test ') with con: #获取连接上的字典 C Ursor, note the method that gets, #每一个 cursor is actually a subclass of cursor cur = con.cursor (mdb.cursors.DictCursor) #执行语句不变cur. Execute ("SELECT * FROM Writers ") #获取数据方法不变rows = Cur.fetchall () #遍历数据也不变 (more directly than the previous) for row in rows: #这里, you can use the key-value pair method, which gets the data print"%s%s "by the key name (row[ "Id"], row["Name"])

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

Import MySQLdb as Mdbimport sys# get the linked object of the database con = mdb.connect (' localhost ', ' root ', ' root ', ' test ') with con: #获取普通的查询 cursorc ur = con.cursor () cur.execute ("SELECT * from writers") rows = Cur.fetchall () #获取连接对象的描述信息desc = Cur.descriptionprint ' Cur.description: ', desc# print header, is the field name 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)

Import MySQLdb as Mdbimport Syscon = Mdb.connect (' localhost ', ' root ', ' root ', ' test ') with con:cur = Con.cursor () #我们看到, here you can Cur.execute by writing an SQL statement that can be assembled ("UPDATE writers SET Name =%s WHERE Id =%s", ("Guy de maupasant", "4")) #使用 Cur.rowcount get Affects how many lines print "Number of rows updated:%d"% cur.rowcount

Results:
Number of rows Updated:1

Example 7, put the picture binary into MYSQL

Some people like to put pictures into MYSQL (which seems to be very few), I think most of the programs, pictures are stored on the server file, the database is only the image of the address, but MYSQL is to support the image into the database, but also corresponding to a dedicated field BLOB (Binary Large object), that is, the larger binary objects field, see the following program, pay attention to the test picture yourself to find one, the address should be correct:
First, create a table in the database to hold the picture:

Copy the Code code as follows:

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

Then run the following PYTHON code:

Import MySQLdb as Mdbimport systry: #用读文件模式打开图片fin = Open (".. /web.jpg ") #将文本读入 an IMG object in img = Fin.read () #关闭文件fin. Close () except IOError, E: #如果出错, printing error message print" Error%d:%s "% (e.args[0],e . args[1]) sys.exit (1) Try: #链接 MySQL, get object conn = mdb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' test ') # Get Execution Cursorcursor = conn.cursor () #直接将数据作为字符串, insert Database Cursor.execute ("INSERT into Images SET data= '%s '%mdb.escape_string (IMG)) #提交数据conn. Commit () #提交之后, and then close the cursor and link cursor.close () conn.close () except MDB. Error, E: #若出现异常, printing information print "Error%d:%s"% (E.args[0],e.args[1]) sys.exit (1)

Example 8. Read the picture from the database

Import MySQLdb as Mdbimport systry: #连接 MySQL, get connected Object conn = Mdb.connect (' localhost ', ' root ', ' root ', ' test '); cursor = conn. Cursor () #执行查询该图片字段的 sqlcursor.execute ("Select Data from Images LIMIT 1") #使用二进制写文件的方法, open a picture file, if it does not exist automatically create Fout = open (' Image.png ', ' WB ') #直接将数据如文件fout. Write (Cursor.fetchone () [0]) #关闭写入的文件fout. Close () #释放查询数据的资源cursor. Close () Conn.close () except IOError, E: #捕获 IO exception, mostly file write error print "Error%d:%s"% (E.args[0],e.args[1]) sys.exit (1)

Example 9, using Transaction as Transaction (manual commit, automatic rollback)

Import MySQLdb as Mdbimport systry: #连接 MySQL, get connected Object conn = Mdb.connect (' localhost ', ' root ', ' root ', ' test '); cursor = conn. Cursor () #如果某个数据库支持事务, will open automatically # here is MYSQL, so will automatically open the transaction (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 commit 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), so an error occurred: "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.

The above is for you to share the 9 practical python operation MySQL database instance, I hope that everyone's learning is helpful.

