how to declare sql query in python

Question

I am running a mysql insert query from my python script and I am running into the following error -

jobsummaryfile = '/home/abcd/projects/starling/daily_job_summary/'+ starling_date +'.tsv'
 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)")
conn.close()

The error i am getting is -

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/home/abcd/projects/starling/daily_job_summary/2014_02_17.tsv I' at line 1")

It is picking up the I at the end and that is causing it to fail. How do i get rid of this?

Answer 1

The error message shows you, that the parsing not ending with the blank (showing you the "I" from "INTO" next).

You have to (single-)quote your filename, so compose the SQL string with double quotes to embed the ' around filename

obsummaryfile = "'/home/abcd/projects/starling/daily_job_summary/" + starling_date + ".tsv'"

Does this help?

• xyzzz: i fixed this issue on my side...I need help with one more thing...I want to modify the above query to insert few more variables from the script into the same table.