How to remove quotations of string in list of values for sql query in python

Question

I want to insert values into the table open.roads using SQL in python, the table has the following columns:

id integer NOT NULL DEFAULT nextval('open.res_la_roads_id_seq'::regclass),
run_id integer,
step integer,
agent integer,
road_id integer,
CONSTRAINT res_la_roads_pkey PRIMARY KEY (id)

I'm trying to write various rows of values into sql within one query, therefore I created a list with the values to be inserted in the query, following this example:

INSERT INTO open.roads(id, run_id, step, agent, road_id)
    VALUES (DEFAULT, 1000, 2, 2, 5286), (DEFAULT, 1000, 1, 1, 5234);

The list in Python should contain:

list1=(DEFAULT, 1000, 2, 2, 5286), (DEFAULT, 1000, 1, 1, 5234), (.....

I have problems with the value "DEFAULT" as it is a string which should be introduced in sql without the quotations. But I don't manage to remove the quotations, I have tried to save "DEFAULT" in a variable as a string and used str.remove(), str.replace() etc.

The code I'm trying to use:

for road in roads:
    a="DEFAULT", self.run_id, self.modelStepCount, self.unique_id, road
    list1=append.(a)
val=','.join(map(str, list1))
sql = """insert into open.test ("id","run_id","step","agent","road_id")
values {0}""".format(val)
self.model.mycurs.execute(sql)

I get an error because of the quotiations:

psycopg2.DataError: invalid input syntax for integer: "DEFAULT"
LINE 2:('DEFAULT', 582, 0, 2, 13391),('DEFAULT'

How can I remove them? Or is there another way to do this?

`invalid input syntax for integer: "DEFAULT"` sounds like you need to pass an integer and not a string, so try replacing `"Default"` with a default integer — FlyingTeller, Mar 12 '18 at 13:48
i've found the error related question: [Link_Click_Me](https://stackoverflow.com/questions/47945824/error-invalid-input-syntax-for-integer-for-a-non-integer?rq=1) — ARr0w, Mar 12 '18 at 13:48
This is how I was doing it before trying to input multiple rows at once, and it was working: sql = """insert into open.res_la_roads ("id","run_id","step","agent","road_id") values (DEFAULT,{0},{1},{2},{3} )""".format(self.run_id, self.modelStepCount, self.unique_id, road) — Raquel Roses, Mar 12 '18 at 13:50
Please tag your RDBMS: Oracle, Postgres, SQL Server, MySQL... — Parfait, Mar 12 '18 at 15:38

score 0 · Accepted Answer · answered Mar 12 '18 at 13:52

I think this might help you.

sql = "INSERT INTO open.roads(id, run_id, step, agent, road_id) VALUES "
values = ['(DEFAULT, 1000, 2, 2, 5286), ', '(DEFAULT, 1000, 1, 1, 5234)']

for value in values:
    sql += value

print(sql)

After running this code, it will print: INSERT INTO open.roads(id, run_id, step, agent, road_id) VALUES (DEFAULT, 1000, 2, 2, 5286), (DEFAULT, 1000, 1, 1, 5234)

Other solution is to format your string using %.

print("INSERT INTO open.roads(id, run_id, step, agent, road_id) VALUES (%s, %s, %s, %s, %s)" % ("DEFAULT", 1000, 2, 2, 5286))

This will print INSERT INTO open.roads(id, run_id, step, agent, road_id) VALUES (DEFAULT, 1000, 2, 2, 5286)

score 0 · Answer 2 · answered Mar 12 '18 at 15:39

Simply omit the column in append query as it will be supplied with DEFAULT value per your table constraint. Also, use parameterization of values by passing a vars argument in execute (safer/more maintainable than string interpolation): execute(query, vars). And since you have a list of tuples, even consider executemany().

Below assumes database API (i.e., pymysql, pyscopg2) uses the %s as parameter placeholder, otherwise use ?:

# PREPARED STATEMENT
sql = """INSERT INTO open.test ("run_id", "step", "agent", "road_id")
         VALUES (%s, %s, %s, %s);"""

list1 = []
for road in roads:
    a = (self.run_id, self.modelStepCount, self.unique_id, road)
    list1.append(a)

self.model.mycurs.executemany(sql, list1)

How to remove quotations of string in list of values for sql query in python

2 Answers2