Does "reflection" reflects everything in an existing SQLite database?

[Bao Niu]

I created a database manually using simple sqlite3 in python. When I load tables in that database using SqlAlchemy's reflection mode, i.e. setting autoload=True, I know all the columns will be reflected and mapped to the corresponding classes. However, I'm NOT so sure if the following details will also be reflected automatically:

1) foreign_keys flag

For example, if I already set PRAGMA foreign_keys=ON when I created that database, do I still need to set up an event listener to set that flag for each session when they connect to the database? Or this will be reflected automatically?

2) relationships between two tables

Here I mean if a relationship that is not ambiguous, for example, Users and Addresses, do I still need to type in and explicitly define an address attribute in Users class? Could this be deduced by SqlAlchemy automatically?

(I tried but failed, but I think this might make sense if SqlAlchemy can do this automatically when reflecting)

3) constraints

Currently when I examine the __table__ attribute I simply cannot find any constraint listed there. I set several ON DELETE RESTRICT, ON UPDATE CASCADE when I created this database but they are not visible to me even after autoload=True.

I'd really appreciate some insight here.  Thanks in advance.

[Michael Bayer]

when using FK constraints with sqlite, the only impact that “PRAGMA foreign_keys=ON” has is whether or not the foreign keys are enforced when INSERT, UPDATE or DELETE statements are received by the sqlite database.    It has no impact on reflection, SQLite is nice enough to tell us about the FOREIGN KEY constraints that are present whether or not this pragma is enabled.

so as far as the behavior of reflection and relationship(), PRAGMA foreign_keys has no effect.   

as far as ON DELETE RESTRICT and other directives like that i don’t think SQLAlchemy’s SQLite dialect parses those directives right now.  SQLite isn’t the kind of database that people usually get too deeply involved with constraints and triggers and stuff like that.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Bao Niu]

Hi Michael,

Thanks for your explanation. Let me build on your explanation and ask for some further clarification.

Suppose I have the following snippet:

import sqlalchemy

import sqlalchemy.ext.declarative

import sqlalchemy.orm

import sqlalchemy.engine

@sqlalchemy.event.listens_for(sqlalchemy.engine.Engine, "connect")

def set_sqlite_pragma(dbapi_connection, connection_record):

    cursor = dbapi_connection.cursor()

    cursor.execute("PRAGMA foreign_keys=ON")

    cursor.close()

This snippet only serves one purpose, which is setting FOREIGN_KEYS=ON. As you pointed out in last email, if the database already *knows* this flag's value when it was created, I don't see any point running the above snippet. So my assumption is, as long as I set PRAGMA foreign_keys=ON when I created the database, I don't need to remember to turn it on when I use the database foreign keys later. Is my understanding correct?

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/-JDkPBG3QOM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Michael Bayer]

On Mar 21, 2014, at 5:01 PM, Bao Niu <niub...@gmail.com> wrote:

Hi Michael,

Thanks for your explanation. Let me build on your explanation and ask for some further clarification.

Suppose I have the following snippet:

import sqlalchemy

import sqlalchemy.ext.declarative

import sqlalchemy.orm

import sqlalchemy.engine

@sqlalchemy.event.listens_for(sqlalchemy.engine.Engine, "connect")

def set_sqlite_pragma(dbapi_connection, connection_record):

    cursor = dbapi_connection.cursor()

    cursor.execute("PRAGMA foreign_keys=ON")

    cursor.close()

This snippet only serves one purpose, which is setting FOREIGN_KEYS=ON. As you pointed out in last email, if the database already *knows* this flag's value when it was created,

I don't see any point running the above snippet. So my assumption is, as long as I set PRAGMA foreign_keys=ON when I created the database, I don't need to remember to turn it on when I use the database foreign keys later. Is my understanding correct?

the database doesn’t “know” anything about PRAGMA FOREIGN KEYS when you first connect.  SQLite’s behavior here is that it has to be set for every new connection, if you want it to be on.  

This flag is documented at http://www.sqlite.org/foreignkeys.html#fk_enable .