CODEWITHSUNDEEP
pythonsqlinsertsqlalchemy
EB.
EB.

Reputation: 3555

In SQLAlchemy, how do I use the insert().from_select() construct properly?

I'm trying to create a simple function that renders a dict containing (dest_column, src_expression) pairs.

In particular, I'd like the interface to be something like:

insert_into(table_obj_a, tbl_object_b, dict(
    col_c = 'col_f',
    col_a = 'col_g',
    col_q = 'col_r + 5',
    col_r = tbl_object_b.col_q,
    col_m = 'rand()',
))

In trying to implement this, I've noticed some peculiar behavior with the .from_select functionality, namely that the order of the columns passed to the from_select seems to be ignored.

This is what I'm experiencing:

bug.py

import sqlalchemy as sa

def _insert_into_dict(into_table, from_table, column_dict):
    into_keys = []
    from_vals = []
    for k, v in sorted((k, v) for (k, v) in column_dict.iteritems()):
        into_keys.append(k)
        from_vals.append(v)
    print into_keys
    print from_vals
    sel = sa.select(from_vals, from_obj=from_table)
    print sel
    print
    return into_table.insert().from_select(
        into_keys, sel
    )

md = sa.MetaData()

cols = ['aaaa', 'bbbb', 'dddd', 'eeee', 'ffff', 'aardvark']

print _insert_into_dict(
    sa.Table('table_a', md, *[sa.Column(x, sa.String) for x in cols]),
    sa.Table('table_b', md),
    dict(
        aaaa='aaaa',
        bbbb='bbbb',
        dddd='dddd',
        eeee='eeee',
        ffff='ffff',
        aardvark='aardvark',
    )
)

Output

u@host:~/src/sa_bug$ python bug.py
['aaaa', 'aardvark', 'bbbb', 'dddd', 'eeee', 'ffff']
['aaaa', 'aardvark', 'bbbb', 'dddd', 'eeee', 'ffff']
SELECT aaaa, aardvark, bbbb, dddd, eeee, ffff
FROM table_b

INSERT INTO table_a (aaaa, bbbb, dddd, eeee, ffff, aardvark) SELECT aaaa, aardvark, bbbb, dddd, eeee, ffff
FROM table_b

Expected

u@host:~/src/sa_bug$ python bug.py
['aaaa', 'aardvark', 'bbbb', 'dddd', 'eeee', 'ffff']
['aaaa', 'aardvark', 'bbbb', 'dddd', 'eeee', 'ffff']
SELECT aaaa, aardvark, bbbb, dddd, eeee, ffff
FROM table_b

INSERT INTO table_a (aaaa, aardvark, bbbb, dddd, eeee, ffff) SELECT aaaa, aardvark, bbbb, dddd, eeee, ffff
FROM table_b

As you can see, the order of the column names in the INSERT INTO statement does not match the order of the expressions in the SELECT statement.

Am I misunderstanding the intended use of .from_select() here?

Upvotes: 0

Views: 1282

Answers (2)

EB.
EB.

Reputation: 3555

As zzzeek mentioned, this was a bug in SQLAlchemy < 0.8.4.

http://www.sqlalchemy.org/trac/ticket/2895

Upvotes: 0

jbub
jbub

Reputation: 2665

The problem is when you define your into_table like this:

sa.Table('table_a', md, *[sa.Column(x, sa.String) for x in cols])

The cols list is not sorted, so it is perfectly right that for this list:

cols = ['aaaa', 'bbbb', 'dddd', 'eeee', 'ffff', 'aardvark']

the INSERT SQL is:

INSERT INTO table_a (aaaa, bbbb, dddd, eeee, ffff, aardvark)

Inside your function you are using sorted() to sort the column_dict, so you must also sort the cols list. Just try adding cols.sort() after your cols definition.

After that the INSERT SQL should be:

INSERT INTO table_a (aaaa, aardvark, bbbb, dddd, eeee, ffff)

Upvotes: 2

Related Questions