SQL syntax error in absurdly simple sql?

Question

Calling:

INSERT IGNORE INTO standard_group (group_name, subject, grade, country, state) VALUES (%(group_name), %(subject), %(grade), %(country), %(state));

With

('Arizona Social Studies Standards', 'Social Studies', '1', 'United States', 'AZ')

eg:

    print group_columns
    print group_sql
    print group
    cur.execute(group_sql, dict(zip(group_columns, group)))

But I always get:

['group_name', 'subject', 'standard_category', 'standard_sub_category', 'standard_name', 'standard_description', 'grade', 'country', 'state', 'is_common_core', 'is_state_standard']
INSERT IGNORE INTO standard_group (group_name, subject, grade, country, state) VALUES (%(group_name), %(subject), %(grade), %(country), %(state));
('Arizona Social Studies Standards', 'Social Studies', '1', 'United States', 'AZ')
Traceback (most recent call last):
  File "standards-import/simport.py", line 167, in <module>
    cur.execute(group_sql, dict(zip(group_columns, group)))
  File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 507, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 722, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 640, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.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 '%(group_name), %(subject), %(grade), %(country), %(state))' at line 1

I've tried with named params, ? and %s, and get

Not all arguments used
Wrong number of arguments for named parameter
SQL syntax

What's going on?? What am I missing???

When you use `%(group_name)` shouldn't the substitutions be in a dictionary rather than a list? — Barmar, Aug 19 '15 at 19:45
`dict(zip(group_columns, group))` turns them into a dict (with the right key names). Something else is also still the issue, because I can't do list + `%s` or `?` format, either. — Jordan Warbelow-Feldstein, Aug 19 '15 at 20:13
Can you paste your python script so we can look at indents and such? — FirebladeDan, Aug 19 '15 at 20:19

twil · Accepted Answer · 2015-08-19T21:41:10.797

2

It looks like a small typo. You forgot to add s to the params

INSERT IGNORE INTO standard_group (group_name, subject, grade, country, state) VALUES (%(group_name)s, %(subject)s, %(grade)s, %(country)s, %(state)s);

The relevant code is here:

edited Aug 19 '15 at 21:41

answered Aug 19 '15 at 21:10

twil

6,032
1
30
28

This inserts, sort of, but I get rows that look like: http://cl.ly/image/0l1l2d3V181o -- Where is this documented? I'm getting confused between docs for Mysql, Mysql-python, and mysqldb which all do this differently. – Jordan Warbelow-Feldstein Aug 19 '15 at 21:13
1

Your `def row_to_group_values(row)` returns string, not list. When you `zip` it with `group_columns` you get what you see. – twil Aug 19 '15 at 21:49
I'm a big dummy. Thanks guys. – Jordan Warbelow-Feldstein Aug 20 '15 at 02:47

SQL syntax error in absurdly simple sql?

1 Answers1