SA support for SQLite ATTACH?

[Karlo Lozovina]

Greetings everyone,

does SQLAlchemy somehow support SQLites' ATTACH DATABASE statement? I
have a in-memory SQLite database that I want to dump to file, so I was
thinking of using ATTACH to do it. Any other ideas welcome ;).

Thanks in advance,
Karlo.

[Michael Bayer]

attach database ...wow i never knew it had that ! if its a matter of
issuing the string "attach database", just use literal text() or
engine.execute().

attach database....very handy !

[Karlo Lozovina]

On Mar 9, 4:33 pm, "Michael Bayer" <zzz...@gmail.com> wrote:

> attach database ...wow i never knew it had that ! if its a matter of
> issuing the string "attach database", just use literal text() or
> engine.execute().

Me neither ;), until the other day I started looking for an efficient
way to dump in-memory SQLite databases to hdd. And this ATTACH thing
sounded just like made for it.

> attach database....very handy !

So, let's say I issue this engine.execute(), how would one proceede
inserting rows into this attached database. Let's say mem_db is the
main, in-memory database, and file_db has just been attached, and is a
in-file database. SQL syntax is somewhat like this: "INSERT INTO
file_db.table1 SELECT * FROM mem_db.table1", is any way SQLAlchemy can
do this without resorting to engine.execute() ?

Thanks!

Karlo.

[Michael Bayer]

OK, just read over ATTACH, you issue the "ATTACH DATABASE" command
textually. from that point on, the tables in that database are
accessible using a schemaname syntax (i.e. schemaname.tablename).
if you really want to just copy tables wholesale, yeah youd have to
issue "INSERT ... SELECT" statements which also would have to be
texual at this point. other operations can be done if you define
Table objects with "schema=<whatever>", and you can also use
autoload=True to read them in for you.

[VN]

I tried to get this to work but did not succeed. Suppose I have two
databases dbA and dbB which have one table each dbAtbl and dbBtbl.
Both tables have a column id on which I would like to perform a join.

import sqlalchemy as SA
md_dbA=SA.BoundMetaData('dbA')
tbl_dbAtbl=SA.Table('dbAtbl',md_dbA,autoload=True)

SA.text("ATTACH DATABASE 'C:Temp\dbA.db' as dbA_db")

How can I now perform the join? I am not able to see dbBtbl.

[Michael Bayer]

you might need to use a Table with a "schema='dbA_db'", just a
guess. look at the echoed sql and see if it matches things that work
at the sqlite console.

[VN]

I tried the following:

import sqlalchemy as SA
md_dbA=SA.BoundMetaData('dbA')
tbl_dbAtbl=SA.Table('dbAtbl',md_dbA,autoload=True)

SA.text("ATTACH DATABASE 'C:Temp\dbB.db' as dbB_db")
tbl_dbBtbl=SA.Table('dbB_db.dbBtbl',md_dbA,schema='dbB_db',autoload=True)
<=======

The code fails on the line marked above. Did I mis-understand what you
said?

Also, how do I look at the echoed SQL for "text" function?

[VN]

I tried the following:

import sqlalchemy as SA
md_dbA=SA.BoundMetaData('dbA')
tbl_dbAtbl=SA.Table('dbAtbl',md_dbA,autoload=True)

SA.text("ATTACH DATABASE 'C:Temp\dbB.db' as dbB_db")

tbl_dbBtbl=SA.Table('dbB_db.dbBtbl',md_dbA,schema='dbB_db',autoload=True)
<=======

The code fails on the line marked above. Did I mis-understand what you
said?

Also, how do I look at the echoed SQL for "text" function?

On Apr 30, 9:07 am, Michael Bayer <mike...@zzzcomputing.com> wrote:

[Michael Bayer]

On May 1, 2007, at 12:24 PM, VN wrote:

>
> I tried the following:
>
> import sqlalchemy as SA
> md_dbA=SA.BoundMetaData('dbA')
> tbl_dbAtbl=SA.Table('dbAtbl',md_dbA,autoload=True)
>
> SA.text("ATTACH DATABASE 'C:Temp\dbB.db' as dbB_db")
> tbl_dbBtbl=SA.Table
> ('dbB_db.dbBtbl',md_dbA,schema='dbB_db',autoload=True)
> <=======
>
> The code fails on the line marked above. Did I mis-understand what you
> said?

first thing, the "schema" argument that is "dbB_db" is separate from
the tablename. so tablename must be "dbBtbl".

also, I doubt that sqlite reflection code is working for tables in
alternate "schemas" right now. it uses PRAGMA table_info
(tablename). what happens if you say PRAGMA table_info
(dbB_db.dbBtbl) (at the sqlite3 command prompt) ? if PRAGMA
table_info doesnt work for attached tables, then this feature would
be impossible to add.

> Also, how do I look at the echoed SQL for "text" function?

create_engine('sqlite://', echo=True)

[Barry]

I got this to work by passing a 'creator' argument to create_engine
and doing the ATTACH inside my creator function. Below is a full code
example. Each database contains one table, database_a -> table_a and
database_b -> table_b.

from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper

import sqlalchemy
from sqlalchemy.ext import activemapper, sessioncontext

engine = None

def connect():
from pysqlite2 import dbapi2 as sqlite
conn = sqlite.connect('c:\\tmp\\database_a')
conn.execute("ATTACH DATABASE 'C:\\tmp\\database_b' AS
database_b")
return conn

def create_engine():
global engine
engine = sqlalchemy.create_engine('sqlite:////tmp/database_a',
creator=connect)
metadata.connect(engine)

def create_session():
return sqlalchemy.create_session(bind_to=engine)

metadata = activemapper.metadata
create_engine()
session = activemapper.Objectstore(create_session)
activemapper.objectstore = session

##########################################################################
# Classes
##########################################################################

class A(object): pass
class B(object): pass

##########################################################################
# Tables
##########################################################################

table_a = sqlalchemy.Table('table_a', metadata, autoload=True)

table_b = sqlalchemy.Table('table_b', metadata, autoload=True)

##########################################################################
# Mappings
##########################################################################

assign_mapper(session.context, A, table_a)

assign_mapper(session.context, B, table_b)

import sys

for a in A.select():
print 'id: %s, b_id: %s, s: %s' % (a.id, a.b_id, a.s)

for b in B.select():
print 'id: %s, s: %s' % (b.id, b.s)

print list(select([table_a, table_b], table_a.c.b_id ==
table_b.c.id).execute())

[Ashish Srivastava]

Thanks it is nice post.

I also refer very useful and helpful article about SQLite - ATTACH DATABASE

Please visit this helpful article.

http://www.mindstick.com/blog/10947/SQLite%20ATTACH%20DATABASE#.VffLzhFViko

http://www.tutorialspoint.com/sqlite/sqlite_attach_database.htm