autoload how?

[Lukasz Szybalski]

Hello,

In tg2.

cp model.template mymodel.py

Inside I put what? If I do this then system complains about:

recall_table = Table('recall_db', metadata, autoload=True)

class Recall(object):
def __init__(self, **kw):
"""automatically mapping attributes"""
for key, value in kw.iteritems():
setattr(self, key, value)

#Mapping of Table to Python Object Class
mapper(Recall, recall_table)

and the bottom of the __init__.py says:
from myapp.model.mymodel import Recall

That doesn work because:

sqlalchemy.exc.UnboundExecutionError: The MetaData is not bound to an
Engine or Connection. Execution can not proceed without a database to
execute against. Either execute with an explicit connection or assign
the MetaData's .bind to enable implicit execution.

So I read in __init__

def init_model(engine):
"""Call me before using any of the tables or classes in the model."""

DBSession.configure(bind=engine)
# If you are using reflection to introspect your database and create
# table objects for you, your tables must be defined and mapped inside
# the init_model function, so that the engine is available if you
# use the model outside tg2, you need to make sure this is called before
# you use the model.

#
# See the following example:

#global t_reflected

#t_reflected = Table("Reflected", metadata,
# autoload=True, autoload_with=engine)

#mapper(Reflected, t_reflected)

But what does that mean???
Where did Reflected came from??
How should I use get my model imported so it stays in the mymodel.py
file and the __init__ only imports it.?

Ideas?

Thanks,
Lucas

[Crusty]

Hey there Lukasz,

you need to import all that into your mymodel.py of course.

from [your_project_here].model import DeclarativeBase, DBSession, metadata

then metadata is bound. I dont know what this has to do with
"autoload" but your exception complains about metadata being unbound.
Also I would suggest you to go with declarative syntax, if you dont
really need the flexibility of a non declarative setup.

Actually if you had really copied the model template, this would be
all in your file already, so I'm a bit surprised.
After youre done in mymodel.py you simply import it in
/models/__init__.py, at the bottom of the file like so:

from mymodel import Recall

Hope I didnt misunderstand your problem.

Greetings,

Tom

[Crusty]

Hey there,

sorry I just re-read all that, guess the coffee didnt kick in yet,
ignore my answer please :P
I misread the UnboundException thingy, so my answers was totally useless.

[Crusty]

Alright there, second try Lukasz ;)

the first thing you need to do, according to SA docs is having
metadata bound to a session:

"To use autoload=True, the table's MetaData object need be bound to an
Engine or Connection, or alternatively the autoload_with=<some
connectable> argument can be passed."

you can do this in 2 ways, first is like this:

http://www.sqlalchemy.org/docs/05/metadata.html?highlight=metadata#binding-metadata-to-an-engine-or-connection

engine = create_engine('sqlite://', **kwargs)

# create MetaData
meta = MetaData()

# bind to an engine
meta.bind = engine

and second is: you simply go with the autoload_with parameter, as
specified in the comments of /models/__init__.py:

t_reflected = Table("Reflected", metadata, autoload=True,
autoload_with=engine)

According to the SA docs:

http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/schema.html?highlight=table#sqlalchemy.schema.Table
Names which contain no upper case characters will be treated as case
insensitive names, and will not be quoted unless they are a reserved
word. Names with any number of upper case characters will be quoted
and sent exactly. Note that this behavior applies even for databases
which standardize upper case names as case insensitive such as Oracle.

So "Reflected" is simply a case sensitive table name.

My guess: like so many other TG things, the example would simply not
work as it is presented.

You should be right about your question "where does Reflected come
from", because I think, it didnt come from anywhere, it would throw a
syntax error.
Just try writing:

class Reflected(objec): pass

t_reflected = Table("Reflected", metadata, autoload=True,
autoload_with=engine)

mapper(Reflected, t_reflected)

that should do the job.

And sorry for the useless answer(s) from before, definitly a reading
/fail due to morning delirium.

Greetings,

Tom

[Lukasz Szybalski]

What you are really saying is that you cannot autoload a table in any
other file except for __init__.py
You can define new tables but not autolaod them.

Here is what finally works. How would I make this a declarative
autoload? Since there is no examples I can't really tell.

Before init_model:
from sqlalchemy import Table
from sqlalchemy.orm import mapper, relation

Inside the init_model do:

recall_table = Table('recall_db', metadata,

autoload=True,autoload_with=engine)

