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:
filename
parameter as command line argument.filename
it should create an absolute path to the SQLite database.engine
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.
MyClass
I need Base
to be defined before MyClass
.Base
I need engine
to be defined before Base
.engine
I need path_to_file
to be defined before engine
.path_to_file
outside of main()
I need create_file()
to be defined before path_to_file
.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.
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())
sqlalchemy.exc.UnboundExecutionError: No engine is bound to this Table's MetaData. Pass an engine to the Table via autoload_with=<someengine>, or associate the MetaData with an engine via metadata.bind=<someengine>
Aufwind 2012-04-05 21:02
class MyTable
entirely and do Base.__tablename__ = "my_table"
and so on inside the body of main()
Joel Cornett 2012-04-05 21:24
Base
is just an instance ofdeclarative_base
. Why not just subclassdeclarative_base
- Joel Cornett 2012-04-05 20:42