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



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

下面整理一下：





#!/usr/bin/env python

# -*- coding:utf-8 -*-

# __author__ = "blzhu"

"""

python study

Date：2017

"""

import pymysql

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()# 将数据写入数据库

  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/python3

import pymysql

import types



db=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=1

sql="update user set age=100 where id='%s'" % (id)

try:

  cursor.execute(sql)

  db.commit()

except:

  db.rollback()

  

#删除

id=2

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