class Recall(object):
def __init__(self, **kw):
"""automatically mapping attributes"""
for key, value in kw.iteritems():
setattr(self, key, value)

#Mapping of Table to Python Object Class

mapper(Recall, recall_table,primary_key=[recall_table.c.RECORD_ID])

I'm not sure what the global portion of the example code is referring
to? What is the declarative version?

Question 2:
How to autoload 129 tables in the database?

#for name in engine.execute("SHOW TABLES"):
# tables[name] = sqlalchemy.Table(name, metadata, autoload=True)

But how do I get the python class, and mappers for all these tables?

Thanks,
Lucas

[Crusty]

Hey Lukasz,

theoretically declarative setup allowes autoload as well.
I have never tried it, but its in the SA docs.

It roughly goes like this:

http://www.sqlalchemy.org/docs/05/reference/ext/declarative.html?highlight=__table_args__#table-configuration

class MyClass(DeclarativeBase):
__tablename__ = 'sometable'
__table_args__ = (
ForeignKeyConstraint(['id'], ['remote_table.id']),
UniqueConstraint('foo'),
{'autoload':True}
)

Plus other things you might wanna add there.

Getting the python classes for 129 tables is a whole different story.
I personally would go with a class factory, but it feels kinda wrong.
I think youre moving into some area where it's not TG related, but SA
related. Probably you should trying reposting this on the SA mailing
list, Michael Bayer is usually there to answer tricky questions ;)

Greetings,

Tom

[Martin Keegan]

> > Question 2:
> > How to autoload 129 tables in the database?
>
> > #for name in engine.execute("SHOW TABLES"):
> > #   tables[name] = sqlalchemy.Table(name, metadata, autoload=True)
>
> > But how do I get the python class, and mappers for all these tables?
>
> > Thanks,
> > Lucas
>

I think I'm trying to solve the same problem as you :)

I created a dummy module APPNAME.model.reflected and imported it in
APPNAME/model/__init__.py

then

class ReflectedTable(object):
def __init__(self, **kwargs):
for k, v in self.iteritems():
setattr(self, k, v)

def init_model(engine):
"""Call me before using any of the tables or classes in the
model."""

global metadata

DBSession.configure(bind=engine)

def table_names():
introspect = '''SELECT name FROM sqlite_master
WHERE type = 'table';'''
for row in engine.execute(introspect):
yield str(row[0])

def do_reflect(name):
class_name = name.title()

reflected_table = Table(name, metadata,
autoload=True, autoload_with=engine)

cls = type(class_name, (ReflectedTable,), {})
mapper(cls, reflected_table)
setattr(APPNAME.model.reflected, class_name, cls)

for name in table_names():
do_reflect(name)

[Lukasz Szybalski]

> Before init_model:
> from sqlalchemy import Table
> from sqlalchemy.orm import mapper, relation
>
> Inside the init_model do:
>    recall_table = Table('recall_db', metadata,
> autoload=True,autoload_with=engine)
>
>    class Recall(object):
>        def __init__(self, **kw):
>            """automatically mapping attributes"""
>            for key, value in kw.iteritems():
>                setattr(self, key, value)
>
>    #Mapping of Table to Python Object Class
>    mapper(Recall, recall_table,primary_key=[recall_table.c.RECORD_ID])
>
>

So now that I have the table in the __init__ file and autoloaded, is
there a reason it does not show up in the admin interface? Do I need
to somehow map it for the admin interface to see it?

Thanks,
Lucas

[Lukasz Szybalski]

Here is the final version.

On Mon, Sep 21, 2009 at 8:45 AM, Lukasz Szybalski <szyb...@gmail.com> wrote:
>> Before init_model:
>> from sqlalchemy import Table
>> from sqlalchemy.orm import mapper, relation
>>

>> class Recall(object):
>> def __init__(self, **kw):
>> """automatically mapping attributes"""
>> for key, value in kw.iteritems():
>> setattr(self, key, value)

>> Inside the init_model do:

>>    recall_table = Table('recall_db', metadata,
>> autoload=True,autoload_with=engine)
>>
>>    #Mapping of Table to Python Object Class
>>    mapper(Recall, recall_table,primary_key=[recall_table.c.RECORD_ID])
>>

The python class need to be outside of the init model function. It
could probably be imported as well.

Now your model should show in admin interface. If you are using rum
then you can even download a csv of the data. Not sure if catwalk has
that.

Now I just have to try sqlsoup to see if I can preload 100+ tables
without typing mappers, and table autoloads.

Thanks,
Lucas