提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
操作mysql
一、导入包
import pymysql
二、数据库连接
db = pymysql.connect(
host = 'localhost',
port = 3306,
user = 'root',
password = 'root',
database = 'boss',
charset = 'utf8mb4'
)
三、创建游标
cursor = db.cursor()
四、操作数据库
1.查询
代码如下(示例):
#查询
sql = "select * from boos"
try:
cursor.execute(sql)
results = cursor.fetchone()
#print(results[1])
resultss = cursor.fetchall()
# print(resultss)
for prow in resultss:
print(prow)
except:
print('error')
cursor.close()
db.close()
2.添加数据
代码如下(示例):
sql = """
insert into boos (title,company,price,education) values
('SAE','微格北京广告营销未融资100-499人','10-15K','补充医疗保险,加班补助')
"""
sqls = "insert into boos (title,company,price,education) values (%s,%s,%s,%s)"
vals = ( ('SAE1','微格','10','加班补助'),('SAE2','微格','10','加班补助'),('SAE3','微格','10','加班补助'),('SAE4','微格','10','加班补助'))
try:
cursor.execute(sql) #单个数据插入
cursor.executemany(sqls,vals) #批量数据插入
#添加操作需要提交
db.commit()
except:
#发生错误时回滚
db.rollback()
print('error')
cursor.close()
db.close()
3.修改数据
代码如下(示例):
sql = "update boos set title='ssss' where id= {} ".format('398')
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
print('error')
cursor.close()
db.close()
4.删除数据
代码如下(示例):
sql = "delete from boos where id = {}".format('398')
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
print('error')
cursor.close()
db.close()
四、关闭游标,数据库连接
cursor.close()
db.close()