Python database operations Common features use detailed (CREATE TABLE/Insert data/Get Data) _python

Example 1, get the MySQL version

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:
Database version:5.5.25

Instance 2, creating a table and inserting data

Copy Code code as follows:

#-*-Coding:utf-8-*-
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 ')")

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

Copy Code code as follows:

#-*-Coding:utf-8-*-
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

Copy Code code as follows:

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

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 www.server110.com 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 www.server110.com 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

Copy Code code as follows:

Run Result: 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 www.server110.com 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