Python操作mysql
用python代码连接数据库,并发送指令(sql语句)
1. 安装第三方库
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
2.连接数据库
# 1. 连接数据库
conn = pymysql.connect(
host='127.0.0.1',
port = 3306,
user='root',
passwd='123456',
charset='utf8',
db='unicom'
)
cursor = conn.cursor()
3. 新建数据
cursor.execute('insert into admin(username, password,mobile) values ("zsh","admin","12233454434")')
如果字符串需要可变,不能用python的链接字符串,因为可能被sql注入,正确的方法是:
sql = "insert into admin(username, password,mobile) values (%s,%s,%s)"
cursor.execute(sql, ['张四','123456', '13456565543'])
sql = "insert into admin(username, password,mobile) values (%(name)s,%(pwd)s,%(mobile)s)"
cursor.execute(sql,{"name":"李四", "pwd":"123456", "mobile":"13455677765"})
4. 查询数据
sql = "select * from admin where id > 4"
cursor.execute(sql)
# 查询多条数据: 返回的是列表,没有数据,则返回空列表
data_list = cursor.fetchall()
#print(data_list)
for row_dict in data_list:
print(row_dict)
# 查询一条数据,直接返回的是字典,没有数据,返回None
cursor.fetchone()
5. 删除数据
sql = "delete from admin where id=1"
cursor.execute(sql)
6. 修改数据
sql = "update admin set mobile=%s where id=%s"
cursor.execute(sql, ["13333333333", 4])
7. 提交数据
对于增,删,改数据,仅仅是在内存操作的,仅当执行conn.commit()进行提交,才能数据落地.
# 提交指令
conn.commit()
8. 关闭光标和链接
# 关闭
cursor.close()
conn.close()