On Jul 17, 2009, at 1:04 PM, Dimitri Tcaciuc wrote:
>
>
> Hey everyone,
>
> I have a main database file x.db which contains some tables with the
> bulk of data I'm working with. Now, when the operating program
> connects to it, given a unique key, I'd like to cache some data. What
> I was going to do is to have the program connect to database y.db,
> which executes "attach database x.db as base".
>
> The issue is that it seems SQLite chokes on schema specifier in
> foreign keys (which is consistent with their website docs), which is
> what SQLA tries to execute. Here's the isolated problem case:
>
> bash $ sqlite3 y.db
> SQLite version 3.6.2
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> attach database "x.db" as base;
> sqlite> create table base.foo (id integer not null primary key, name
> varchar);
> sqlite> create table base.bar (id integer not null primary key, foo_id
> integer not null, name varchar, foreign key (foo_id) references
> base.foo(id) );
> SQL error: near ".": syntax error
>
> SQLA definitions for the above would be:
>
> (preceded by "attach database "x.db" as base" in connection listener)
>
> foo = Table("foo", metadata,
> Column("id", Integer, primary_key=True),
> Column("name", String),
> schema="base"
> )
>
> bar = Table("bar", metadata,
> Coumn("id", Integer, primakry_key=True),
> Column("foo_id", Integer, ForeignKey(foo.c.id),
> Column("name", String),
> schema="base"
> )
>
> Is this an expected behavior and what could I do in this situation?
> The main point is to create some cached tables in the y.db cache that
> I could seamlessly use in join statements with data from x.db.
options are:
1. dont use ForeignKey(), which in SQLite don't have any effect
anyway. If you're using the ORM, relation() can get by without
ForeignKey by using the primaryjoin and foreign_keys arguments.
2. issue the CREATE TABLE statements as regular engine.execute("""),
and keep the ForeignKey usage in your app so that joins still know how
to auto-generate.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
