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

pandas, postgresql, sqlalchemy

Solution

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)

Problem

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})] ```

Original source