librelist archives

« back to archive

Multiple Database Binds without SQLAlchemy

Multiple Database Binds without SQLAlchemy

From:
Gary Chambers
Date:
2013-07-12 @ 21:08
All,

Is there an existing pattern, or can anyone recommend one to make multiple
connections to a database without using SQLAlchemy?  It's too much
abstraction for my novice OOP skills.  Thank you in advance.

--
G.

Re: [flask] Multiple Database Binds without SQLAlchemy

From:
Owein Reese
Date:
2013-07-12 @ 22:14
Any sort of connection pool? For postgres I can recommend pgbouncer. Very
good library.
On Jul 12, 2013 5:10 PM, "Gary Chambers" <gwchamb@gwcmail.com> wrote:

> All,
>
> Is there an existing pattern, or can anyone recommend one to make multiple
> connections to a database without using SQLAlchemy?  It's too much
> abstraction for my novice OOP skills.  Thank you in advance.
>
> --
> G.
>

Re: [flask] Multiple Database Binds without SQLAlchemy

From:
Gary Chambers
Date:
2013-07-13 @ 00:05
Owein,

>> Is there an existing pattern, or can anyone recommend one to make multiple
>> connections to a database without using SQLAlchemy?  It's too much
>> abstraction for my novice OOP skills.  Thank you in advance.

> Any sort of connection pool? For postgres I can recommend pgbouncer. Very
> good library.

Thank you for your reply.  I should have been a bit more specific in my
request.  I am looking for a pattern to bind a single application (a web
site in this case) to multiple (separate schemas) database connections.  I
can brute force it, but I was hoping to, for example, use the before_request
and teardown_request decorators.

--
G.

Re: [flask] Multiple Database Binds without SQLAlchemy

From:
YKdvd
Date:
2013-07-13 @ 01:33
>>> Is there an existing pattern, or can anyone recommend one to make
>>> multiple
>>> connections to a database without using SQLAlchemy?  It's too much
>>> abstraction for my novice OOP skills.  Thank you in advance.

> Thank you for your reply.  I should have been a bit more specific in my
> request.  I am looking for a pattern to bind a single application (a web
> site in this case) to multiple (separate schemas) database connections.  I
> can brute force it, but I was hoping to, for example, use the
> before_request and teardown_request decorators.

Is it that you want to avoid SQLAlchemy's ORM and mapping stuff, and issue
your own SQL statements?  In that case you could probably just use the
low-level stuff from SQLAlchemy's Core (the Engine and Connection
objects), which would give you all its support for multiple SQL flavors. 
There is a ["strategy='threadlocal'] option to create_engine that sounds
like it might ease working with the flask request context - initialize
your multiple engines during app config, and get and close connections
from them during before/teardown.  This is just off the top of my head,
but it would be worth looking into.

Re: [flask] Multiple Database Binds without SQLAlchemy

From:
Gary Chambers
Date:
2013-07-15 @ 14:02
YKdvd,

Thanks for the reply, and apologies to the list in advance for the lengthy
reply.

> Is it that you want to avoid SQLAlchemy's ORM and mapping stuff, and issue
> your own SQL statements?

Yes.

> In that case you could probably just use the low-level stuff from
> SQLAlchemy's Core (the Engine and Connection objects), which would give
> you all its support for multiple SQL flavors.

That's been my first step, but there's a theoretical piece that I must be
missing.  How does one simply connect to an existing database with
SQLAlchemy?  Here's something that works (and in this case is a database
view) but, based upon the search results I've obtained, I seem to be the
only one doing it this way:

app = Flask(__name__)
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
# app.config['SQLALCHEMY_ECHO'] = True
app.config['SQLALCHEMY_BINDS'] = {'db1': 'postgresql://...',
                                   'db2': 'postgresql://...'}
db = SQLAlchemy(app)

class Engreqs(db.Model):
     __bind_key__ = 'db1'
     __tablename__ = 'vengreqs'
     __table_args__ = {'autoload': True,
                       'autoload_with': db.get_engine(app, bind=__bind_key__)}
     engreq_id = db.Column(db.Integer, primary_key=True)


open_reqs = Engreqs.query.filter(Engreqs.status.in_(['NEW', 'OPEN'])).all()
print [(c.engreq_id, c.status) for c in open_reqs]

