StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTrouble inserting data into Postgresql db via Python/psycopg2
text
Body
copied!<p>Using a method (see bottom), I build an insert command to insert an item (stored as a dictionary) into my postgresql database. Although, when I pass that command to cur.execute I get a syntax error. I'm really at a loss as to why this error is occurring.</p> <pre><code>>>> print insert_string """INSERT INTO db_test (album, dj, datetime_scraped, artist, playdatetime, label, showblock, playid, showtitle, time, station, source_url, showgenre, songtitle, source_title) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);""", (item['album'], item['dj'], item['datetime_scraped'], item['artist'], item['playdatetime'], item['label'], item['showblock'], item['playid'], item['showtitle'], item['time'], item['station'], item['source_url'], item['showgenre'], item['songtitle'], item['source_title']) >>> cur.execute(insert_string) psycopg2.ProgrammingError: syntax error at or near """"INSERT INTO db_test (album, dj, datetime_scraped, artist, playdatetime, label, showblock, playid, showtitle, time, station, source_url, showgenre, songtitle, source_title) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"""" LINE 1: """INSERT INTO db_test (album, dj, datetime_scraped, artis... </code></pre> <p>Here's a more "eyeball-friendly" version of that insert command:</p> <pre><code>"""INSERT INTO db_test (album, dj, datetime_scraped, artist, playdatetime, label, showblock, playid, showtitle, time, station, source_url, showgenre, songtitle, source_title) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);""", (item['album'], item['dj'], item['datetime_scraped'], item['artist'], item['playdatetime'], item['label'], item['showblock'], item['playid'], item['showtitle'], item['time'], item['station'], item['source_url'], item['showgenre'], item['songtitle'], item['source_title']) </code></pre> <p>The method used to build the insert:</p> <pre><code>def build_insert(self, table_name, item): if len(item) == 0: log.msg("Build_insert failed. Delivered item was empty.", level=log.ERROR) return '' #itemKeys = item.keys() itemValues = [] for key in item.keys(): # Iterate through each key, surrounded by item[' '], seperated by comma itemValues.append('item[\'{theKey}\']'.format(theKey=key)) sqlCommand = "\"\"\"INSERT INTO {table} ({keys}) VALUES ({value_symbols});\"\"\", ({values})".format( table = table_name, #table to insert into, provided as method's argument keys = ", ".join(item.keys()), #iterate through keys, seperated by comma value_symbols = ", ".join("%s" for key in itemValues), #create a %s for each key values = ", ".join(itemValues)) return sqlCommand </code></pre> <p>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~</p> <p>EDIT:</p> <p>I used Gringo Suaves suggestions, with the exception of a small change to the build_insert method (create as many %s symbols as needed based on # of keys present.</p> <pre><code>def build_insert(self, table_name, item): if len(item) == 0: log.msg("Build_insert failed. Delivered item was empty.", level=log.ERROR) return '' keys = item.keys() values = [ item[k] for k in keys] # make a list of each key sqlCommand = 'INSERT INTO {table} ({keys}) VALUES ({value_symbols});'.format( table = table_name, #table to insert into, provided as method's argument keys = ", ".join(keys), #iterate through keys, seperated by comma value_symbols = ", ".join("%s" for value in values) #create a %s for each key ) return (sqlCommand, values) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload