On Mar 21, 2014, at 5:01 PM, Bao Niu <niuba...@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 .
>
>
> On Fri, Mar 21, 2014 at 7:10 AM, Michael Bayer <mike...@zzzcomputing.com>
> wrote:
> 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.
>
>
> On Mar 21, 2014, at 2:18 AM, Bao Niu <niuba...@gmail.com> wrote:
>
>> 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.
>>
>> --
>> 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+unsubscr...@googlegroups.com.
>>
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
