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

Anthony · Accepted Answer · 2018-08-28 13:45:26Z

2

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.

answered Aug 28, 2018 at 13:45

Anthony

25.5k3 gold badges31 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

evan54 Over a year ago

thank you this. answers the question. I guess one thing is I don't fully understand what the .table files do. it seems like I'm able to use the table just fine without the by doing migrate=False

Anthony Over a year ago

When pyDAL runs a migration (create table, alter table), it adds some metadata to an associated *.table file to maintain a record of the current schema in the database (so it can determine what future migrations to run if you make a change to the model definition). If you do not want pyDAL to manage migrations (i.e., automatically change the database schema when you change the model definition), then you can leave migrate=False and do not need the *.table file.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related