2

I have a dictionary of column name / values, to insert into a table. I have a function that generates the INSERT statement. I'm stuck because the function always puts quotes around the values, and some are integers.

e.g. If column 1 is type integer then the statement should be INSERT INTO myTable (col1) VALUES 5; vs INSERT INTO myTable (col1) VALUES '5'; second one causes an error saying column 5 does not exist.

EDIT: I found the problem (I think). the value was in double quotes not single, so it was "5".

In Python, given a table and column name, how can I test if the INSERT statement needs to have '' around the VALUES ?

Celeritas
  • 14,489
  • 36
  • 113
  • 194

4 Answers4

2

This question was tagged with "psycopg2" -- you can prepare the statement using a format string and have psycopg2 infer types for you in many cases.

cur.execute('INSERT INTO myTable (col1, col2) VALUES (%s, %s);', (5, 'abc'))

psycopg2 will deal with it for you, because Python knows that 5 is an integer and 'abc' is a string.

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

Dan Lowe
  • 51,713
  • 20
  • 123
  • 112
1

Although I personally don't like the idea, you can use single quotes around integers when you insert in Postgres.

Perhaps your problem is the lack of parentheses:

INSERT INTO myTable(col1)
    VALUES('5');

Here is a SQL Fiddle illustrating this code.

As you note in the comments, double quotes do not work in Postgres.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It seems the problem was caused by using double quotes instead of single on the value to insert. Is that possible? – Celeritas Aug 10 '15 at 01:14
  • 1
    Yes, you should use single quotes in SQL statements when making a string. Double quotes are reserved for another use (quoting symbol names) and in practice they don't show up much. – Dan Lowe Aug 10 '15 at 01:19
  • @Celeritas . . . Yes, but your question says nothing about double quotes. – Gordon Linoff Aug 10 '15 at 01:20
  • I just realized the function I was using to generate the statement was using double quotes. Sorry. – Celeritas Aug 10 '15 at 01:21
1

You certainly want to use a library function to decide whether or not to quote values you insert. If you are inserting anything input by a user, writing your own quoting function can lead to SQL Injection attacks.

It appears from your tags that you're using psycopg2 - I've found another response that may be able to answer your question, since I'm not familiar with that library. The main gist seems to be that you should use

cursor.execute("query with params %s %s", ("param1", "pa'ram2"))

Which will automatically handle any quoting needed for param1 and param2.

Community
  • 1
  • 1
Ramfjord
  • 872
  • 8
  • 14
0
  1. You can put always the single quote (be careful, if the value contents a quote you must double it: insert into example (value_t) values ('O''Hara');
  2. You can decide checking the value that you want to insert regardles of the type of de destination
  3. You can decide checking the type of the target field

As you can see in http://sqlfiddle.com/#!15/8bfbd/3 theres no mater with inserting integers into a text field or string that represents an integer in a numeric field.

To check the field type you can use the information_schema:

 select data_type from information_schema.columns 
   where table_schema='public'
     and table_name='example'
     and column_name='value_i';

http://sqlfiddle.com/#!15/8bfbd/7

Emilio Platzer
  • 2,327
  • 21
  • 29