> There is a ["strategy='threadlocal'] option to create_engine that sounds
> like it might ease working with the flask request context - initialize
> your multiple engines during app config, and get and close connections
> from them during before/teardown.  This is just off the top of my head,
> but it would be worth looking into.

Would I be able to achieve what I want to do with blueprints?  For example
each separate connection would be contained within the blueprint:

mod = Blueprint('app1', __name__, url_prefix='/app1')

def connect_db():
     return(postgresql db handle)

@mod.before_request()
def before_request():
     g.db = connect_db()

@mod.teardown_request()
def teardown_request(exception):
     g.db.close()

Thank you.

--
G.

Re: [flask] Multiple Database Binds without SQLAlchemy

From:
YKdvd
Date:
2013-07-15 @ 15:30
I may have misunderstood what you wanted to do.  For clarity, I'll use
"SQLAlchemy" for the actual SQLAlchemy, and flask-sqlalchemy for the flask
wrapper.  The latter is where you are getting thing like the "db =
SQLAlchemy(app)" lines.

You said you wanted to avoid the ORM and mapping stuff, but that's what
your Engreqs class is using, and the "query" method and so on.  If you are
comfortable with this there would seem to be no need to avoid the
flask-sqlalchemy wrapper to go with low-level SQLAlchemy routines and
issue raw SQL commands.  I'm not sure about the "autoload" instead of
defining your columns, but if it is working, then great.

So, are you trying to read from a bunch of different tables in different
databases? If so, wouldn't you just define the rest of your table models
the way you did Engreqs, and indicate which of your binds they each happen
to reside in, just as you did?  In this case, Engreqs records always come
from "db1"?  You can probably get flask-sqlalchemy experts here to help
you tweak any small problems you may be having.

Or, do you have a bunch of databases (db1, db2, etc), that all have that
same Engreqs table, and you want to choose which database you are
retrieving Engreqs from during the Flask request, and it might be
different each request?  In that case the flask-sqlalcemy wrapper may not
be flexible enough for you, as I don't think you can change what engine a
model refers to on the fly like that?

In my case, I had something like the second alternative, with multiple
schemas all containing the same table definitions, and needing to choose
which one to access based on the request URL.  I was also already
accessing my data using SQLAlchemy from non-flask code and had models
defined and everything, so I just skipped flask-sqlalchemy altogether.
Instead I create my own locally proxied "db" variable, which has a scoped
session for the appropriate engine based on the request.  But this
probably isn't for the squeamish… :)


On 2013-07-15 11:02 AM, "Gary Chambers" <gwchamb@gwcmail.com> wrote:

YKdvd,

Thanks for the reply, and apologies to the list in advance for the lengthy
reply.

Is it that you want to avoid SQLAlchemy's ORM and mapping stuff, and issue
your own SQL statements?

Yes.

In that case you could probably just use the low-level stuff from
SQLAlchemy's Core (the Engine and Connection objects), which would give
you all its support for multiple SQL flavors.

That's been my first step, but there's a theoretical piece that I must be
missing.  How does one simply connect to an existing database with
SQLAlchemy?  Here's something that works (and in this case is a database
view) but, based upon the search results I've obtained, I seem to be the
only one doing it this way:

app = Flask(__name__)
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
# app.config['SQLALCHEMY_ECHO'] = True
app.config['SQLALCHEMY_BINDS'] = {'db1': 'postgresql://...',
                                   'db2': 'postgresql://...'}
db = SQLAlchemy(app)

class Engreqs(db.Model):
     __bind_key__ = 'db1'
     __tablename__ = 'vengreqs'
     __table_args__ = {'autoload': True,
                       'autoload_with': db.get_engine(app,
bind=__bind_key__)}
     engreq_id = db.Column(db.Integer, primary_key=True)


open_reqs = Engreqs.query.filter(Engreqs.status.in_(['NEW', 'OPEN'])).all()
print [(c.engreq_id, c.status) for c in open_reqs]

There is a ["strategy='threadlocal'] option to create_engine that sounds
like it might ease working with the flask request context - initialize
your multiple engines during app config, and get and close connections
from them during before/teardown.  This is just off the top of my head,
but it would be worth looking into.

Would I be able to achieve what I want to do with blueprints?  For example
each separate connection would be contained within the blueprint:

mod = Blueprint('app1', __name__, url_prefix='/app1')

def connect_db():
     return(postgresql db handle)

@mod.before_request()
def before_request():
     g.db = connect_db()

@mod.teardown_request()
def teardown_request(exception):
     g.db.close()

Thank you.

--
G.

Re: [flask] Multiple Database Binds without SQLAlchemy

From:
Gary Chambers
Date:
2013-07-16 @ 18:51
YKdvd,

> I may have misunderstood what you wanted to do.  For clarity, I'll use
> "SQLAlchemy" for the actual SQLAlchemy, and flask-sqlalchemy for the flask
> wrapper.  The latter is where you are getting thing like the "db =
> SQLAlchemy(app)" lines.

I apologize.  I want to avoid using SQLAlchemy altogether, but I was willing
to try to work with it to accomplish what I was trying to do.

> I'm not sure about the "autoload" instead of defining your columns, but if
> it is working, then great.

From what I can tell, that's the only option available when one doesn't wish
to manually define the columns.  It works, but I was concerned about
unintended side-effects due to my lack of familiarity with it overall.

> So, are you trying to read from a bunch of different tables in different
> databases?

I'm connecting to different schemas (users) in the same PostgreSQL database
(although it could be different databases, too).

> In my case, I had something like the second alternative, with multiple
> schemas all containing the same table definitions, and needing to choose
> which one to access based on the request URL.

In my experimentation, I have discovered that I can do what I seek using
blueprints.  It's possible that I'm abusing them (or have totally missed the
point of their existence), but it works and, more importantly, I understand
it.

> Instead I create my own locally proxied "db" variable, which has a scoped
> session for the appropriate engine based on the request.  But this
> probably isn't for the squeamish… :)

LOL!  I'm not prepared for anything like that, yet.  I arrived here from the
procedural world of C, Perl, and PHP (though the latter two have some
semblance of OOP constructs, I never used them), so I am trying to grasp
concepts and terminology that are foreign to me.

Thank you again for your replies and assistance.

--
G.