Python 3 Mysql 增删改查
时间：2021-07-01 10:21:17
帮助过：47人阅读
pymysql
import datainfo
import time
#获取参数
host =
datainfo.host
username =
datainfo.username
password =
datainfo.password
database =
datainfo.db
print()
#测试数据库连接
def testconnect():
#打开数据库链接
db =
pymysql.connect(host,username,password,database)
#使用cursor() 方法创建一个游标对象 cursor
cursor =
db.cursor()
#使用execute()方法执行SQL查询
cursor.execute("select version()")
#使用fetchone ()获取单条数据
data =
cursor.fetchone()
print(data)
db.close()
#插入数据库
def InsertDate():
#打开数据库链接
db = pymysql.connect(host,username,password,database,charset=
‘utf8‘)
#使用cursor() 方法创建一个游标对象 cursor
cursor =
db.cursor()
create_time = time.strftime(
‘%Y-%m-%d %H:%M:%S‘)
update_time = time.strftime(
‘%Y-%m-%d %H:%M:%S‘)
start_time = time.strftime(
‘%Y-%m-%d %H:%M:%S‘)
end_time = time.strftime(
‘%Y-%m-%d %H:%M:%S‘)
remark =
"测试插入信息"
print(
"开始")
#Sql 插入语句
sql =
"insert into demo(start_time,end_time,creat_time,update_time,remark) " "VALUES (‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘)" %
(start_time,end_time,create_time,update_time,remark)
try:
#执行sql
print(
"执行插入")
tt =
cursor.execute(sql)
print(tt)
db.commit()
except UnicodeEncodeError as e :
#发生错误时回滚
print(e)
db.rollback()
db.close()
#查询操作
def selectData():
db = pymysql.connect(host, username, password, database, charset=
‘utf8‘)
# 使用cursor() 方法创建一个游标对象 cursor
cursor =
db.cursor()
sql =
"select * from demo where id >=‘%d‘" %(1
)
try:
#执行sql
print(
"执行查询")
cursor.execute(sql)
results =
cursor.fetchall()
for row
in results:
id =
row[0]
start_time = row[1
]
end_time = row[2
]
create_time = row[3
]
update_time = row[4
]
remark = row[5
]
#打印结果
print(
"id = %d,start_time=%s,end_time=%s,create_time=%s,update_time=%s,remark=%s" %
(id,start_time,end_time,create_time,update_time,remark))
db.commit()
except UnicodeEncodeError as e :
#发生错误时回滚
print(e)
db.close()
#更新操作
def update_data():
db = pymysql.connect(host, username, password, database, charset=
‘utf8‘)
# 使用cursor() 方法创建一个游标对象 cursor
cursor =
db.cursor()
update_time = time.strftime(
‘%Y-%m-%d %H:%M:%S‘)
sql =
"update demo set update_time =‘%s‘ where id >=‘%d‘ " %(update_time,1
)
try:
#执行sql
print(
"执行更新")
cursor.execute(sql)
db.commit()
except UnicodeEncodeError as e :
#发生错误时回滚
print(e)
db.rollback()
db.close()
#删除操作
def delete_Date():
db = pymysql.connect(host, username, password, database, charset=
‘utf8‘)
# 使用cursor() 方法创建一个游标对象 cursor
cursor =
db.cursor()
sql =
"delete from demo where id <‘%d‘ " %(1
)
try:
#执行sql
print(
"执行删除")
cursor.execute(sql)
db.commit()
except UnicodeEncodeError as e :
#发生错误时回滚
print(e)
db.rollback()
db.close()
if __name__ ==
‘__main__‘:
testconnect()
InsertDate()
selectData()
update_data()
delete_Date()
Python 3 Mysql 增删改查
标签：获取参数 val test 对象 参数 回滚 style 信息 utf8
