首页 > 代码库 > Python学习系列(七)( 数据库编程)
Python学习系列(七)( 数据库编程)
一,MySQL-Python插件
Python里操作MySQL数据库,需要Python下安装访问MySQL数据库接口API包即插件,从而使得Python2.7能访问操作MySQL数据库。MySQL软件可以去官网下载:http://www.mysql.com/
二,访问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环境下运行:
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();事物不提交,将回滚。比较:
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语句等等。
本文出自 “IT学习之路” 博客,请务必保留此出处http://zhangbc.blog.51cto.com/6066576/1547100
Python学习系列(七)( 数据库编程)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。