python 借助 pymysql 操作MySQL及占位符问题

在python3中,主要借助pymysql进行MySQL操作,简单记录下基本的操作步骤:

操作流程一般分为3步:
1. 建立数据库连接;
2. 执行操作(查询、插入、更新、删除等)
3. 关闭连接

这里直接贴代码了,用函数的形式进行表述了:

复制代码
import pymysql


# 数据库连接
def connect():
    conn = pymysql.connect(host='localhost',
                           port=3306,
                           user='root',
                           password='root',
                           database='njust',
                           charset='utf8')

    # 获取操作游标
    cursor = conn.cursor()
    return {"conn": conn, "cursor": cursor}
复制代码

在执行操作时,借助游标方法: cursor.execute() 执行SQL操作。

复制代码
# 1、查询操作并打印结果
def select_sql(table):
    connection = connect()
    conn, cursor = connection['conn'], connection['cursor']
    sql = "select * from %s" % table
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results)
    except Exception as e:
        raise e
    finally:
        cursor.close()
        conn.close()


# 插入操作
def insert_sql(persons_values):
    connection = connect()
    conn, cursor = connection['conn'], connection['cursor']

    keys = ", ".join(persons_values.keys())
    qmark = ", ".join(["%s"] * len(persons_values))
    sql_insert = "insert into persons(%s) values (%s)" % (keys, qmark)
    print(sql_insert)
    try:
        cursor.execute(sql_insert, list(persons_values.values()))
        conn.commit()
        print("插入成功")
    except Exception as e:
        print(e)
        conn.rollback()
        print("插入失败")
    finally:
        cursor.close()
        conn.close()


# 利用字典进行插入
def insert_sql2(message):
    connection = connect()
    conn, cursor = connection['conn'], connection['cursor']

    sql_insert = "insert into persons(ID, LastName, FirstName) " \
                 "values (%(ID)s, %(LastName)s, %(FirstName)s)"
    try:
        cursor.execute(sql_insert, message)
        conn.commit()
        print("插入成功")
    except Exception as e:
        print(e)
        conn.rollback()
        print("插入失败")
    finally:
        cursor.close()
        conn.close()


# 更新数据库
def update_sql():
    connection = connect()
    conn, cursor = connection['conn'], connection['cursor']

    sql_update = "update persons set birthday=%s where ID=%s"
    try:
        cursor.execute(sql_update, ('2001/7/5', 3))
        conn.commit()
        print('更新成功')
    except Exception as e:
        print('更新失败', e)
        conn.rollback()
    finally:
        cursor.close()
        conn.close()
    pass


# 删除操作
def delete_sql(lastname):
    connection = connect()
    conn, cursor = connection['conn'], connection['cursor']

    sql_delete = "delete from persons where LastName=%s"
    try:
        cursor.execute(sql_delete, lastname)
        conn.commit()
        print('删除成功')
    except Exception as e:
        print('删除失败', e)
        conn.rollback()
    finally:
        cursor.close()
        conn.close()
    pass
View Code
复制代码

 应当一直使用%s用于字符串格式化

python中无论整数,字符串占位符都为 %s,且不需加单引号

sql="insert into myauth_perm(permname, permurl, permdemo, parentid) values(%s, %s, %s, %s)"

cur.execute(sql,[html_permname, html_permurl, html_permdemo,html_parentid])

https://www.runoob.com/python3/python3-mysql.html

posted @   Bonnie_ξ  阅读(687)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 按钮权限的设计及实现
· 25岁的心里话
历史上的今天:
2021-01-18 API接口的安全设计验证:ticket,签名,时间戳
2021-01-18 使用Python验证常见的50个正则表达式
2021-01-18 IntelliJ IDEA 超全优化设置,效率杠杠的!
2021-01-18 Java8 Stream流式编程,极大解放你的生产力!
点击右上角即可分享
微信分享提示