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

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语句时的参数问题