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
应当一直使用%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
故乡明
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 分享一个免费、快速、无限量使用的满血 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流式编程,极大解放你的生产力!