python sending list values to postgres

Question

I can read the content of the available files with this python script:

import psycopg2
from config import config
import os

path = 'my_path'
user_id = '000'
session_id = '12345'

params = config()

conn = None
try:
    params = config()
    conn = psycopg2.connect(**params)
    cur = conn.cursor()

    for root, dirs, files in os.walk(path):
        for file in files:
            if file.endswith('.plt'):
            with open(os.path.join(root, file), 'r') as f:
                content = f.readlines()[6:]
                values = [line.strip().split(',') for line in content]
                print(values)
                query = "INSERT INTO tempo (user_id, session_id, lat, lon, flag, alt, days, gpsdate,gpstime)" \
                "VALUES (%{user_id}, %{session_id},%s,%s,%s,%s,%s,%s,%S)"
                #cur.executemany(query, values)


    cur.close()
    conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
    print(error)
finally:
    if conn is not None:
        conn.close()

Output:

[['40.014395', '116.30643', '0', '492', '39751.986099537', '2008-10-30', '23:39:59'], ['40.014535', '116.306443', '0', '492', '39751.9861226852', '2008-10-30', '23:40:01'], ['40.014629', '116.30649', '0', '492', '39751.9861574074', '2008-10-30', '23:40:04'], ['40.014487', '116.306449', '0', '491', '39751.9861921296', '2008-10-30', '23:40:07'], ['40.014487', '116.306449', '0', '491', '39751.9862268518', '2008-10-30', '23:40:10'], ['40.014495', '116.306446', '0', '491', '39751.98625', '2008-10-30', '23:40:12'], ['40.014502', '116.306467', '0', '491', '39751.9863078704', '2008-10-30', '23:40:17'], ['40.014501', '116.306427', '0', '491', '39751.9863657407', '2008-10-30', '23:40:22'], ['40.014481', '116.306393', '0', '491', '39751.9864236111', '2008-10-30', '23:40:27'], ['40.013746', '116.306367', '0', '82', '39751.9864467593', '2008-10-30', '23:40:29'], ['40.01376', '116.306418', '0', '77', '39751.9865046296', '2008-10-30', '23:40:34'], ['40.013771', '116.306424', '0', '81', '39751.9865625', '2008-10-30', '23:40:39']]

[['39.984094', '116.319236', '0', '492', '39744.2451967593', '2008-10-23', '05:53:05'], ['39.984198', '116.319322', '0', '492', '39744.2452083333', '2008-10-23', '05:53:06'], ['39.984224', '116.319402', '0', '492', '39744.2452662037', '2008-10-23', '05:53:11'], ['39.984211', '116.319389', '0', '492', '39744.2453240741', '2008-10-23', '05:53:16'], ['39.984217', '116.319422', '0', '491', '39744.2453819444', '2008-10-23', '05:53:21'], ['39.98471', '116.319865', '0', '320', '39744.2454050926', '2008-10-23', '05:53:23']]

However, when I attempt sending these values to the postgres table uncommenting #cur.executemany(query, values), I get list out of range error.

$python dump.py
list index out of range

How do I fix this issue?

values is supposed to be a list of 9 items, and it's a list of lists of 7 items each. %{user_id} and %{session_id} won't be substituted automatically. — bereal
– bereal, Commented Jun 10, 2020 at 14:52
Also your last %s, is in capital letter (S) , don't know if is a typo. — Danizavtz
– Danizavtz, Commented Jun 10, 2020 at 14:54
@bereal Ah I can see. How do I then add user_id and session_id variables such that they get inserted in the first column of the table? — arilwan
– arilwan, Commented Jun 10, 2020 at 15:00
@arilwan values = [user_id, session_id, *entry] for each entry in values. — bereal
– bereal, Commented Jun 10, 2020 at 15:12

Adrian Klaver · Accepted Answer · 2020-06-10 16:54:36Z

1

Make your life easier and just use %{field_name}s placeholders. Then you don't have to worry about indexing. FYI this:

%{user_id}, %{session_id},%s,%s,%s,%s,%s,%s,%S)

won't work with this:

cur.executemany(query, values)

for two reasons:

1) This %{user_id} should be %{user_id}s

2) Named placeholders need to get their values from a dictionary e.g.:

{"user_id": 1, "session_id": 2}

edited Jun 10, 2020 at 16:54

answered Jun 10, 2020 at 16:47

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

Comments

arilwan · Accepted Answer · 2020-07-06 18:45:02Z

0

Here's what works for me:

values = [line.strip().split(',') for line in content]
values = [[user_id, session_id] + item for item in values]

query = "INSERT INTO tempo (user_id, session_id, lat, lon, flag, alt, days, gpsdate,gpstime)" \
                "VALUES (%{s, %s,%s,%s,%s,%s,%s,%s,%S)"

...

answered Jul 6, 2020 at 18:45

arilwan

4,1038 gold badges37 silver badges77 bronze badges

Collectives™ on Stack Overflow

python sending list values to postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related