How to recognise a sqlite database created with sqlalchemy using pydal?

Question

I create a very simple database with sqlalchemy as follows:

from sqlalchemy import Column, Integer, String                                                                                                                                           
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Person(Base): 
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)

engine = create_engine('sqlite:///sqlalchemy_example.db')

# Create all tables in the engine. This is equivalent to "Create Table"
# statements in raw SQL.
Base.metadata.create_all(engine)

Base.metadata.bind = engine

DBSession = sessionmaker(bind=engine)
session = DBSession()

# Insert a Person in the person table
new_person = Person(name='new person')
session.add(new_person)
session.commit()

and then I tried to read it using pyDAL reference.

from pydal import DAL, Field
db = DAL('sqlite://sqlalchemy_example.db', auto_import=True)
db.tables
>> []
db.define_table('person', Field('name'))
>> OperationalError: table "person" already exists

How do I access the table using pyDAL?

thank you

Answer 1

First, do not set auto_import=True, as that is only relevant if pyDAL *.table migration metadata files exist for the tables, which will not be the case here.

Second, pyDAL does not know the table already exists, and because migrations are enabled by default, it attempts to create the table. To prevent this, you can simply disable migrations:

# Applies to all tables.
db = DAL('sqlite://sqlalchemy_example.db', migrate_enabled=False)

or:

# Applies to this table only.
db.define_table('person', Field('name'), migrate=False)

If you would like pyDAL to take over migrations for future changes to this table, then you should run a "fake migration", which will cause pyDAL to generate a *.table migration metadata file for this table without actually running the migration. To do this, temporarily make the following change:

db.define_table('person', Field('name'), fake_migrate=True)

After leaving the above in place for a single request, the *.table file will be generated, and you can remove the fake_migrate=True argument.

Finally, note that pyDAL expects the id field to be an auto-incrementing integer primary key field.