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

时间：2021-06-29 21:13:19
#!/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","","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

相关文章