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 *」

因為表的定義可能不定時被修改。應該在查詢語句中列出所有要返回的字段的名字。