Python operation MySQL Instance

This article describes how Python operates MySQL, executes SQL statements, obtains result sets, iterates through result sets, obtains a field, gets the name of a table column, inserts a picture into a database, executes various code instances such as a transaction, and describes it in detail.

Example 1, getting the MySQL version

#!/usr/bin/env python import mysqldb as mdb try:con=mdb.connect (' localhost ', ' root ', ' 123 ', ' Test ') #连接mysql的方法 C Ur=con.cursor () #所有的查询, Cur.execute (' SELECT VERSION () ') running on the cursor of a module connecting con, #执行一个查询 data = Cur.fetchone () #取得上面 The result of the query is a single result cur.close () #关闭 print "Database version:%s"%data con.close () except MDB. Error:print "Mysql Error%d:%s"% (E.args[0], e.args[1])

Example 2, creating a table and inserting input

#!/usr/bin/env python

import MySQLdb as MDB

Try:

con=mdb.connect (' localhost ', ' root ', ' 123 ', ' Test ')

cur=con.cursor ()

Cur.execute ("CREATE TABLE IF not EXISTS writers (Id INT PRIMARY KEY auto_increment, Name VARCHAR)")

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 ')")

except MDB. Error:

print "Mysql Error%d:%s"% (E.args[0], e.args[1])

running results, logging into the database for viewing

mysql> SELECT * from writers;

+----+-------------------+

| Id | Name |

+----+-------------------+

| 1 | Jack London |

| 2 | Honore de Balzac |

| 3 | Lion Feuchtwanger |

| 4 | Emile Zola |

| 5 | Truman Capote |

+----+-------------------+

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

#!/usr/bin/env python

import MySQLdb as MDB

Try:

con=mdb.connect (' localhost ', ' root ', ' 123 ', ' Test ')

cur=con.cursor ()

Cur.execute ("SELECT * from writers")

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

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

Print Row

except MDB. Error:

print "Mysql Error%d:%s"% (E.args[0], e.args[1])

Run Results

(1L, ' Jack London ')

(2L, ' Honore de Balzac ')

(3L, ' Lion feuchtwanger ')

(4L, ' Emile Zola ')

(5L, ' Truman Capote ')

The above code is used to take all the results out, but when printing is a tuple of each line of printing, now we use the method to extract the individual data.

#!/usr/bin/env python

import MySQLdb as MDB

Try:

con=mdb.connect (' localhost ', ' root ', ' 123 ', ' Test ')

cur=con.cursor ()

Cur.execute ("SELECT * from writers")

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

for I in Range (numrows): #循环numbrows次, fetching one row of data at a time

row = Cur.fetchone () #每次取出一行, put in row, this is a tuple (id,name)

print row[0],row[1] #直接输出两个元素

except MDB. Error:

print "Mysql Error%d:%s"% (E.args[0], e.args[1]

Run Results

1 Jack London

2 Honore de Balzac

3 Lion Feuchtwanger

4 Emile Zola

5 Truman Capote

This article is from the "Server" blog, so be sure to keep this source http://zhangfang2012.blog.51cto.com/6380212/1633937

Python operation MySQL instance