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:

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:

?

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 delete data in 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 data for row in rows:    print  '%s %s '% (row[' ID '],row[' Name ') #Closing  databasecur.close () conn.close () >>> ====================== ========== restart ================================>>> 4 zhangbc5 lis086  wangw 

         III) Get 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.

Python Learning Series (vii) (database programming)