Re: [sqlalchemy] Sqlalchemy, Postgres and SSL
this would look like:
e = create_engine("postgresql+psycopg2://scott:tiger@host/dbname",
connect_args={'sslmode':'require'})
or
e =
create_engine("postgresql+psycopg2://scott:tiger@host/dbname?sslmode=require")
SSL for psycopg2 is described at:
http://initd.org/psycopg/docs/module.html#psycopg2.connect
http://www.postgresql.org/docs/9.0/static/libpq-ssl.html#LIBPQ-SSL-SSLMODE-STATEMENTS
On May 6, 2011, at 10:05 AM, mando wrote:
> Hi, how can I set my sqlalchemy engine to connect to a postgres on
> remote setting the ssl mode as allow?
>
> Thanks a lot!
>
> Luca
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@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 sqlalchemy@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] Re: deferred column sometimes lazily loaded, sometimes not
On May 6, 2011, at 8:53 PM, Bill Curtis wrote: > I think I have figured out what's going on here. > > request-1: > > 1.1) query runs, model object gets loaded > 1.2) model object gets stuffed in session.identity map > 1.3) model object gets stuffed in secondary beaker cache > 1.4) deferred field gets loaded > 1.5) model object gets updated in session identity map > 1.6) somehow secondary cache gets updated > > request-2: > > 2.1) query runs, model object gets loaded FROM secondary cache > 2.2) model object gets stuffed in session.identity map > 2.3) deferred field does not need to be loaded, b/c it was in secondary > beaker cache > > Periodically, the cache times out, and I request-1 behavior repeats. > > I'm a little unclear exactly when and how step 1.6 is happening. If anyone > has thoughts, I'd be happy to hear them. My beaker-cache code it largely > derived from the example here, if anyone else is using it: if a query is run with the beaker cache switch on, a row representing the object in question is loaded, the row matches the object in the identity map, the beaker caching query then caches the results of that load, including your object that came from the identity map. I dont understand the ultimate issue, unless its that you're getting the wrong data back. if its just that the data is being cached instead of it loading deferred, then yes that's just the caching query happening. it would need to be more careful about the state its placing in the cache - like, when the object is serialized for caching, have it expire those attributes you don't want in the cache. > > FWIW, my higher-level concerns are around how to find and invalidate objects > in the secondary cache, once they have become dirty. the way the example works right now, you need to create a Query that represents the same cache key as one that you'd like to clear out, and invalidate. if you're looking to locate objects based on identity, you'd probably want to change the scheme in which data is cached - probably cache result sets which serialize only a global identifier for each object, then store each object individually under those individual identifiers. it would be slower on a get since it means a get for the result plus a get for each member, but would allow any identity to be cleared globally.the get for each member could be ameliorated by the fact that they'd be in the identity map first, before out in a cache. not at all simple and its why 2nd level caching is not a built in feature ! too many ways to do it. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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.
[sqlalchemy] deferred column sometimes lazily loaded, sometimes not
I put a breakpoint inside Session.query(), and I'm seeing some behavior that I don't understand. I'm using scoped_session() in my startup code, and calling Session.remove() in a finally block of my pylons base controller. This means that each web request should get a completely new SQAlchemy Session object, if I understand correctly. If I continue from the breakpoint to the next break, I can inspect the session after the first query on my page has been run. I can also confirm, before running the first query, that the session's identity map is EMPTY. The model object returned by the first query has a deferred column. Sometime, if I print this after running the first query: self.identity_map.values()[0]._sa_instance_state.callables I get a hash with containing the loader for the deferred column. I these cases, if access the deferred column, I can see via some other logging code that the loader runs at the point. Other times, if I print the _sa_instance_state.callables (again, starting with an empty identity_map, and then stepping thru the same first query) I get back an empty hash. i.e., no loader. Accessing the deferred column at this point does not make the loader code run. So it's as though the column in question sometimes is not being lazily loaded. Can anyone shed light on what's going on? --bill -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] eager loading and a self-referential composite primary key
Okay, that's fair. I figured it was a bit pathological, even though
it's a real-world use case. :)
Sorry I didn't find #1401 in my searches. Wrong keywords!
I'll see if I can find some time to write up a sqla test case out of
my repro script. Too bad my netbook seems dead set on corrupting my
flash media. :(
Thanks for the prompt response!
On Fri, May 6, 2011 at 11:55 AM, Michael Bayer wrote:
> OK this is actually a classic issue, for which no solution has been built
> yet. Mostly because its an incredibly obscure case - there's a way to do it
> in the internals but its a bit of a killer.
>
> we get the relationship's local side:
>
> (Pdb) self.local_side
> OrderedSet([Column('accountid', Integer(), ForeignKey('folder.accountid'),
> table=, primary_key=True, nullable=False), Column('folderid',
> Integer(), table=, primary_key=True, nullable=False)])
>
> we get the relationship's remote side:
>
> (Pdb) self.remote_side
> OrderedSet([Column('accountid', Integer(), ForeignKey('folder.accountid'),
> table=, primary_key=True, nullable=False), Column('parent',
> Integer(), ForeignKey('folder.folderid'), table=, nullable=False)])
>
> and the primaryjoin:
>
> (Pdb) primaryjoin.__str__()
> 'folder.accountid = folder.accountid AND folder.folderid = folder.parent'
>
> we tell ClauseAdapter, "change all the columns in the "folder" table, which
> are not in the "local side", to come from the alias "folder_1"".
>
> when we chain this along several joins, we also have to say:
>
> "change all the columns in the "folder" table, which are not in the "remote
> side", to come from the alias "folder_1"".
>
> the solution has to involve adding annotations to just one side of
> "folderid=folderid", then enhancing ClauseAdapter to be able to take cues
> from callables that look at annotations, instead of just presence in a set.
>
> so the answer right now is that we don't have this one case covered. Its
> ticket 1401 which is two years old, I've added some thoughts there.
>
> the two workarounds are to use joined load with contains_eager(), or the
> hand-rolled subqueryload recipe at
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading .
>
>
> On May 6, 2011, at 11:15 AM, Taavi Burns wrote:
>
>> We have a multi-tenant database that includes a heirarchical layout.
>> We tried to apply subqueryloads across the board, but tests fail in
>> this one case.
>>
>> class Folder(Base):
>> __tablename__ = 'folder'
>> __table_args__ = (
>> ForeignKeyConstraint(['accountid', 'parent'],
>> ['folder.accountid', 'folder.folderid']),
>> {}
>> )
>> folderid = Column(Integer(), primary_key=True, nullable=False)
>> accountid = Column(Integer(), primary_key=True,
>> autoincrement=False, nullable=False)
>> parent = Column(Integer(), primary_key=False, nullable=False)
>> name = Column(String(length=50), primary_key=False, nullable=False)
>>
>> Folder.child_folders = relation(
>> Folder,
>> backref=backref(
>> 'parent_folder',
>> remote_side=[Folder.accountid, Folder.folderid]
>> )
>> )
>>
>> We have multiple hierarchies with overlapping folderids, so we need
>> any walk to related resources to include that restriction. With normal
>> lazy loads, things work fine. With either eagerloads or subqueryloads,
>> we get too many rows back. It looks like the accountid part of the
>> foreign key doesn't get specified properly in the query:
>> "folder.accountid = folder.accountid".
>>
>> eagerload:
>> SELECT …
>> FROM folder LEFT OUTER JOIN folder AS folder_1 ON
>> folder.accountid = folder.accountid --< lol, wut
>> AND folder.folderid = folder_1.parent
>> WHERE folder.folderid = ? AND folder.accountid = ?
>>
>> subqueryload:
>> SELECT …
>> FROM (SELECT … FROM folder WHERE folder.folderid = ? AND
>> folder.accountid = ?) AS anon_1
>> JOIN folder ON
>> folder.accountid = folder.accountid --< lol, wut
>> AND anon_1.folder_folderid = folder.parent
>> ORDER BY anon_1.folder_accountid, anon_1.folder_folderid
>>
>> I've attached a full sqlite repro script and tried it against 0.6.4,
>> 0.6.8dev, and 0.7b5; same result every time.
>>
>> Thoughts? Thanks!
>>
>> --
>> taa
>> /*eof*/
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalchemy@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 sqlalchemy@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.
>
>
--
taa
/*eof*/
--
You received this message because you ar
RE: [sqlalchemy] Dynamic query
> -Original Message-
> From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
> On Behalf Of Enrico Morelli
> Sent: 06 May 2011 16:20
> To: sqlalchemy
> Subject: [sqlalchemy] Dynamic query
>
> Dear all,
>
> I've a form where people fill one or more fields to search in a db.
> For the moment I solve it using a lot of if statement and a lot of
> different query based on the filled fields. Something like that:
>
> if start_date and end_date and instrument and details and technician:
> c.results =
>
> Session.query(Repairs).filter(and_(Repairs.start_date>=start_date,
> Repairs.end_date<=end_date,
> Repairs.instrument_id==instrument,
> Repairs.details.like('%%%s%%' % details),
> Repairs.technician.like('%%%s%%' % technician)
> )).order_by('start_date').all()
>
> elif start_date and end_date and instrument and details:
> c.results =
>
> Session.query(Repairs).filter(and_(Repairs.start_date>=start_date,
> Repairs.end_date<=end_date,
> Repairs.instrument_id==instrument,
> Repairs.details.like('%%%s%%' %
> details), )).order_by('start_date').all()
>
> and so on for each combination (for 5 fields I have 20 query!). There
> is
> a way to do that in a more dynamic way?
>
You can call Query.filter multiple times. Here's an example:
query = Session.query(Repairs)
if start_date:
query = query.filter(Repairs.start_date >= start_date)
if end_date:
query = query.filter(Repairs.end_date <= end_date)
if instrument:
query = query.filter(Repairs.instrument_id == instrument)
# etc.
results = query.order_by('start_date').all()
Each filter condition will be combined using AND.
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 sqlalchemy@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] eager loading and a self-referential composite primary key
OK this is actually a classic issue, for which no solution has been built yet.
Mostly because its an incredibly obscure case - there's a way to do it in the
internals but its a bit of a killer.
we get the relationship's local side:
(Pdb) self.local_side
OrderedSet([Column('accountid', Integer(), ForeignKey('folder.accountid'),
table=, primary_key=True, nullable=False), Column('folderid',
Integer(), table=, primary_key=True, nullable=False)])
we get the relationship's remote side:
(Pdb) self.remote_side
OrderedSet([Column('accountid', Integer(), ForeignKey('folder.accountid'),
table=, primary_key=True, nullable=False), Column('parent', Integer(),
ForeignKey('folder.folderid'), table=, nullable=False)])
and the primaryjoin:
(Pdb) primaryjoin.__str__()
'folder.accountid = folder.accountid AND folder.folderid = folder.parent'
we tell ClauseAdapter, "change all the columns in the "folder" table, which are
not in the "local side", to come from the alias "folder_1"".
when we chain this along several joins, we also have to say:
"change all the columns in the "folder" table, which are not in the "remote
side", to come from the alias "folder_1"".
the solution has to involve adding annotations to just one side of
"folderid=folderid", then enhancing ClauseAdapter to be able to take cues from
callables that look at annotations, instead of just presence in a set.
so the answer right now is that we don't have this one case covered. Its
ticket 1401 which is two years old, I've added some thoughts there.
the two workarounds are to use joined load with contains_eager(), or the
hand-rolled subqueryload recipe at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading .
On May 6, 2011, at 11:15 AM, Taavi Burns wrote:
> We have a multi-tenant database that includes a heirarchical layout.
> We tried to apply subqueryloads across the board, but tests fail in
> this one case.
>
> class Folder(Base):
>__tablename__ = 'folder'
>__table_args__ = (
>ForeignKeyConstraint(['accountid', 'parent'],
> ['folder.accountid', 'folder.folderid']),
>{}
>)
>folderid = Column(Integer(), primary_key=True, nullable=False)
>accountid = Column(Integer(), primary_key=True,
> autoincrement=False, nullable=False)
>parent = Column(Integer(), primary_key=False, nullable=False)
>name = Column(String(length=50), primary_key=False, nullable=False)
>
> Folder.child_folders = relation(
>Folder,
>backref=backref(
>'parent_folder',
>remote_side=[Folder.accountid, Folder.folderid]
>)
> )
>
> We have multiple hierarchies with overlapping folderids, so we need
> any walk to related resources to include that restriction. With normal
> lazy loads, things work fine. With either eagerloads or subqueryloads,
> we get too many rows back. It looks like the accountid part of the
> foreign key doesn't get specified properly in the query:
> "folder.accountid = folder.accountid".
>
> eagerload:
> SELECT …
> FROM folder LEFT OUTER JOIN folder AS folder_1 ON
>folder.accountid = folder.accountid --< lol, wut
> AND folder.folderid = folder_1.parent
> WHERE folder.folderid = ? AND folder.accountid = ?
>
> subqueryload:
> SELECT …
> FROM (SELECT … FROM folder WHERE folder.folderid = ? AND
> folder.accountid = ?) AS anon_1
> JOIN folder ON
>folder.accountid = folder.accountid --< lol, wut
> AND anon_1.folder_folderid = folder.parent
> ORDER BY anon_1.folder_accountid, anon_1.folder_folderid
>
> I've attached a full sqlite repro script and tried it against 0.6.4,
> 0.6.8dev, and 0.7b5; same result every time.
>
> Thoughts? Thanks!
>
> --
> taa
> /*eof*/
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@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 sqlalchemy@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.
[sqlalchemy] Dynamic query
Dear all,
I've a form where people fill one or more fields to search in a db.
For the moment I solve it using a lot of if statement and a lot of
different query based on the filled fields. Something like that:
if start_date and end_date and instrument and details and technician:
c.results =
Session.query(Repairs).filter(and_(Repairs.start_date>=start_date,
Repairs.end_date<=end_date,
Repairs.instrument_id==instrument,
Repairs.details.like('%%%s%%' % details),
Repairs.technician.like('%%%s%%' % technician)
)).order_by('start_date').all()
elif start_date and end_date and instrument and details:
c.results =
Session.query(Repairs).filter(and_(Repairs.start_date>=start_date,
Repairs.end_date<=end_date,
Repairs.instrument_id==instrument,
Repairs.details.like('%%%s%%' % details),
)).order_by('start_date').all()
and so on for each combination (for 5 fields I have 20 query!). There is
a way to do that in a more dynamic way?
Thanks
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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.
[sqlalchemy] eager loading and a self-referential composite primary key
We have a multi-tenant database that includes a heirarchical layout.
We tried to apply subqueryloads across the board, but tests fail in
this one case.
class Folder(Base):
__tablename__ = 'folder'
__table_args__ = (
ForeignKeyConstraint(['accountid', 'parent'],
['folder.accountid', 'folder.folderid']),
{}
)
folderid = Column(Integer(), primary_key=True, nullable=False)
accountid = Column(Integer(), primary_key=True,
autoincrement=False, nullable=False)
parent = Column(Integer(), primary_key=False, nullable=False)
name = Column(String(length=50), primary_key=False, nullable=False)
Folder.child_folders = relation(
Folder,
backref=backref(
'parent_folder',
remote_side=[Folder.accountid, Folder.folderid]
)
)
We have multiple hierarchies with overlapping folderids, so we need
any walk to related resources to include that restriction. With normal
lazy loads, things work fine. With either eagerloads or subqueryloads,
we get too many rows back. It looks like the accountid part of the
foreign key doesn't get specified properly in the query:
"folder.accountid = folder.accountid".
eagerload:
SELECT …
FROM folder LEFT OUTER JOIN folder AS folder_1 ON
folder.accountid = folder.accountid --< lol, wut
AND folder.folderid = folder_1.parent
WHERE folder.folderid = ? AND folder.accountid = ?
subqueryload:
SELECT …
FROM (SELECT … FROM folder WHERE folder.folderid = ? AND
folder.accountid = ?) AS anon_1
JOIN folder ON
folder.accountid = folder.accountid --< lol, wut
AND anon_1.folder_folderid = folder.parent
ORDER BY anon_1.folder_accountid, anon_1.folder_folderid
I've attached a full sqlite repro script and tried it against 0.6.4,
0.6.8dev, and 0.7b5; same result every time.
Thoughts? Thanks!
--
taa
/*eof*/
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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.
subquery_self_referential_foreign_key.py
Description: Binary data
[sqlalchemy] Sqlalchemy, Postgres and SSL
Hi, how can I set my sqlalchemy engine to connect to a postgres on remote setting the ssl mode as allow? Thanks a lot! Luca -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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.
