python连接mysql实例分享
示例一
#coding=UTF-8
importsys
importMySQLdb
importtime
reload(sys)
sys.setdefaultencoding('utf-8')
defconnectDemo():
returnMySQLdb.Connection("127.0.0.1","root","root","demo",3306,charset="utf8")
if__name__=='__main__':
begin=time.time()
conn=connectDemo()
cursor=conn.cursor()
sql="""
showtables
"""
count=cursor.execute(sql)
rows=cursor.fetchall()
cursor.close()
conn.close()
print"========demo库共:%s张表============"%(count)
print'耗时:%s秒'%(time.time()-begin)
示例二
importMySQLdb
conn=MySQLdb.connect(host="localhost",
user="root",
passwd="123456",
db="test")
cursor=conn.cursor()
cursor.execute("select*fromhard")
res=cursor.fetchall()
forxinres:
printx
cursor.close()
conn.close()
示例三
1安装Python的Mysql包
root@10.1.1.45:~#apt-getinstallpython-mysqldb root@10.1.1.45:~#python Python2.5.2(r252:60911,Jan42009,21:59:32) [GCC4.3.2]onlinux2 Type"help","copyright","credits"or"license"formoreinformation. >>>importMySQLdb >>>
这里导入MySQLdb没有报错,就说明安装成功.
2下面就可以连接数据库,可以进行增删改操作.
root@10.1.1.45:python#catcreate.py
#!/usr/bin/envpython
#coding=utf-8
#导入相关模块
importMySQLdb
#建立和mysql数据库的连接
conn=MySQLdb.connect(host='localhost',user='root',passwd='davehe')
#获取游标
curs=conn.cursor()
#执行SQL,创建一个数据库
curs.execute("createdatabasepythondb")
#选择连接哪个数据库
conn.select_db('pythondb')
#执行SQL,创建一个表
curs.execute("createtabletest(idint,messagevarchar(50))")
#插入一条记录
value=[1,"davehe"]
curs.execute("insertintotestvalues(%s,%s)",value)
#插入多条记录
values=[]
foriinrange(20):
values.append((i,'hellomysqldb'+str(i)))
curs.executemany("insertintotestvalues(%s,%s)",values)
#提交修改
conn.commit()
#关闭游标连接,释放资源
curs.close()
#关闭连接
conn.close()
root@10.1.1.45:python#./create.py
3下面利用python查看mysql里刚添加的记录.
root@10.1.1.45:python#catselect.py
#!/usr/bin/envpython
#coding=utf-8
#导入相关模块
importMySQLdb
#建立和mysql数据库的连接
conn=MySQLdb.connect(host='localhost',user='root',passwd='hc1226')
#获取游标
curs=conn.cursor()
#选择连接哪个数据库
conn.select_db('pythondb')
#查看共有多少条记录
count=curs.execute('select*fromtest')
print"一共有%s条记录"%count
#获取一条记录,以一个元组返回
result=curs.fetchone()
print"当前的一条记录ID:%smessage:%s"%result
#获取后10条记录,由于之前执行了getchone(),所以游标已经指到第二条记录,下面也就从第二条记录开始返回
results=curs.fetchmany(10)
forrinresults:
printr
#重置游标位置,0,为偏移量,mode=relative(默认)
curs.scroll(0,mode='absolute')
#获取所有记录
results=curs.fetchall()
forrinresults:
printr
#提交修改
conn.commit()
#关闭游标连接,释放资源
curs.close()
#关闭连接
conn.close()
root@10.1.1.45:python#./select.py 一共有21条记录 当前的一条记录ID:1message:davehe (0L,'hellomysqldb0') (1L,'hellomysqldb1') (2L,'hellomysqldb2') (3L,'hellomysqldb3') (4L,'hellomysqldb4') (5L,'hellomysqldb5') (6L,'hellomysqldb6') (7L,'hellomysqldb7') (8L,'hellomysqldb8') (9L,'hellomysqldb9') (1L,'davehe') (0L,'hellomysqldb0') (1L,'hellomysqldb1') (2L,'hellomysqldb2') (3L,'hellomysqldb3') (4L,'hellomysqldb4') (5L,'hellomysqldb5') (6L,'hellomysqldb6') (7L,'hellomysqldb7') (8L,'hellomysqldb8') (9L,'hellomysqldb9') (10L,'hellomysqldb10') (11L,'hellomysqldb11') (12L,'hellomysqldb12') (13L,'hellomysqldb13') (14L,'hellomysqldb14') (15L,'hellomysqldb15') (16L,'hellomysqldb16') (17L,'hellomysqldb17') (18L,'hellomysqldb18') (19L,'hellomysqldb19')