Python operation MySQL DB instance

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

Free: http://yunpan.cn/cVnTc9iRsQR4m access password 06FC

The installation process is simple enough to say:

Example 1, Get MYSQL version

#-*-Coding:utf-8-*-#安装 MYSQL DB for PythonImportMySQLdb asMdbcon =NoneTry:#连接 MySQL method: Connect (' IP ', ' user ', ' password ', ' dbname ')con = Mdb.connect (' localhost ',' Root ',' Root ',' Test ');#所有的查询, all running on the cursor of a module connected to concur = con.cursor ()#执行一个查询Cur.execute ("Select VERSION ()")#取得上个查询的结果, is a single resultdata = Cur.fetchone ()Print "Database version:%s"% datafinally:ifCon#无论如何, connect remember to closeCon.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 EXISTSWriters (IdINT PRIMARY KEYAuto_increment, NameVARCHAR( -))") #以下插入了 5 data cur.execute ("INSERT  intoWriters (Name)VALUES(' Jack London ')") Cur.execute ("INSERT  intoWriters (Name)VALUES(' Honore de Balzac ')") Cur.execute ("INSERT  intoWriters (Name)VALUES(' Lion Feuchtwanger ')") Cur.execute ("INSERT  intoWriters (Name)VALUES(' Emile Zola ')") Cur.execute ("INSERT  intoWriters (Name)VALUES(' Truman Capote ')")

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

import  mysqldb as  mdbimport  sys #连接 MySQL, Get Connected object  con = mdb.connect ( ' localhost ' ,  ' root ' , ,  ' test ' ); with  con: #仍然是, the first step to get a connected cursor object for executing a query  cur = Con.cursor ()  #类似于其他语言的 query function, execute is the Execute query function in Python  cur.execute (" SELECT * from writers ")  #使用 fetchall function, 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 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 #获取 MySQL link object  con = Mdb.connect ( ' localhost ' , ,  ' root ' ,  ' test ' ); with Con:# Gets the object that executes the query  cur = con.cursor ()  #执行那个查询, here is a SELECT statement  cur.execute ( "SELECT * from writers" )  #使用 Cur.rowcount get the number of bars in the result set  NumRows = int  (cur.rowcount)  #循环 numrows times, Remove one row of data at a time  for  i in Range (numrows):  #每次取出一行, Put in row, this is a tuple (id,name)  row = Cur.fetchone ()  #直接输出两个元素  print Row[0 ], Row[1 ] 

Operation Result:

1 Jack London2 Honore de Balzac3 Lion Feuchtwanger4 Emile Zola5 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 #获得 the link object of the MySQL query  con = Mdb.connect ( ' localhost ' , ,  ' root ' ,  ' test ' ) with Con:# Get the dictionary cursor on the connection, note the method obtained,   #每一个 the cursor is actually a subclass of the cursor  cur = con.cursor ( Mdb.cursors.DictCursor)  #执行语句不变  Cur.execute ()  #获取数据方法不变  rows = Cur.fetchall ()  #遍历数据也不变 (more directly than the previous one)  for  row in Rows: #这里, you can use the key-value pair method, the key name to get the data  print "%s%s "% (Row[ "Id" ], Row[ "Name" ]) 

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

import MySQLdb as mdbimport sys#获取数据库的链接对象con = mdb.connect(‘localhost‘, ‘root‘, ‘root‘, ‘test‘)with con:#获取普通的查询 cursorcur = con.cursor()cur.execute("SELECT * FROM Writers")rows = cur.fetchall()#获取连接对象的描述信息desc = cur.descriptionprint ‘cur.description:‘,desc#打印表头，就是字段名字print "%s %3s" % (desc[0][0], desc[1][0])for row in rows:#打印结果print "%2s %3s" % row

Operation Result:

cur.description((‘Id’, 3, 1, 11, 11, 0, 0), (‘Name’, 253, 17, 25, 25, 0, 1))Id Name1 Jack London2 Honore de Balzac3 Lion Feuchtwanger4 Emile Zola5 Truman Capote

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

importas mdbimport syscon = mdb.connect(‘localhost‘‘root‘‘root‘‘test‘)with con:cur = con.cursor()#我们看到，这里可以通过写一个可以组装的 sql 语句来进行cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s",("Guy de Maupasant""4"))#使用 cur.rowcount 获取影响了多少行print"Number of rows updated: %d"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:

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 ")#将文本读入 the IMG objectimg = Fin.Read()#关闭文件Source: Crazy Ant blog www.crazyant.net summarizes and collates fin.Close() except IOError, E:#如果出错, print error messagePrint "Error %d: %s"% (e.args[0],e.args[1]) sys.Exit(1) Try:#链接 MySQL, get the objectconn = mdb.Connect(host=' localhost ', user=' Root ', passwd=' Root ', db=' Test ')#获取执行 Cursorcursor = Conn.cursor ()#直接将数据作为字符串, inserting a databaseCursor.execute ("INSERT into Images SET data= '%s'"%mdb.escape_string (IMG))#提交数据Conn.commit ()#提交之后, then close the cursor and linkCursor.Close() Conn.Close() except MDB. Error, E:#若出现异常, printing informationPrint "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 objectsconn = mdb.Connect(' localhost ',' Root ',' Root ',' Test '); cursor = Conn.cursor ()#执行查询该图片字段的 SQLCursor.execute ("Select Data from Images LIMIT 1")#使用二进制写文件的方法, open a picture file and create it automatically if it doesn't existFout =Open(' Image.png ',' WB ')#直接将数据如文件Fout.Write(Cursor.fetchone () [0])#关闭写入的文件Fout.Close()#释放查询数据的资源Cursor.Close() Conn.Close() except IOError, E:#捕获 IO exception, mostly file write errorsPrint "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 objectsconn = 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 submissionConn.commit () cursor.Close() Conn.Close() except MDB. Error, E:#如果出现了错误, you can roll back, that is, the above three statements are either executed or not executedConn.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.

Python operation MySQL DB instance