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

学习的几个地方:

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

#!/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))

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