0

So i'm trying to insert some data inside a for loop in sqlite database with python. I ve tried the following and none work:

conn.execute("INSERT INTO {table} ({colname}) VALUES ({val})"\
    .format(table="Financial_Data",colname=sheet_results.cell(row=1,column=celula.col_idx).value,val=celula.value))

error:

sqlite3.OperationalError: near "name": syntax error

I've also tried this version:

conn.execute("INSERT INTO "Financial_Data" (?) VALUES (?)",(sheet_results.cell(row=1,column=celula.col_idx).value,celula.value)

The values are from openpyxl and they are tested. The error i recieve is syntax error.

this works(god knows why):

conn.execute('INSERT INTO Financial_Data ("Company name") VALUES (?)',(rand[1].value,))

What am i doing wrong? Much appreciated!

Mike
  • 466
  • 9
  • 24
  • Table and column name are not supposed to be passed as parameters in prepared statements, it is only meant for values. Use `str.format()` for specifying table and column name. – Ashwini Chaudhary May 02 '17 at 11:09
  • U should have written this as an answer, http://stackoverflow.com/questions/8841488/pysqlite-placeholder-substitution-for-column-or-table-names i found this. True stroy. – Mike May 02 '17 at 11:13
  • @AshwiniChaudhary could u give me a short example of using string.format()? – Mike May 02 '17 at 11:13
  • Your first example is using it already, just don't use it for values. – Ashwini Chaudhary May 02 '17 at 11:15
  • This doesnt work: conn.execute("INSERT INTO Financial_Data ({colname}) VALUES (?)".format(colname=hed), valoare) – Mike May 02 '17 at 11:46
  • What's the error? I hope you are passing in a tuple: `(valoare, )`. – Ashwini Chaudhary May 02 '17 at 11:56
  • The exact code is this: conn.execute("INSERT INTO Financial_Data ({colname}) VALUES (?)".format(colname=hed), (valoare,)) And the error is this: sqlite3.OperationalError: near "name": syntax error – Mike May 02 '17 at 11:58
  • Try with `{colname!r}`? (This should take care of the space) – Ashwini Chaudhary May 02 '17 at 12:01
  • Praise god, it worked, why? i even tried with {} and it didnt work. – Mike May 02 '17 at 12:05
  • `!r` returns the `__repr__` representation of a string, by default it's `__str__` i.e `!s`. Without `!r` the query would result in `INSERT INTO Financial_Data (Company name) VALUES (?)` and that's a syntax error, with `!r` you will get `INSERT INTO Financial_Data ('Company name') VALUES (?)` – Ashwini Chaudhary May 02 '17 at 12:07
  • I had to modify with a !r where i first inserted the columns, otherwise they wouldn't match when i verified the same string against itself with !r. Anyway, i wouldnt have figured out on my own, thank you very much @AshwiniChaudhary – Mike May 02 '17 at 12:13
  • @AshwiniChaudhary Unfortunately "abc" != repr("abc"). So if i have a column named "abc" and i ll try to insert using {colname!r} it will give me column not found error, if i ll try to ADD COLUMN {col!r} it wont write it, nor will it give me an error, so apparently i have no solution so far. – Mike May 02 '17 at 12:49
  • 1
    Use explicit quotes in that case: `'INSERT INTO Financial_Data ("{colname}") VALUES (?)'`. – Ashwini Chaudhary May 02 '17 at 12:58
  • Gr8, solved. Special thanks :D – Mike May 02 '17 at 13:07

0 Answers0