How to read dynamically generated table definitions using SQLAlchemy

How to read dynamically generated table definitions using SQLAlchemy

I want to do the title! I looked it up, so I made a note. In conclusion, it's OK if you create a mapping object using the built-in function type. For example, write like this.

engine = create_engine(Database URL, echo=True, encoding='utf-8')
base = declarative_base(engine)

dic = {
    '__tablename__':table name,
    '__table_args__': {'autoload': True}}}
obj = type('MyObject', (base,), dic)

The point is to set autoload = True to create a mapping from an existing table.

sample

Sample code

sql_sample.py

# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

schema = 'sqlite:///test.db'

engine = create_engine(schema, echo=False, encoding='utf-8')
base = declarative_base(engine)
session = sessionmaker(bind=engine)()

management_dic = {
    '__tablename__': 'management',
    '__table_args__': {'autoload': True}}
management_object = type('management_object', (base,), management_dic)
fetch_management = session.query(management_object).all()

for management_row_object in fetch_management:
    folder_table_dic = {
        '__tablename__': management_row_object.folder_table_name,
        '__table_args__': {'autoload': True}}
    folder_object_name = management_row_object.folder_table_name.encode('utf-8')
    folder_object = type(folder_object_name, (base,), folder_table_dic)
    fetch_folder = session.query(folder_object).all()

    print "-" * 32
    print "table_name: %s" % folder_object_name
    print "-" * 32
    for row in fetch_folder:
        print "  fid: %s,  file_name: %s" % (row.fid, row.file_name)
    print

Database settings to use the sample code

A table that manages dynamically created tables (management)

management.sql

CREATE TABLE management(
    tid INTEGER NOT NULL PRIMARY KEY,
    folder_table_name TEXT NOT NULL);
INSERT INTO management(tid, folder_table_name) VALUES (0,'folder_0000');
INSERT INTO management(tid, folder_table_name) VALUES (1,'folder_0001');

Dynamically generated table (folder_0000)

folder_0000.sql

CREATE TABLE folder_0000(
    fid INTEGER NOT NULL PRIMARY KEY,
    file_name TEXT NOT NULL
);
INSERT INTO folder_0000(fid, file_name) VALUES (0,'ham.txt');
INSERT INTO folder_0000(fid, file_name) VALUES (1,'spam.txt');
INSERT INTO folder_0000(fid, file_name) VALUES (2,'egg.txt');

Dynamically generated table (folder_0001)

folder_0001.sql

CREATE TABLE folder_0001(
    fid INTEGER NOT NULL PRIMARY KEY,
    file_name TEXT NOT NULL
);
INSERT INTO folder_0001(fid, file_name) VALUES (0,'foo.txt');
INSERT INTO folder_0001(fid, file_name) VALUES (1,'bar.txt');

Run the sample

sqlite3 test.db < management.sql
sqlite3 test.db < folder_0000.sql
sqlite3 test.db < folder_0001.sql
python sql_sample.py

Sample execution result

--------------------------------
table_name: folder_0000
--------------------------------
  fid: 0,  file_name: ham.txt
  fid: 1,  file_name: spam.txt
  fid: 2,  file_name: egg.txt

--------------------------------
table_name: folder_0001
--------------------------------
  fid: 0,  file_name: foo.txt
  fid: 1,  file_name: bar.txt

reference

Python> Documentation> Python standard library> 2. Built-in functions Dynamically setting _tablename_ for sharding in SQLAlchemy?