pymysql操作数据库入门

1. python操作数据库

pymysql

PyMSQL是一个纯的python的MySQL客户端。

pip install PyMySQL

 

使用步骤

1. 创建连接(修高速公路)
2. 创建游标(运输车)
3. 执行sql(提货单)
4. 获取结果(卸货)
5. 关闭游标(车退掉)
6. 关闭连接(路也断掉)

import pymysql
from pymysql.cursors import DictCursor
# 1.创建连接(修高速公路)
conn = pymysql.connect(
    host='127.0.0.1',
    user='admin',
    password='12345',
    port=3306,
    db='mysql',
    charset='utf8'
)
# 2. 创建游标(运输车)
# cursor = conn.cursor()  # 返回元组
cursor = conn.cursor(DictCursor)  # 返回字典
# 3. 执行sql(提货单)
sql ="select count(1) total, (case when t1.status = 1 then '待整改' when t1.status = 2 then '待复查' when t1.status = 3 then '整改完成' else '未知类型' end) orderStatus from ibs_ai_iot.ai_rectification_main t1 left join ibs_ai_iot.work_order t3 on t1.id = t3.rectification_id where t1.project_id = 103672 and t1.delete_flag = 0 and t3.is_delete = 0 group by t1.status order by orderStatus desc;",
res = cursor.execute(sql)
print(res)
# 4. 获取结果(卸货)
res1 = cursor.fetchone() # 一次卸载一条
res2 = cursor.fetchmany(3) # 一次卸载指定的条数
res3 = cursor.fetchall() # 一次获取所有
# 5. 关闭游标(车退掉)
cursor.close()
# 6. 关闭连接(路也断掉)
conn.close()

[{'total': 30, 'orderStatus': '整改完成'}, {'total': 5, 'orderStatus': '待整改'}]

 

 

更新数据

import pymysql
db_config = {
    'host': '127.0.0.1',
    'user': 'admin',
    'password': '123456',
    'port': 3306,
    'db': 'mysql',
    'charset': 'utf8'
}
conn = pymysql.connect(**db_config)
try:
    with conn.cursor() as cursor: # 上下文管理--自动关闭游标
        sql1 = 'UPDATE table t1  set t1.user_name="勇哥" where t1.username="h1" '
        sql2 = 'UPDATE table t1  set t1.user_name="勇哥2" where t1.username="h2" '
        cursor.execute(sql1)
        cursor.execute(sql2)
        # pymysql默认开启事务
        conn.commit()
except Exception as e:
    # 回滚
    conn.rollback()
finally:
    conn.close()