Python database operations frequently used features (CREATE TABLE/Insert data/Get Data)

Example 1, get MySQL version

Copy the 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 ');
#所有的查询, all running on the cursor of a module connected to 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 Result:
Database version:5.5.25

Example 2, creating a table and inserting data

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

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

Copy the Code code as follows:

#-*-Coding:utf-8-*-
Import MySQLdb as MDB
Import Sys
#连接mysql, gets the connected object
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');
With Con:
#仍然是, the first step is to get a connected cursor object for executing the query
cur = con.cursor ()
#类似于其他语言的query函数, execute is the Execute query function in Python
Cur.execute ("SELECT * From writers")
#使用fetchall函数, 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
For row in rows:
Print row

Copy the Code code as follows:

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

Example 4, using the dictionary cursor to get the result set (you can use the table field name to access the value)

Copy the Code code as follows:

#-*-Coding:utf-8-*-
# Source: Crazy Ant 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 obtained,
#每一个cursor其实都是cursor的子类
cur = con.cursor (mdb.cursors.DictCursor)
#执行语句不变
Cur.execute ("SELECT * From writers")
#获取数据方法不变
rows = Cur.fetchall ()
#遍历数据也不变 (a little more directly 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%s"% (row["Id"], row["Name"])

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

Copy the Code code as follows:

#-*-Coding:utf-8-*-
# Source: Crazy Ant 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 name of the field
Print "%s%3s"% (Desc[0][0], desc[1][0])
For row in rows:
#打印结果
Print "%2s%3s"% row

Copy the Code code as follows:

Running results: 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)

Copy the Code code as follows:

#-*-Coding:utf-8-*-
# Source: Crazy Ant 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 how many rows are affected
Print "Number of rows updated:%d"% cur.rowcount

Results:
Copy CodeThe code is as follows:

Number of rows Updated:1

