Errors in SQL Syntax

Question

I am trying to run MySQL query through a python script and keep getting an error in my SQL syntax, from I can see the query is set up correctly. Could someone please give me a second set of eyes on this?

    conn = mysql.connector.connect(**config)
    connect = conn.cursor()
    query = u'INSERT INTO page_load_times (self, object_id, page_header, elapsed_time, date_run) ' \
            'VALUES ({}, {}, {}, {}, {})'.format(self, self.object_id, self.page_header, t.interval, timestamp)
    connect.execute(query)
    conn.commit()
    conn.close()

The error I am getting is below:

ProgrammingError: 1064 (42000): 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 '13:56:17.491000)' at line 1

Guessing oen of those is a string and it's nto quoted in your query... — Cheruvian
– Cheruvian, Commented Jul 24, 2014 at 20:03

alecxe · Accepted Answer · 2014-07-24 20:05:49Z

5

Don't pass query parameters via string formatting.

Let the mysql client do the job by passing parameters into the query in the second argument to execute(). Aside from not having problems with quotes and data type conversions, you would avoid sql injection risks:

query = """INSERT INTO 
               page_load_times 
               (self, object_id, page_header, elapsed_time, date_run)
           VALUES 
               (%(self)s, %(object_id)s, %(page_header)s, %(interval)s, %(timestamp)s)"""
params = {'self': self, 
          'object_id': self.object_id, 
          'page_header': self.page_header, 
          'interval': t.interval, 
          'timestamp': timestamp}

connect.execute(query, params)

answered Jul 24, 2014 at 20:05

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

1 Comment

DarthOpto Over a year ago

Thank you, that looks like it may work, as soon as I figure out a problem with someone else's code.

Collectives™ on Stack Overflow

Errors in SQL Syntax

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