如下所示：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
#!/usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "blzhu""""python studyDate：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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
#!/usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "blzhu""""python studyDate：2017"""import pymysqltry: # 获取一个数据库连接，注意如果是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()# 将数据写入数据库 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
#!/usr/bin/python3import pymysqlimport typesdb=pymysql.connect("localhost","root","123456","python");cursor=db.cursor()#创建user表cursor.execute("drop table if exists user")sql="""CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""cursor.execute(sql)#user插入数据sql="""INSERT INTO `user` (`name`, `age`) VALUES('test1', 1),('test2', 2),('test3', 3),('test4', 4),('test5', 5),('test6', 6);"""try: # 执行sql语句 cursor.execute(sql) # 提交到数据库执行 db.commit()except: # 如果发生错误则回滚 db.rollback() #更新id=1sql="update user set age=100 where id='%s'" % (id)try: cursor.execute(sql) db.commit()except: db.rollback() #删除id=2sql="delete from user where id='%s'" % (id)try: cursor.execute(sql) db.commit()except: db.rollback() #查询cursor.execute("select * from user")results=cursor.fetchall()for row in results: name=row[0] age=row[1] #print(type(row[1])) #打印变量类型 <class 'str'> print ("name=%s,age=%s" % \ (age, name)) |
以上这篇python3.4用循环往mysql5.7中写数据并输出的实现方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持服务器之家。
