python-mysql查询不起作用 [英] python - mysql query not working

问题描述

我正在尝试使用以下Python脚本将数据加载到MySQL表中:

I am trying to load data into a MySQL table with the following Python script:

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'")

我知道在传递查询时这是一个引号问题，但是我很难解决这个问题.有人可以建议我在哪里做错了吗?

I know it is a quotes issue while passing the query, but I am having a hard time trying to figure it out. Can someone please suggest where am I making a mistake?

这是我要执行的查询:

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

推荐答案

似乎您需要为pipeline_name和grid

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

这篇关于python-mysql查询不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！