python操作数据库

1、python连接mysql

首先安装pymysql  pip install PyMySQL

import pymysql.cursors
from datetime import datetime

# 连接数据库
connect = pymysql.Connect(
host='localhost',
port=3306,
user='root',
passwd='',
db='guest',
charset='utf8'
)

# 获取游标
cursor = connect.cursor()

# 插入数据
sql = "INSERT INTO sign_event VALUES (14,'小米发布会','地铁站',100,100,'2016-08-20 00:25:42','2016-08-20 00:25:42');"
print(sql)
cursor.execute(sql)
connect.commit()
print('成功插入', cursor.rowcount, '条数据')

#修改数据
sql = "UPDATE sign_event SET name = '葫芦娃' WHERE id = 14 "
cursor.execute(sql)
connect.commit()
print('成功修改', cursor.rowcount, '条数据')

# 查询数据
sql = "SELECT name,status FROM sign_event WHERE id = 14 "
cursor.execute(sql)
for row in cursor.fetchall():
print("name:%sstatus:%s" % row)
print('共查找出', cursor.rowcount, '条数据')

# 事务处理
sql_1 = "UPDATE sign_event SET `limit` = `limit` + 100 WHERE id = 14 "
sql_2 = "UPDATE sign_event SET `limit` = `limit` + 200 WHERE id = 14 "
sql_3 = "UPDATE sign_event SET `limit` = `limit` - 100 WHERE id = 14 "

try:
cursor.execute(sql_1)
cursor.execute(sql_2)
cursor.execute(sql_3)
except Exception as e:
connect.rollback() # 事务回滚
print('事务处理失败', e)
else:
connect.commit() # 事务提交
print('事务处理成功', cursor.rowcount)

# 删除数据
sql = "DELETE FROM sign_event WHERE id = 14"
cursor.execute(sql)
connect.commit()
print('成功删除', cursor.rowcount, '条数据')

# 关闭连接
cursor.close()
connect.close()