Using sqlalchemy models with run-time connection

[Alexander Perepelica]

Hello!

I use sqlalchemy in flask app and try create some database editor.
And my problem is using sqlalchemy models and give to user possibility change IP address of database server (or connect to server at runtime).
Can I do such things with sqlalchemy?

Thank you!

[Michael Bayer]

SQLAlchemy uses the Python DBAPI to connect to a database, see http://www.python.org/dev/peps/pep-0249/.

So as far as the details of TCP/IP connections, SQLAlchemy just passes that information on to the DBAPI.

For example, connecting to Postgresql with psycopg2:

engine = create_engine(“postgresql+psycopg2://scott:tiger@somehostname/test”)
sqla_conn = engine.connect()

the above does the same as:

import psycopg2
conn = psycopg2.connect(user=‘scott’, passwd=‘tiger’, host=‘somehostname’, database=‘test’)

So, whatever hostname you put into “create_engine()” there, that’s the one that engine will connect to.

If then you had an application where you want to at some point change to a different hostname, you’d make a new engine. you can also dispose() the old one

engine.dispose()
engine = create_engine(“postgresql+psycopg2://scott:tiger@newhostname/test”)


The reason we call dispose() is because connections are by default pooled, meaning when you say:

sqla_conn.close()

that just returns the “connection” to a pool.

You can *turn that off* by using NullPool:

from sqlalchemy.pool import NullPool

engine = create_engine(“postgresql+psycopg2://scott:tiger@somehostname/test”, poolclass=NullPool)

with the above, you can make a new engine later and you don’t need to dispose() the old one, just make sure whatever connections were opened were closed.

[Alexander Perepelica]

Ok, I understand, but how can I bind model with this session? Must  I use declarative_base or mapping approach?

[Michael Bayer]

On Feb 16, 2014, at 7:05 PM, Alexander Perepelica <perepel...@gmail.com> wrote:

> Ok, I understand, but how can I bind model with this session? Must I use declarative_base or mapping approach?

the concept of “the thing that talks to a database” and “the kinds of structures that write SQL for us” are separate. You might have seen some old tutorials that talk about this thing called “metadata.bind = engine”, or even “BoundMetaData”, you should ignore that stuff completely.

so the Session here is the “thing that talks to a database”. It’s a semi-short lived object that usually spans the length of a transaction, or possibly several, but always within the scope of “here’s some things we want to do with a database connection”:

sess = Session(engine)


the mapping()/declarative_base() all that, knows nothing about any Session or Engine. That has to do with the Table object, which is a thing that talks about what a hypothetical database table looks like, which is then inside a collection of other Table objects called a MetaData, which is essentially just a dictionary, and then mapping has to do with taking any kind of Python class you’ve made and linking it to a Table. Declarative base just does class->mapper->Table with less typing. You do whatever method you want to get a Table, and if you want, a class mapped to it. Then you can create SQL constructs using those objects, and those SQL constructs, when passed along to a Session or Engine, get turned into strings and sent to a real database over a connection.

With the ORM, the connection point is kind of automatic, because you start with a Session:

sess = Session(engine)

then the Session is a factory for a Query:

my_query = sess.query(MyMappedClass).filter_by(id=7)

the “my_query” above is a Query object that refers to our “session”. This is just how the API is, it really doesn’t matter too much that it is linked to the Session as of yet, since it hasn’t don’t anything with a database yet.

But when you say this:

my_query.all()

now we’re going to look at self.session, which will look at self.bind, which will do “conn = self.bind.connect()) and then “conn.execute(my_query.statement)”.

So the linkage of “databases” to a “model” is made as late as possible, and only within the scope of when a query actually executes.

[Alexander Perepelica]

Great, so I define in my code

session = None
Base = declarative_base()

class Model1(Base):
     id = ....

and after user input host IP we can execute code

engine = create_engine(“postgresql+psycopg2://scott:tiger@somehostname/test”, poolclass=NullPool) 

session = sessionmaker(bind=engine)

and last point how can set new session to Base?

[Michael Bayer]

you don’t.  It’s only when you use session.query(MyClass) or session.add(MyClass()) that those states are associated.

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Alexander Perepelica]

Aha, Base (declarative_base) don't need proper connection to db in any point. Connection with database must be established only when execute queries.
Thank you very much!