Possible to use pandas/sqlalchemy to insert arrays into sql database? (postgres)

Question

With the following:

engine = sqlalchemy.create_engine(url)

df = pd.DataFrame({
    "eid": [1,2],
    "f_i": [123, 1231],
    "f_i_arr": [[123], [0]],
    "f_53": ["2013/12/1","2013/12/1",],
    "f_53a": [["2013/12/1"], ["2013/12/1"],],
})

with engine.connect() as con:
    con.execute("""
        DROP TABLE IF EXISTS public.test;
        CREATE TABLE public.test
        (
          eid integer NOT NULL,
          f_i INTEGER NULL,
          f_i_arr INTEGER NULL,
          f_53 DATE NULL,
          f_53a DATE[] NULL,
           PRIMARY KEY(eid)
        );;
        """)
    df.to_sql("test", con, if_exists='append')

If I try to insert only column "f_53" (an date) it succeeds.

If I try to add column "f_53a" (a date[]) it fails with:

                                            ^
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "f_53a" is of type date[] but expression is of type text[]
LINE 1: ..._53, f_53a, f_i, f_i_arr) VALUES (1, '2013/12/1', ARRAY['201...
                                                             ^
HINT:  You will need to rewrite or cast the expression.
 [SQL: 'INSERT INTO test (eid, f_53, f_53a, f_i, f_i_arr) VALUES (%(eid)s, %(f_53)s, %(f_53a)s, %(f_i)s, %(f_i_arr)s)'] [parameters: ({'f_53': '2013/12/1', 'f_53a': ['2013/12/1', '2013/12/1'], 'f_i_arr': [123], 'eid': 1, 'f_i': 123}, {'f_53': '2013/12/1', 'f_53a': ['2013/12/1', '2013/12/1'], 'f_i_arr': [0], 'eid': 2, 'f_i': 1231})]

The first line of the error message is indicating that the expression - ["2008-03-08",] - is of type text[]. It might be that Pandas hasn't automatically converted it like it did with f_53. Have you tried manually casting it with pd.to_datetime()? pandas.pydata.org/pandas-docs/stable/generated/… — bouteillebleu
– bouteillebleu, Commented May 5, 2017 at 23:32
If I change all the date types to int and string values to integers, the error is the same. In this case pandas has a column of dtype=object hold a list of ints. Seems its not specific to dates, but to arrays. — user48956
– user48956, Commented May 5, 2017 at 23:37
Ack -- schema bug in edit. You were right pd.to_datetime-ing dates fixes this and date and date[] are inserting correctlt — user48956
– user48956, Commented May 6, 2017 at 0:23
Awesome - would you be able to write up how you fixed it as an answer, so that other people can see what solved it? :) — bouteillebleu
– bouteillebleu, Commented May 6, 2017 at 7:27

Kzryzstof · Accepted Answer · 2018-10-06 12:13:18Z

10

I have mentioned the dtypes explicitly and it worked for me for postgres.

//sample code

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.dialects import postgresql
 df.to_sql('mytable',pgConn, if_exists='append', index=False,  dtype={'datetime': sqlalchemy.TIMESTAMP(), 'cur_c':postgresql.ARRAY(sqlalchemy.types.REAL),
        'volt_c':postgresql.ARRAY(sqlalchemy.types.REAL)
        })

edited Oct 6, 2018 at 12:13

Kzryzstof

8,44213 gold badges71 silver badges135 bronze badges

answered Oct 6, 2018 at 11:18

naidu

1011 silver badge3 bronze badges

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

Comments

user48956 · Accepted Answer · 2017-05-11 20:34:36Z

Yes -- is possible to insert [] and [][] types from a dataframe into postgres form a dataframe.

Unlike flat DATE types, which are may be correctly parsed by sql, DATE[] and DATE[][] need to be converted to datetime objects first. Like so.

with engine.connect() as con:
    con.execute("""
        DROP TABLE IF EXISTS public.test;
        CREATE TABLE public.test
        (
          eid integer NOT NULL,
          f_i INTEGER NULL,
          f_ia INTEGER[] NULL,
          f_iaa INTEGER[][] NULL,
          f_d DATE NULL,
          f_da DATE[] NULL,
          f_daa DATE[][] NULL,
          PRIMARY KEY(eid)
        );
        """)


    d = pd.to_datetime("2013/12/1")
    i = 99
    df = pd.DataFrame({
        "eid": [1,2],

        "f_i": [i,i],
        "f_ia": [None, [i,i]],
        "f_iaa": [[[i,i],[i,i]], None],

        "f_d": [d,d],
        "f_da": [[d,d],None],
        "f_daa": [[[d,d],[d,d]],None],
    })
    df.to_sql("test", con, if_exists='append', index=None)

Collectives™ on Stack Overflow

Possible to use pandas/sqlalchemy to insert arrays into sql database? (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