python3.4 An implementation that writes and outputs data to mysql5.7 using a loop
- 2020-06-07 04:42:03
- OfStack
As shown below:
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "blzhu"
"""
python study
Date : 2017
"""
import pymysql
# import MySQLdb #python2 The products of
try:
# To obtain 1 Three database connections, note if yes UTF-8 Type, you need to specify the database
conn = pymysql.connect(host='localhost', user='root', passwd='root', db='zbltest1', port=3306, charset='utf8')
cur = conn.cursor() # To obtain 1 A 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()# Write the data to the database
# 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')")# correct
#cur.execute("INSERT INTO 'student' ('id','name','gender') VALUES ('4', 'zbl4', 'man')")# error
#cur.execute("INSERT student ('id','name','gender') VALUES ('4', 'zbl4', 'man')")
cur.execute('select * from student')
# data=cur.fetchall()
for d in cur:
# Pay attention to int The type needs to be used str Function to escape
print("ID: " + str(d[0]) + ' Name: ' + d[1] + " Gender: " + d[2])
print("row_number:", (cur.rownumber))
# print('hello')
cur.close() # Close the cursor
conn.close() # Release database resources
except Exception:
print(" An exception occurs ")
The above code is correct, but it is tortuous.
The following is the summary:
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "blzhu"
"""
python study
Date : 2017
"""
import pymysql
try:
# To obtain 1 Three database connections, note if yes UTF-8 Type, you need to specify the database
conn = pymysql.connect(host='localhost', user='root', passwd='root', db='zbltest1', port=3306, charset='utf8')
cur = conn.cursor() # To obtain 1 A 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()# Write the data to the database
cur.execute('select * from student')
# data=cur.fetchall()
for d in cur:
# Pay attention to int The type needs to be used str Function to escape
print("ID: " + str(d[0]) + ' Name: ' + d[1] + " Gender: " + d[2])
print("row_number:", (cur.rownumber))
# print('hello')
cur.close() # Close the cursor
conn.close() # Release database resources
except Exception:
print(" An exception occurs ")
#!/usr/bin/python3
import pymysql
import types
db=pymysql.connect("localhost","root","123456","python");
cursor=db.cursor()
# create user table
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 Insert data
sql="""INSERT INTO `user` (`name`, `age`) VALUES
('test1', 1),
('test2', 2),
('test3', 3),
('test4', 4),
('test5', 5),
('test6', 6);"""
try:
# perform sql statements
cursor.execute(sql)
# Commit to the database for execution
db.commit()
except:
# If an error occurs, roll back
db.rollback()
# update
id=1
sql="update user set age=100 where id='%s'" % (id)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
# delete
id=2
sql="delete from user where id='%s'" % (id)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
# The query
cursor.execute("select * from user")
results=cursor.fetchall()
for row in results:
name=row[0]
age=row[1]
#print(type(row[1])) # Print variable type <class 'str'>
print ("name=%s,age=%s" % \
(age, name))