python3.4用循环往mysql5.7中写数据并输出

!/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("发生异常")

学习的几个地方:

http://blog.csdn.net/nuli888/article/details/51960571

 1 #!/usr/bin/python3
 2 import pymysql
 3 import types
 4 
 5 db=pymysql.connect("localhost","root","123456","python");
 6 
 7 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`) VALUES
23 (‘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=1
42 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=2
51 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

http://www.runoob.com/python/python-mysql.html

 

http://www.cnblogs.com/lei0213/p/6002921.html

 

http://blog.csdn.net/magicbreaker/article/details/41811519

 

http://blog.csdn.net/bwlab/article/details/51146640

python3.4用循环往mysql5.7中写数据并输出

标签：gic   ide   rownumber   break   usr   地方   comm   ldb   整理