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?
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.
