python3.4用循环往mysql5.7中写数据并输出的实现方法

Python  /  管理员 发布于 7年前   163

如下所示：

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

上面代码是对的，但是是曲折的。

下面整理一下：

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

#!/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中写数据并输出的实现方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。