Python operation MySQL Instance code tutorial (1/3)

This article introduces Python operation MySQL, execute SQL statement, get result set, traverse result set, get a field, get the name of the table field, insert the picture into the database, execute the transaction and so on various code examples and detailed introduction, the code mostly, is a table sumptuous beautiful code feast.

Instance 1, get the version of MySQL

To install the MySQL module in the Windows environment for Python development, see my other article:

The code is as follows

Copy Code

#-*-Coding: UTF-8-*- #安装MYSQL DB for Python import mysqldb as mdb con = None Try:     #连 How to connect to MySQL: Connect (' IP ', ' user ', ' password ', ' dbname ')     con = mdb.connect (' localhost ', ' root ',         ' root ', ' test ');     #所有的查询     cur = con.cursor ()    which is running on a module cursor that connects con   #执行一个查询     cur.execute ("Select VERSION ()")     #取得上个查询的结果, is a single result     data = Cur.fetchone ()     print "Database version:%s"% Data Finally: &NB sp;   if con:         #无论如何, connect remember to close          con.close () execution result: 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:

The code is as follows

Copy Code

#-*-Coding:utf-8-*- # from Www.crazyant.net 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 the results (see in phpMyAdmin, see the same result on the 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:

code is as follows

copy code

#-*-Coding: UTF-8-*- # Source: Www.crazyant.net Collation Summary Import MYSQLDB as MDB Import sys #连接mysql, getting connected objects Con = mdb.co Nnect (' localhost ', ' root ', ' root ', ' test '); With con:     #仍然是, the first step is to get the cursor object for the connection, which is used 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 saved in rows     rows = Cur.fetchall ()      #依次遍历结果集, each element is found to be a record in the table, with a tuple to display     for row in rows:     & nbsp;   Print row Run Result: (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:

The code is as follows

Copy Code

#-*-Coding: UTF-8-*- # Source: Crazy Ant Blog www.crazyant.net summary collation Import mysqldb as MDB Import sys #获取mysql的链接对象 Con = Mdb.connect (' localhost ', ' root ', ' root ', ' test '); With con:     #获取执行查询的对象     cur = con.cursor ()     # Execute the query with the SELECT statement     cur.execute ("SELECT * from writers")     # Use Cur.rowcount to get the number of bars for the result set     numrows = Int (cur.rowcount)     #循环numrows次, Fetch one row of data at a time     for I in Range (numrows):         #每次取出一行, In row, this is a tuple (id,name)         row = Cur.fetchone ()          #直接输出两个元素         print row[0], row[1]

Run Result:

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

Home 1 2 3 last