Python学习系列(七)( 数据库编程)

一,MySQL-Python插件
      Python里操作MySQL数据库,需要Python下安装访问MySQL数据库接口API包即插件,从而使得Python2.7能访问操作MySQL数据库。MySQL软件可以去官网下载:http://www.mysql.com/; MySQLdb插件下载:http://sourceforge.net/projects/mysql-python/files/latest/download
二,访问MySQL数据库
    1,连接数据库mysql
         基本格式:connect ([host=]'ip',[user=]'user',[passwd=]'password',[db=]'dbname')
    2,数据库的基本操作
     1)create创建表
 import MySQLdb
#connect to a database 'test'
conn=MySQLdb.connect(host='localhost',user='root',passwd='zbc123',db='test')
cursor=conn.cursor()
#create a table
cursor.execute('create table \
test(ID int primary key auto_increment,Name char(25))')
#Closing database
cursor.close()
conn.close()
     2)fetchall访问:
 import MySQLdb
#connect to a database 'test'
conn=MySQLdb.connect(host='localhost',user='root',passwd='zbc123',db='test')
cursor=conn.cursor()
#fetch datas
n=cursor.execute('select * from test;')
r=cursor.fetchall()
print n,r
#Closing database
cursor.close()
conn.close() >>> ================================ RESTART ================================
>>>
3 ((4L, 'zhangbc'), (5L, 'lis08'), (6L, 'wangw'))
>>>
 
在Mysql5.6环境下运行:
Python学习系列(七)( 数据库编程)
3)insert向表中插入数据:
 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 datas
n=cursor.execute('select * from test;')
r=cursor.fetchall()
print n,r
#Closing database
cursor.close()
conn.close() >>> ================================ RESTART ================================
>>>
4 ((1L, 'zhangbc'), (2L, 'lis'), (3L, 'wangw'), (4L, 'zhanghua'))
注意:一定要写上conn.commit();事物不提交,将回滚。比较:
Python学习系列(七)( 数据库编程)
 
Python学习系列(七)( 数据库编程)
4)update修改表中数据:
 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 datas
n=cursor.execute('select * from test;')
r=cursor.fetchall()
print n,r
#Closing database
cursor.close()
conn.close() >>> ================================ RESTART ================================
>>>
4 ((1L, 'zhangbc'), (2L, 'Lisi08'), (3L, 'wangw'), (4L, 'zhanghua'))
5)delete删除表中数据:
 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 datas
n=cursor.execute('select * from test;')
r=cursor.fetchall()
print n,r
#Closing database
cursor.close()
conn.close() >>> ================================ RESTART ================================
>>>
3 ((1L, 'zhangbc'), (2L, 'Lisi08'), (3L, 'wangw'))
     6)关于select及其遍历:
      i)使用元组tuple与fetchone结合
 import MySQLdb
#connect to a database 'test'
conn=MySQLdb.connect(host='localhost',user='root',passwd='zbc123',db='test')
cursor=conn.cursor()
#fetch datas
cursor.execute('select * from test;')
#获得结果集的记录
numrows=int(cursor.rowcount)
#循环,取行数据
for i in range(numrows):
row=cursor.fetchone()
print row[0],row[1]
#Closing database
cursor.close()
conn.close() >>> ================================ RESTART ================================
>>>
4 zhangbc
5 lis08
6 wangw

ii)使用字典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,每一个cursor其实都是cursor的子类
cur=conn.cursor(mdb.cursors.DictCursor)
#fetch datas
cur.execute('select * from test;')
#获得结果集
rows=cur.fetchall()
#循环,取行数据
for row in rows:
print '%s %s'%(row['ID'],row['Name'])
#Closing database
cur.close()
conn.close() >>> ================================ RESTART ================================
>>>
4 zhangbc
5 lis08
6 wangw

iii)获取单个表的字段名及其信息

 #-*- 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,每一个cursor其实都是cursor的子类
cur=conn.cursor()
#fetch datas
cur.execute('select * from test;')
#获得结果集
rows=cur.fetchall()
#获得链接对象的描述信息
desc=cur.description
print 'cur.description:',desc
#打印表头
print '%2s %3s'%(desc[0][0],desc[1][0])
#循环,取行数据
for row in rows:
print '%2s %3s'%row
#Closing database
cur.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
三,小结
     本文主要介绍了Python下如何访问并操数据库的基本知识,如:如何连接数据库,如何执行执行SQL语句等等。
 
上一篇:Swift基础--Swift中的分类以及在分类中扩展init方法的注意事项


下一篇:Python学习系列(四)(列表及其函数)