RE: [sqlalchemy] reflecting existing databases with no a priori knowledge of their structure
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Harry Percival
Sent: 03 June 2010 16:24
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] reflecting existing databases with no a
priori knowledge of their structure
Hi All,
I'm building a tool to extract info from databases. The
user/programmer doesn't have any advance knowledge of the structure of
the database before they load it, and i want to dynamically generate
mapped classes for the database.
i just want to check there isn't some helpful sqlalchemy stuff that
can make my life easier, cos it feels harder than it should be.
sqlsoup seems to expect you to know table names ahead of time. i can't
find a way of extracting a list of table names from db =
SqlSoup(engine) and i'm finding myself generating classes on the fly
using the type() function.
stuff like:
meta.reflect(bind=engine)
tables = meta.raw_tables
class MyTable(object):
pass
for t in tables:
tempclass =
type('Table%d'%counter,(MyTable,),{'engine':self.engine})
mapper(tempclass,t)
then i use a bunch of classfunctions hanging off MyTable to do things
like return select alls ... anyways, this feels harder than it should
be. am i missing something? or is sqlalchemy simply not really used
much to work with existing / arbitrary databases?
I'm not quite sure what you're asking for. Once you've used meta.reflect to
reflect all your tables, you can pass that metadata instance to the SqlSoup
constructor. So you now have all the table names available in MetaData.tables
(or MetaData.sorted_tables), and you can access the mapped classes via
SqlSoup.entity(table_name).
For example:
import sqlalchemy as sa
from sqlalchemy.ext.sqlsoup import SqlSoup
meta = sa.MetaData('db://user:passw...@host/database')
meta.reflect()
db = SqlSoup(meta)
for table in meta.sorted_tables:
cls = db.entity(table.name)
print cls
print cls.get(1)
Hope that helps,
Simon
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] reflecting existing databases with no a priori knowledge of their structure
On Jun 3, 2010, at 11:24 AM, Harry Percival wrote:
Hi All,
I'm building a tool to extract info from databases. The
user/programmer doesn't have any advance knowledge of the structure of
the database before they load it, and i want to dynamically generate
mapped classes for the database.
i just want to check there isn't some helpful sqlalchemy stuff that
can make my life easier, cos it feels harder than it should be.
sqlsoup seems to expect you to know table names ahead of time. i can't
find a way of extracting a list of table names from db =
SqlSoup(engine) and i'm finding myself generating classes on the fly
using the type() function.
the engine has a table_names function, also metadata has a reflect() method
that pulls in a whole schema and you can peek into the metadata.tables
dictionary. There is also an Inspector interface that gives you all sorts of
info about a database.
http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html?highlight=table_names#sqlalchemy.engine.base.Engine.table_names
http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html?highlight=reflect#sqlalchemy.schema.MetaData.reflect
the inspector appears like it hasn't made it into the docs yet (that's a bug).
It's here:
http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/engine/reflection.py
stuff like:
meta.reflect(bind=engine)
tables = meta.raw_tables
class MyTable(object):
pass
for t in tables:
tempclass = type('Table%d'%counter,(MyTable,),{'engine':self.engine})
mapper(tempclass,t)
then i use a bunch of classfunctions hanging off MyTable to do things
like return select alls ... anyways, this feels harder than it should
be. am i missing something? or is sqlalchemy simply not really used
much to work with existing / arbitrary databases?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
