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
– FlyingTeller, Commented Mar 12, 2018 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
– Raquel Roses, Commented Mar 12, 2018 at 13:50
Please tag your RDBMS: Oracle, Postgres, SQL Server, MySQL... — Parfait
– Parfait, Commented Mar 12, 2018 at 15:38

Laszlowaty · Accepted Answer · 2018-03-12 13:52:28Z

0

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)

answered Mar 12, 2018 at 13:52

Laszlowaty

1,3232 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parfait · Accepted Answer · 2018-03-12 15:39:09Z

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)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related