如下所示:
#!/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])) #打印變量類型print ("name=%s,age=%s" % \ (age, name))
以上這篇python3.4用循環往mysql5.7中寫數據并輸出的實現方法就是小編分享給大家的全部內容了,希望能給大家一個參考,也希望大家多多支持腳本之家。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
