python - mysql 查询无效

Question

我正在尝试用以下的Python脚本把数据加载到MySQL表中：

conn = connect_db()
cursor = conn.cursor()
cursor.execute(
    "LOAD DATA LOCAL INFILE " + jobsummaryfile + " INTO TABLE daily_job_summary " +
    "FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (@col1, @col2, @col3, @col4) " +
    "set jobname=@col1, queue=@col2, maphours=@col3, reducehours=@col4, date=" + date + 
    ", pipeline=" + pipeline_name + ", grid=" + grid
)

但是我遇到了以下错误：

_mysql_exceptions.OperationalError: (1054, "Unknown column 'galaxy' in 'field list'")

我知道这是在传递查询时引号的问题，但我很难找出具体哪里出错了。有没有人能告诉我我错在哪里？

这是我想执行的查询：

LOAD DATA LOCAL INFILE 'file.tsv' 
    INTO TABLE daily_job_summary 
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\n' 
    (@col1, @col2, @col3, @col4) 
    set jobname=@col1, queue=@col2, maphours=@col3, reducehours=@col4, 
        date=2014-01-05, pipeline='abcd', grid='AB'

这是我的表结构：

| id          | int(11)      | NO   | PRI | NULL    | auto_increment |  
| date        | date         | YES  |     | NULL    |                |  
| pipeline    | varchar(12)  | YES  |     | NULL    |                |  
| grid        | varchar(2)   | YES  |     | NULL    |                |  
| jobname     | varchar(255) | YES  |     | NULL    |                |  
| maphours    | int(11)      | YES  |     | NULL    |                |  
| reducehours | int(11)      | YES  |     | NULL    |                |  
| queue       | varchar(60)  | YES  |     | NULL    |                |  

Answer 1

如果你使用参数而不是把字符串拼接在一起，MySQLdb会帮你处理引号的问题。最简单的方法是：

cursor.execute("SELECT * FROM tbl WHERE col1 = %s;", ('foo',))

%s 是一个占位符，用来表示一个参数，参数会按照顺序插入；你必须为查询字符串中的每个占位符提供参数。或者，你也可以使用字典来指定命名参数：

cursor.execute("SELECT * FROM tbl WHERE col1 = %(col1)s;", {'col1': 'foo'})

不过，并不是所有的部分都能这样替换（具体可以查看MySQLdb用户指南中的paramstyle属性）：

参数占位符只能用来插入列的值。它们不能用于SQL的其他部分，比如表名、语句等。

所以在你的查询中，你可以把date、pipeline_name和grid作为参数传入，但不能把jobsummaryfile传入，因为它是一个文件路径，而不是列的值。你还可以使用隐式字符串拼接，让代码更易读，并通过去掉不必要的用户变量来简化你的语句：

query = ("LOAD DATA LOCAL INFILE " + jobsummaryfile + " INTO TABLE daily_job_summary "
         "FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' "
         "(jobname, queue, maphours, reducehours) "
         "set date=%s, pipeline=%s, grid=%s;")
curs.execute(query, (date, pipeline_name, grid))

你需要(@col1, @col2, @col3, @col4)的唯一原因是如果你需要在把它们插入到表中之前处理这四列的值。例如，如果你想确保你的作业名称总是大写，你可以这样做：

LOAD DATA LOCAL INFILE 'file.tsv'
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\n'
    (@col1, queue, maphours, reducehours)
    SET jobname=UPPER(@col1), date='2014-01-05', pipeline='abcd', grid='AB';

但是因为你只是把数据原样插入，所以那些用户变量只会让查询变得更长、更难读；最好把它们去掉。

---

就个人而言，我喜欢在SQL语句中使用Python的多行字符串。这让我可以做到两件事：首先，我可以快速复制/粘贴整个语句到Workbench进行测试；其次，我可以在开头加上注释，这样在查看服务器管理选项卡时，可以帮助我识别当前正在运行的查询。缺点是，这样会稍微冗长一些，尤其是在插入表名时。

例如：

load_from_file = """--load from {file}
LOAD DATA LOCAL INFILE {file}
    INTO TABLE daily_job_summary
    FIELDS TERMINATED BY '\t' 
    LINES TERMINATED BY '\n'
    (jobname, queue, maphours, reducehours)
    SET date=%s, pipeline=%s, grid=%s;
"""
curs.execute(query=load_from_file.format(file=jobsummaryfile), 
             args=(date, pipeline_name, grid))

Answer 2

如果你打印出构建好的 MySQL 语句，你会发现有些字符串值的赋值没有加上必要的单引号。

你需要在每个值周围加上单引号，并且如果字符串中有单引号的话，还要把它们加倍（也就是说，galaxy 应该变成 'galaxy'，而 Joe's Galaxy 应该变成 'Joe''s Galaxy'）。

你可以使用一个类似这样的函数：

 def qstr(in_string, quote_char="'"):

     return quote_char + in_string.replace(quote_char, quote_char*2) + quote_char

然后把你的代码修改成这样："pipeline = " + qstr(pipeline_name)。

Answer 3

看起来你需要给 pipeline_name 和 grid 加上引号。

 cursor.execute(" ... pipeline='"+pipeline_name+"', grid='"+grid+"'")