python3.4使用循环的实例详解-木庄网络博客

本文摘自php中文网，作者零下一度，侵删。

#!/usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "blzhu""""python study
Date：2017"""import 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=0"""17 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