How to convert String to integer and load into Postgres using Python?

Question

I have a record which is stored in data3 which is a string

data3 = ['{"Project_id": "300","Your Design Score ": "N/A"}']

while inserting into my table

cur.executemany("""INSERT INTO TABLE3_ES(Project_id,Your_Design_Score) VALUES (%(Project_id)s, %(Your Design Score )s)""", data3)

It is throwing error:

TypeError: string indices must be integers, not str

How should I convert and load the data into my table?

@NM, Am just trying to insert the data into my local machine postgres — venkat, Nov 27 '17 at 08:39
Yes, but what dbapi are you using? Psycopg2? Also, most of the execute commands use SQL objects and Identifier and Literal objects to avoid SQL injection. https://www.python.org/dev/peps/pep-0249/ — N M, Nov 27 '17 at 08:40
For example, cur.execute( sql.SQL("insert into {} values (%s, %s)") .format(sql.Identifier('my_table')), [10, 20]) for Identifiers. Similarly, you can use sql.Literal for literals. Also, note here sql refers to psycopg2.sql — N M, Nov 27 '17 at 09:01

score 1 · Accepted Answer · answered Nov 27 '17 at 08:55

Oups, your sql executemany expects an iterable of dictionaries, and you give it an iterable of strings. The correct way would be to fix the way you get data3, but if you cannot do that, you must eval the strings to build a dicts. As eval is evil you should try to use ast.literal_eval if your actual data can be processed by it.

In the example use case, you should be able to insert the data with a comprehension list converting all string elements to dictionaries:

cur.executemany("""INSERT INTO TABLE3_ES(Project_id,Your_Design_Score) 
    VALUES (%(Project_id)s, %(Your Design Score )s)""",
    [ ast.literal_eval(s) for s in data3])

Alternatively, if the data is supposed to be legal JSON, `json.loads` would also work (and is generally faster to boot). — ShadowRanger, Dec 31 '17 at 16:37

How to convert String to integer and load into Postgres using Python?

1 Answers1