#!/usr/bin/env python# -*- coding:utf-8 -*-# __author__ = blzhupython study
date：2017import pymysql# import mysqldb #python2中的产物try:# 获取一个数据库连接，注意如果是utf-8类型的，需要制定数据库conn = pymysql.connect(host='localhost', user='root', passwd='root', db='zbltest1', port=3306, charset='utf8')
cur = conn.cursor() # 获取一个游标for i in range(1, 10):
zbl_id = str(i)
zbl_name = 'zbl'+str(i)
zbl_gender = 'man'# print(%s,%s,%s % (zbl_id,zbl_name,zbl_gender))# sql = insert student values (id='%s',name='%s',gender='%s') % (zbl_id,zbl_name,zbl_gender)sql = insert student values ('%s','%s','%s') % (zbl_id, zbl_name, zbl_gender)# print(sql)
cur.execute(sql)
conn.commit()# 将数据写入数据库# try:# cur.execute(sql)# cur.commit()# except:
# cur.rollback()#cur.execute(insert into 'student' ('id','name','gender') values (%s,%s,%s ,(zbl_id,zbl_name,zbl_gender,)))#cur.execute(insert into 'student' ('id','name','gender') values (zbl_id,zbl_name,zbl_gender))
# cur.execute(insert student values (zbl_id,zbl_name,zbl_gender))# cur.execute(insert student values ('4', 'zbl4', 'man'))# 正确#cur.execute(insert into 'student' ('id','name','gender') values ('4', 'zbl4', 'man'))
#错误#cur.execute(insert student ('id','name','gender') values ('4', 'zbl4', 'man'))cur.execute('select * from student')# data=cur.fetchall()for d in cur:
# 注意int类型需要使用str函数转义print(id: + str(d[0]) + ' 名字： ' + d[1] + 性别： + d[2])print(row_number:, (cur.rownumber))# print('hello')cur.close() # 关闭游标conn.close()
# 释放数据库资源except exception:print(发生异常)
上面代码是对的，但是是曲折的。
下面整理一下：
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 # __author__ = blzhu 4 5 python study 6 date：2017 7 8 import pymysql 9 try:10 # 获取一个数据库连接，注意如果是utf-8类型的，需要制定数据库11 conn = pymysql.connect(host='localhost', user='root', passwd='root', db='zbltest1', port=3306, charset='utf8')12 cur = conn.cursor() # 获取一个游标13 for i in range(1, 10):14 zbl_id = str(i)15 zbl_name = 'zbl'+str(i)16 zbl_gender = 'man'17 # print(%s,%s,%s % (zbl_id,zbl_name,zbl_gender))18 # sql = insert student values (id='%s',name='%s',gender='%s') % (zbl_id,zbl_name,zbl_gender)19 sql = insert student values ('%s','%s','%s') % (zbl_id, zbl_name, zbl_gender)20 # print(sql)21 cur.execute(sql)22 conn.commit()# 将数据写入数据库23 cur.execute('select * from student')24 # data=cur.fetchall()25 for d in cur:26 # 注意int类型需要使用str函数转义27 print(id: + str(d[0]) + ' 名字： ' + d[1] + 性别： + d[2])28 print(row_number:, (cur.rownumber))29 # print('hello')30 31 cur.close() # 关闭游标32 conn.close() # 释放数据库资源33 except exception:34 print(发生异常)
学习的几个地方:
#!/usr/bin/python3 2
import pymysql 3 import types 4 5 db=pymysql.connect(localhost,root,123456,python);
cursor=db.cursor() 8 9 #创建user表10
cursor.execute(drop table if exists user)11 sql=create table if not exists `user` (12 `id` int(11) not null auto_increment,13 `name` varchar(255) not null,14 `age` int(11) not null,15
primary key (`id`)16 ) engine=innodb default charset=utf8 auto_increment=017 18 cursor.execute(sql)19 20 21 #user插入数据22
sql=insert into `user` (`name`, `age`) values23 ('test1', 1),24 ('test2', 2),25 ('test3', 3),26 ('test4', 4),27 ('test5', 5),28 ('test6', 6);29 30 try:31
# 执行sql语句32 cursor.execute(sql)33 # 提交到数据库执行34 db.commit()35 except:36 # 如果发生错误则回滚37 db.rollback()38 39 40 #更新41 id=142 sql=update user set age=100 where id='%s' % (id)43 try:44
cursor.execute(sql)45 db.commit()46 except:47 db.rollback()48 49 #删除50 id=251 sql=delete from user where id='%s' % (id)52 try:53 cursor.execute(sql)54 db.commit()55 except:56 db.rollback()57 58 59
#查询60 cursor.execute(select * from user)61 62 results=cursor.fetchall()63 64 for row in results:65 name=row[0]66 age=row[1]67 #print(type(row[1])) #打印变量类型 <class 'str'>68 69
print (name=%s,age=%s % \70 (age, name))
view code
以上就是python3.4使用循环的实例详解的详细内容。