Python Learning Series (vii) (database programming)

One, Mysql-python plug-in

Python operates the MySQL database and requires Python to install access to the MySQL Database Interface API package as a plug-in, which allows Python2.7 to access the operation MySQL database. MySQL software can go to the official website to download: http://www.mysql.com/

Second, access MySQL database

1, connection database MySQL

Basic Format: Connect ([host=]' IP ',[user=]' user ',[passwd=]' password ',[db=] ' dbname ')

2, basic operation of the database

1) CREATE table

Import Mysqldb#connect to a database ' test ' conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' zbc123 ', db= ' Test ') cursor=conn.cursor () #create a tablecursor.execute (' CREATE TABLE test (ID int primary key Auto_increment,name char ( ) ') #Closing Databasecursor.close () conn.close ()

2)Fetchall access:

Import Mysqldb#connect to a database ' test ' conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' zbc123 ', db= ' Test ') cursor=conn.cursor () #fetch datasn=cursor.execute (' select * from Test; ') R=cursor.fetchall () Print n,r#closing databasecursor.close () conn.close () >>> ============================= = = = RESTART ================================>>> 3 (4L, ' ZHANGBC '), (5L, ' lis08 '), (6L, ' Wangw ')) >>>

Operating in a Mysql5.6 environment:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/47/E4/wKioL1QCxY2yVEa7AAGfJZhmYtw943.jpg "title=" Mys.jpg "alt=" Wkiol1qcxy2yvea7aagfjzhmytw943.jpg "/>

3) Insert the data into the table:

Import Mysqldb#connect to a database ' test ' conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' zbc123 ', db= ' Test ') cursor=conn.cursor () #insert data into table ' test ' mysql= ' "INSERT INTO Test (Id,sname) VALUES (4, ' Zhanghua ') ' Cursor.execute (MySQL) conn.commit () #below mysql5.0 needed#fetch datasn=cursor.execute (' select * from Test; ') R=cursor.fetchall () Print n,r#closing databasecursor.close () conn.close () >>> ============================= = = = RESTART ================================>>>4 ((1L, ' ZHANGBC '), (2L, ' lis '), (3L, ' Wangw '), (4L, ' Zhanghua ') )

Note: Be sure to write on conn.commit (); Things are not committed and will be rolled back. Compare:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/47/E4/wKioL1QCx32TZku4AASrbvOM2cI869.jpg "title=" difference. jpg "alt=" Wkiol1qcx32tzku4aasrbvom2ci869.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/47/E2/wKiom1QCxoDQgmMOAATzUDZNdcU530.jpg "title=" Qb4.jpg "alt=" Wkiom1qcxodqgmmoaatzudzndcu530.jpg "/>

4)Update modify data in table:

Import Mysqldb#connect to a database ' test ' conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' zbc123 ', db= ' Test ') cursor=conn.cursor () #update Data of the table ' test ' mysql= ' ' Update test set sname= ' Lisi08 ' where id=2 ' ' Cursor.execute (MySQL) conn.commit () #below mysql5.0 needed#fetch datasn=cursor.execute (' select * from Test; ') R=cursor.fetchall () Print n,r#closing databasecursor.close () conn.close () >>> ============================= = = = RESTART ================================>>>4 ((1L, ' ZHANGBC '), (2L, ' Lisi08 '), (3L, ' Wangw '), (4L, ' Zhanghua '))

5) Delete Deletes the data from the table:

Import Mysqldb#connect to a database ' test ' conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' zbc123 ', db= ' Test ') cursor=conn.cursor () #delete Data of the table ' test ' mysql= ' ' delete from test where id=4 ' Cursor.execute (MySQL) Conn.commit () #below mysql5.0 needed#fetch datasn=cursor.execute (' select * from Test; ') R=cursor.fetchall () Print n,r#closing databasecursor.close () conn.close () >>> ============================= = = = RESTART ================================>>>3 ((1L, ' ZHANGBC '), (2L, ' Lisi08 '), (3L, ' Wangw '))

6) About Select and its traversal:

i) using tuple tuples with fetchone

Import Mysqldb#connect to a database ' test ' conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' zbc123 ', db= ' Test ') cursor=conn.cursor () #fetch datascursor.execute (' select * from Test; ') #获得结果集的记录numrows =int (Cursor.rowcount) #循环, take the row data for I in range (numrows): Row=cursor.fetchone () print row[0],row[1] #Clos ing Databasecursor.close () conn.close () >>> ================================ RESTART ====================== ==========>>> 4 Zhangbc5 lis086 Wangw

II) using the dictionary cursor

#-*-coding:utf-8-*-import MySQLdb as mdb#connect to a database ' test ' conn=mdb.connect (host= ' localhost ', user= ' root ', Passwd= ' zbc123 ', db= ' test ') with Conn: #获取连接上的字典cursor, each cursor is actually a subclass of the cursor cur=conn.cursor ( Mdb.cursors.DictCursor) #fetch datascur.execute (' select * from Test; ') #获得结果集rows =cur.fetchall () #循环, fetch row data for row Rows:print '%s%s '% (row[' ID '],row[' Name ') #Closing databasecur.close () Conn.close () >>> ================================ RESTART ================================>>> 4 Zhangbc5 lis086 Wangw

       iii) gets the field names and their information for a single table

#-*- coding:utf-8 -*-import mysqldb as mdb#connect to a database  ' Test ' conn=mdb.connect (host= ' localhost ', user= ' root ', passwd= ' zbc123 ', db= ' test ') with conn:      #获取连接上的字典cursor, each cursor is actually a subclass of the cursor     cur=conn.cursor () #fetch   Datascur.execute (' select * from test; ') #获得结果集rows =cur.fetchall () #获得链接对象的描述信息desc =cur.descriptionprint  ' cur.description: ', desc# print header print  '% 2s %3s '% (desc[0][0],desc[1][0]) #循环, fetching rows of data for row in rows:    print  '%2s %3s '%row#closing databasecur.close () conn.close () >>> ============================= === restart ================================>>> cur.description:  (' ID ',  3,  1, 11, 11, 0, 0),  (' Name ',  254, 7, 25, 25, 0, 1)) Id name 4 zhangbc 5 lis08 6 wangw

Three, summary

This article mainly introduces the basic knowledge of how to access and execute databases under Python, such as how to connect a database, execute SQL statements, and so on.

This article is from the "It Learning path" blog, so be sure to keep this source http://zhangbc.blog.51cto.com/6066576/1547100

Python Learning Series (vii) (database programming)