I have a little problem regarding "arranging of code, which depends on each other" when setting an sqlalchemy mapping to a sqlite database in python.

The goal is to write a script, whcih satisfies the following conditions:

1. Gets a filename parameter as command line argument.
2. Based on the filename it should create an absolute path to the SQLite database.
3. It should connect to the database and create an engine
4. It shall reflect the tables in this databases.
5. It should monkey patch the column id in the table mytable as a primary key column, since the table doesn't habe a primary key and sqlalchemy requires one.

So I came up with this:

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base  

def create_path(file_name):
    # generate absolute path to file_name

path_to_file = create_path("my_file_name.sqlite")
engine = create_engine('sqlite:///{path}'.format(path=path_to_file), echo=False)
Base = declarative_base()
Base.metadata.create_all(engine)

class MyTable(Base):
    __tablename__ = 'my_table'
    __table_args__ = {'autoload': True}

    id = Column(Integer, primary_key=True) # Monkey patching the id column as primary key.

def main(argv):
    # parse file_name here from argv
    Session = sessionmaker(bind=engine)
    session = Session()
    for row in session.query(MyTable).all():
        print row
    return "Stop!"

if __name__ == '__main__':
    sys.exit(main())

But this is a doomed construction and I don't see how I could rearrange my code without breaking the dependencies.

1. To be able to create MyClass I need Base to be defined before MyClass.
2. To be able to create Base I need engine to be defined before Base.
3. To be able to create engine I need path_to_file to be defined before engine.
4. To be able to create path_to_file outside of main() I need create_file() to be defined before path_to_file.
5. And so on...

Hopefully you see where I am stuck...

Any suggestions?

Edit: By the way, the code works, but only with a hardcoded filename in the top of the script.

• python
• reflection
• sqlalchemy
• primary-key
• command-line-arguments

I do not see why you could not use the declarative mapping still complety. I think the key to the proper dependencies is to know what to put in the module and what in the script/function. With python, you can easily define the class inside the run_script function.

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

def create_path(filename):
    import os
    fn = os.path.abspath(os.path.join(os.path.dirname(__file__), 'sqlite_files', filename))
    return fn

def run_script(filename):
    path_to_file = create_path(filename)

    engine = create_engine('sqlite:///{path}'.format(path=path_to_file), echo=True)
    Session = sessionmaker(bind=engine)
    Base = declarative_base(bind=engine)

    # object model
    class MyTable(Base):
        __tablename__ = 'my_table'
        __table_args__ = {'autoload': True}
        id = Column(Integer, primary_key=True) # Monkey patching the id column as primary key

    # script itself
    session = Session()
    for row in session.query(MyTable).all():
        print row
    return "Stop!"

def main(argv):
    assert argv, "must specify a database file name"
    run_script(argv[0])

if __name__ == '__main__':
    import sys
    sys.exit(main(sys.argv))

I solved my problem using the classical mapping approach of SQLAlchemy:

from sqlalchemy import create_engine, Column, Integer, MetaData, Table
from sqlalchemy.orm import sessionmaker, mapper

class MyTable(object):    
    pass    

def main():
    path_to_file = create_path("my_file_name.sqlite")
    engine = create_engine('sqlite:///{path}'.format(path=path_to_file), echo=False)

    metadata = MetaData()
    metadata.bind = engine
    my_table = Table('mytable',
                     metadata,
                     Column('id', Integer, primary_key=True), # Monkey patching the id column as primary key.
                     autoload=True,
                     autoload_with=engine)
    mapper(MyTable, my_table)

    Session = sessionmaker(bind=engine)
    session = Session()

    # Do Stuff!

I don't have the capability to do any testing at my current location. But I suggest moving all of that code to main() as follows. Also, MyTable subclasses declarative_base.

from sqlalchemy import create_engine, Column, Integer 
from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base    

class MyTable(declarative_base):     
    __tablename__ = 'my_table'     
    __table_args__ = {'autoload': True}      

    id = Column(Integer, primary_key=True) # Monkey patching the id column as primary key.  

def create_path(file_name):     
    # generate absolute path to file_name  


def main(argv):     # parse file_name here from argv   
    path_to_file = create_path("my_file_name.sqlite") 
    engine = create_engine('sqlite:///{path}'.format(path=path_to_file), echo=False) 
    Base = MyTable()
    Base.metadata.create_all(engine)  

    Session = sessionmaker(bind=engine)     
    session = Session()     
    for row in session.query(MyTable).all():         
        print row     
    return "Stop!"  

if __name__ == '__main__':     
    sys.exit(main())