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?

ngulam · Accepted Answer · 2014-06-09 21:11:48Z

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?

answered Jun 9, 2014 at 21:11

ngulam

1,1051 gold badge7 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xyzzz Over a year ago

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.

Collectives™ on Stack Overflow

how to declare sql query in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related