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

2023-12-21 06:51| 来源: 网络整理| 查看: 265

文章目录 pycharm操作MySQLSQL语句编写（占位符的应用）

pycharm操作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 SQL语句编写（占位符的应用）

执行的关键还是在SQL语句的编写。这类有几种方式：

完整的SQL语句，直接调用 sql_select = "select * from tablename" cursor.execute(sql_select) 利用占位符传递参数。这里要注意，无论整数、字符串，占位符都为 %s，且不需加引号，在sql语句中借助占位符，组成完整SQL tabel = 'persons' sql = "select * from %s" % table cursor.execute(sql) 参数替代 tabel = 'persons' sql = "select * from %s" cursor.execute(sql， table) # 参数多于1个时，execute()传入参数应为list或者tuple类型 sql_update = "update persons set birthday=%s where ID=%s" cursor.execute(sql_update， ('2001/7/5', 3)) 字典类型传递变量，这里要保证占位符的keys要包含在传递的字典keys中 # 这里的占位符%s修改为%(字典keyname)s sql_insert = "insert into persons(ID, LastName, FirstName) " \ "values (%(ID)s, %(LastName)s, %(FirstName)s)" message = { "ID": 7, "LastName": "Jone", "FirstName": "Bob", } cursor.execute(sql_insert， message)

参考来源： python3操作MySQL数据库 Python连接MySQL数据库执行sql语句时的参数问题

【本文地址】

公司简介

联系我们