CODEWITHSUNDEEP
pythonsqlalchemy
Mark Warburton
Mark Warburton

Reputation: 517

Use variable column headings in SQLAlchemy

I'm trying to move to SQLAlchemy from psycopg2. At a point in my code, I've got a list of column names in columnList to be created as text types in a table name stored in tableName. Using psycopg2, this query works as follows:

ct = 'CREATE TABLE ' + tableName + ' ( id integer PRIMARY KEY, '
for _col in columnList:
    ct += '"' + _col + '" text, '
ct = ct[:-2] + ');'
cur.execute(ct)
conn.commit()

How can I achieve the same thing in SQLAlchemy, bearing in mind that I cannot hard code the column names or table names as they are drawn from a dynamic list?

Also, once this table is created, how should it and the columns be referenced later in the code when querying or inserting values?

Thanks.

Upvotes: 0

Views: 311

Answers (1)

SuperShoot
SuperShoot

Reputation: 10861

Using this sample data:

data = {'table1name': ['textcol1', 'textcol2'],
        'table2name': ['textcol3', 'textcol4']}

Here's one way to do it:

from sqlalchemy import Table, Column, Text, Integer, MetaData

metadata = MetaData()

for tblname, colnames in data.items():
    Table(
        tblname, metadata,
        Column('id', Integer, primary_key=True),
        *[Column(name, Text) for name in colnames]
    )

The tables are automatically mapped to their name in the MetaData.tables dictionary...

print(type(metadata.tables['table1name']))  # <class 'sqlalchemy.sql.schema.Table'>

... so you can always access them through there to perform queries, as long as you have the name of the table.

This will render the create table statements:

from sqlalchemy.schema import CreateTable
for table in metadata.tables.values():
    print(CreateTable(table))

Which prints:

CREATE TABLE table1name (
        id INTEGER NOT NULL,
        textcol1 TEXT,
        textcol2 TEXT,
        PRIMARY KEY (id) )



CREATE TABLE table2name (
        id INTEGER NOT NULL,
        textcol3 TEXT,
        textcol4 TEXT,
        PRIMARY KEY (id) )

Upvotes: 1

Related Questions