How to convert select_from object into a new table in sqlalchemy

Question

I have a database that contains two tables in the data, cdr and mtr. I want a join of the two based on columns ego_id and alter_id, and I want to output this into another table in the same database, complete with the column names, without the use of pandas.

Here's my current code:

mtr_table = Table('mtr', MetaData(), autoload=True, autoload_with=engine)
print(mtr_table.columns.keys())

cdr_table = Table('cdr', MetaData(), autoload=True, autoload_with=engine)
print(cdr_table.columns.keys())

query = db.select([cdr_table])
query = query.select_from(mtr_table.join(cdr_table,
        ((mtr_table.columns.ego_id == cdr_table.columns.ego_id) &
            (mtr_table.columns.alter_id == cdr_table.columns.alter_id))),
    )

results = connection.execute(query).fetchmany()

Currently, for my test code, what I do is to convert the results as a pandas dataframe and then put it back in the original SQL database:

df = pd.DataFrame(results, columns=results[0].keys())
df.to_sql(...)

but I have two problems:

loading everything into a pandas dataframe would require too much memory when I start working with the full database
the columns names are (apparently) not included in results and would need to be accessed by results[0].keys()

I've checked this other stackoverflow question but it uses the ORM framework of sqlalchemy, which I unfortunately don't understand. If there's a simpler way to do this (like pandas' to_sql), I think this would be easier.

What's the easiest way to go about this?

irene · Accepted Answer · 2019-10-27 15:26:09Z

0

So I found out how to do this via CREATE TABLE AS:

query = """
    CREATE TABLE mtr_cdr AS
    SELECT
        mtr.idx,cdr.*
    FROM mtr INNER JOIN cdr 
        ON (mtr.ego_id = cdr.ego_id AND mtr.alter_id = cdr.alter_id)""".format(new_table)


with engine.connect() as conn:
    conn.execute(query)

The query string seems to be highly sensitive to parentheses though. If I put a parentheses enclosing the whole SELECT...FROM... statement, it doesn't work.

answered Oct 27, 2019 at 15:26

irene

2,2531 gold badge24 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

How to convert select_from object into a new table in sqlalchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related