Python - where's the error in my MySQL query?

Question

I'm working with PyMySQL and Python.

 sql = "INSERT INTO accounts(date_string, d_day, d_month, d_year, trans_type, descriptor, inputs, outputs, balance, owner) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', AES_ENCRYPT('%s', 'example_key_str')"
cur.execute(sql % (date, d_day, d_month, d_year, ttype, desc, money_in, money_out, bal, owner))

This throws a vague syntax error, and I have no idea how to fix it. The evaluated query is:

INSERT INTO accounts(date_string, d_day, d_month, d_year, trans_type, descriptor, inputs, outputs, balance, owner) VALUES ('12 Feb 2012', '12', 'Feb', '2012', 'CHQ', 'CHQ 54', '7143.78', '0.00', '10853.96', AES_ENCRYPT('[email protected]', 'example_key_str')

The MySQL error is:

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 '' at line 1

Any help would be much appreciated. Thanks in advance.

alecxe · Accepted Answer · 2016-03-27 15:30:48Z

2

There is no closing parenthesis:

INSERT INTO 
    accounts
    (date_string, d_day, d_month, d_year, 
     trans_type, descriptor, inputs, outputs, balance, 
     owner) 
VALUES 
    ('12 Feb 2012', '12', 'Feb', '2012', 
     'CHQ', 'CHQ 54', '7143.78', '0.00', '10853.96',        
     AES_ENCRYPT('[email protected]', 'example_key_str'))
                                                   HERE^

As a side note, don't use string formatting to insert the query parameters into the query - there is a safer and more convenient way to do it - parameterized query:

sql = """
    INSERT INTO 
        accounts
        (date_string, d_day, d_month, d_year, 
        trans_type, descriptor, inputs, outputs, balance, 
        owner) 
    VALUES 
        (%s, %s, %s, %s, 
        %s, %s, %s, %s, %s, 
        AES_ENCRYPT(%s, 'example_key_str'))"""
cur.execute(sql, (date, d_day, d_month, d_year, ttype, desc, money_in, money_out, bal, owner))

answered Mar 27, 2016 at 15:30

alecxe

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

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

1 Comment

Alex Over a year ago

Thanks! We're all sorted now.

Collectives™ on Stack Overflow

Python - where's the error in my MySQL query?

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