Python/Database Programming

MySQL

import pymysql
conn = pymysql.connect(host="10.1.2.3",user="root",password="root_password",db="db_name")
cursor = conn.cursor()

sql = "SELECT col1, col2 from ... "

try:
        conn.ping(reconnect=True)
        cursor.execute(sql) 
        conn.commit()
except Exception as e:
        info="fail to execute: " + sql
        WriteLog('warning',repr(e))
        WriteLog('warning', info)

一般原理

参数引用

# 不提倡这么写！因为它不能正确处理特殊字符如何被替换，如单引号。
result = db.execute("SELECT name FROM employees WHERE location = '" + location + "'")

甚至可能SQL注入攻击.

应该使用execute的自动化参数替代的方法，如：

result = db.execute("SELECT name FROM employees WHERE location = ?", [location])

更复杂的例子，接口库应该提供quoting函数供显式调用。如pymysql.converters的escape_string：

criteria = [("company", company)] # list of tuples (fieldname, value)
if department != None :
    criteria.append(("department", department))
#end if
# ... append other optional criteria as appropriate ...

result = db.execute \
  (
        "SELECT name FROM employees WHERE "
    +
        " and ".join
          (
            "%s = %s" % (criterion[0], MySQLdb.escape_string(criterion[1]))
            for criterion in criteria
          )
  )

这将产生查询字符串如“select name from employees where company = 'some company'” 或 “select name from employees where company = 'some company' and department = 'some department'”。

使用迭代器

Python 3的SQLite接口库，cursor.execute方法返回结果集的迭代器。例如：

def db_iter(db, cmd, mapfn = lambda x : x) :
    "executes cmd on a new cursor from connection db and yields the results in turn."
    cu = db.cursor()
    result = cu.execute(cmd)
    while True :
        yield mapfn(next(result))
    #end while
#end db_iter

该函数使用例子：

for \
    artist, publisher \
in \
    db_iter \
      (
        db = db,
        cmd =
                "SELECT artist, publisher FROM artists WHERE location = %s"
            %
                 apsw.format_sql_value(location)
      ) \
:
    print(artist, publisher)
#end for

和

for \
    location \
in \
    db_iter \
      (
        db = db,
        cmd = "SELECT DISTINCT location FROM artists",
        mapfn = lambda x : x[0]
      ) \
:
    print(location)
#end for

脚本中不要使用“SELECT *”

因为表的定义可能不定时被修改。应该在查询语句中列出所有要返回的字段的名字。