[sqlalchemy] Re: Selecting what properties of an object will be loaded
Cool, thanx
On 16 окт, 19:15, Ants Aasma [EMAIL PROTECTED] wrote:
With
session.query(User).options(undefer(User.column_a),
defer(User.column_b), noload(User.column_c))
column_a will be loaded with the query, column_b will be loaded on
access and column_c will be None regardless of the value in the
database.
Ants
On Oct 16, 12:56 pm, Alex K [EMAIL PROTECTED] wrote:
Hi All,
I wonder if there is a way to set what columns of the object will be
used during this particular query, to reduce the query in case if I
need the object, but I don't need all object properties.
is something like this: session.query(User).load('column_a') possible?
session.query([...]) - won't apply, since i need mapped object.
Thanks,
Alex
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: how to print a constructed query with it's parameters?
Thanks for the solution!
But i get the warning for this query:
SELECT address.name AS address_lang, user.name AS user_name
FROM addresses
LEFT OUTER JOIN user ON user.id = address.user_id
Throws
sqlalchemy-0.5.0rc2dev_r5150-py2.5.egg\sqlalchemy\sql\expression.py:
1616: SAWarning: Column 'name' on table 'Select object' being replaced
by another column with the same key. Consider use_labels for select()
statements.
self[column.key] = column
On Oct 15, 5:09 pm, Ants Aasma [EMAIL PROTECTED] wrote:
This seems to come up often. I took a few minutes and threw together a
semi-robust way to do this on 0.5 series. I posted it under usage
recipes in the wiki:http://www.sqlalchemy.org/trac/wiki/DebugInlineParams
It has some flaws, but should be somewhat helpful for debugging.
Ants
On Oct 15, 2:42 pm, alex bodnaru [EMAIL PROTECTED] wrote:
hi friends,
i have a lot to learn from both approaches, but i have sadly appeared too
lazy.
there will be no problem to imagine what the sql will be, only by
looking at the
template statement (with ?'s) and at the list of parameters.
since the template is available to print (probably by __str__), i'd
onlu ask where
the bindparams list is. eventual quotes and escapes may be imagined by
the types of
the columns.
thanks in advance,
alex
On Wed, Oct 15, 2008 at 12:54, [EMAIL PROTECTED] wrote:
i have another approach, which may or may not serve you.
All those '?' are bindparams, and one can eventualy get them printed
with their names - and put names where there aren't.
that's what i needed, i guess replacing names with values would be
easy job.
the code is part of tests/convertertest.py of sqlalchemyAggregator,
http://dev.gafol.net/t/aggregator/
or
http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/agg...
class T_mark( unittest.TestCase):
...
def setUp( self):
self.m = MetaData()
#hack for better visibility
def bp( self,bindparam):
if bindparam.value is not None:
return 'const('+repr(bindparam.value)+')'
k = bindparam.key
if k.startswith( Converter._pfx): #my own bindparams
k = k[ len( Converter._pfx):]
return 'BindParam('+k+')'
self.old_bp = DefaultCompiler._truncate_bindparam
DefaultCompiler._truncate_bindparam = bp
def tearDown( self):
DefaultCompiler._truncate_bindparam = self.old_bp
...
str(expression) then does things like
:const(True) AND :BindParam(oid) = movies.id
tags.tabl = :const('movies') AND tags.oid = :BindParam(oid)
there's some more stuff going on there around compatibility with SA
0.3--0.5, but that's core.
ciao
svil
On Wednesday 15 October 2008 13:33:46 King Simon-NFHD78 wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of alex bodnaru
Sent: 15 October 2008 11:00
To: SQLAlchemy
Subject: [sqlalchemy] how to print a constructed query with
it's parameters?
hello friends,
in order to debug my code, i wish to print my query sql.
it's in the fashion of
query =
table.query().filter(table.code='XL').filter(table.name.like('
%'+q+'%')
with unicode parameters.
by just printing query, i get the select with ? parameters, but
not the additional parameters list, that contains ['XL',
%q-value%]. since it doesn't presently work ok, i'd like to print
the list as well.
thanks in advance,
alex
This question comes up a lot. For example, see
http://groups.google.com/group/sqlalchemy/browse_thread/thread/a060
2ede8 18f55c7
Firstly, if you use echo=True in your call to create_engine, all
SQL will be printed to stdout. The parameters will be displayed as
a list AFTER the SQL is printed.
Eg. (fromhttp://www.sqlalchemy.org/docs/05/ormtutorial.html)
BEGIN
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
['ed', 'Ed Jones', 'edspassword']
SELECT users.id AS users_id, users.name AS users_name,
users.fullname AS users_fullname, users.password AS users_password
FROM users
WHERE users.name = ?
LIMIT 1 OFFSET 0
['ed']
You can control the logging more finely using the logging module -
see
http://www.sqlalchemy.org/docs/05/dbengine.html#dbengine_logging
for more details.
The problem is that SQLAlchemy doesn't ever replace those '?'
characters with the actual parameter values. Those strings are
passed directly to the DBAPI driver, along with the list of
parameter values. It is then up to the DBAPI driver how it passes
the query to the database. (This is why SQLAlchemy is fairly safe
from SQL Injection attacks).
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
[sqlalchemy] Changing loglevels
Hi,
I'm using SQLA 0.5.0rc2 and am running into a problem. I've got a
piece of code like this:
import sys
import logging
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
logging.basicConfig(stream=sys.stderr)
logging.getLogger('sqlalchemy.pool').setLevel(logging.ERROR)
engine = sa.create_engine(.)
logging.getLogger('sqlalchemy.pool').setLevel(logging.INFO)
In the example above I won't see any INFO log messages. However, if
you change the first setLevel from logging.ERROR to logging.INFO you
_do_ get INFO log messages. It looks like you have to specify a
loglevel below logging.WARN *before* creating the engine. Otherwise I
can't get any logging to work.
Maybe it's my lack of understanding the logging module, but does
someone know what is going on?
Regards,
Ids
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Changing loglevels
On Oct 17, 2008, at 9:22 AM, Ids wrote:
Hi,
I'm using SQLA 0.5.0rc2 and am running into a problem. I've got a
piece of code like this:
import sys
import logging
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
logging.basicConfig(stream=sys.stderr)
logging.getLogger('sqlalchemy.pool').setLevel(logging.ERROR)
engine = sa.create_engine(.)
logging.getLogger('sqlalchemy.pool').setLevel(logging.INFO)
In the example above I won't see any INFO log messages. However, if
you change the first setLevel from logging.ERROR to logging.INFO you
_do_ get INFO log messages. It looks like you have to specify a
loglevel below logging.WARN *before* creating the engine. Otherwise I
can't get any logging to work.
Maybe it's my lack of understanding the logging module, but does
someone know what is going on?
the engine checks the log levels when its first constructed, and
sticks with those. this is because the logging.isEnabledFor() call is
apparently very expensive.
to change the engine's log behavior after creation you can use the
echo flag as an alternative. This flag has some of its own opinions
about logging though which may or may not get in the way of what
you're trying to accomplish.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] relation, primaryjoin, uselist=False How to LIMIT to 1?
hi, on Mapper() i have relation() i use primaryjoin, and uselist=False, but i also want to have LIMIT=1 on my join it would be much faster!! SA should add the limit itself when uselist=False --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: relation, primaryjoin, uselist=False How to LIMIT to 1?
On Oct 17, 2008, at 3:32 PM, g00fy wrote:
hi,
on Mapper() i have relation() i use primaryjoin, and uselist=False,
but i also want to have LIMIT=1 on my join
it would be much faster!!
SA should add the limit itself when uselist=False
uselist=False is intended for a one-to-one relation where there is
only one child row referencing the parent row. It's not used to limit
the size of a collection that is otherwise many elements.
Since it seems you're wrestling with a large collection, your best
option may be to use a dynamic loader which allows any query
criterion to be used with an ordinary mapped attribute. This is
described in:
http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relation_largecollections_dynamic
Another option would include setting up your primaryjoin to issue
criterion which matches the exact row you're looking for. Here's an
example:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
metadata =MetaData(engine)
item = Table('item', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', Integer, ForeignKey('parent.id'))
)
parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
)
metadata.create_all()
engine.execute(insert into parent values(1))
engine.execute(insert into parent values(2))
engine.execute(insert into item values(1, 1))
engine.execute(insert into item values(2, 1))
engine.execute(insert into item values(3, 1))
engine.execute(insert into item values(4, 1))
engine.execute(insert into item values(5, 2))
engine.execute(insert into item values(6, 2))
engine.execute(insert into item values(7, 2))
class Parent(object):
pass
class Item(object):
pass
mapper(Parent, parent, properties={
'item':relation(Item, uselist=False,
primaryjoin
=
item
.c
.id
=
=
select
([func
.max
(item
.c
.id
)]).where
(item.c.parent_id==parent.c.id).correlate(parent).as_scalar(),
viewonly=True
)
})
mapper(Item, item)
sess = create_session()
p2 = sess.query(Parent).get(2)
assert p2.item.id == 7
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: relation, primaryjoin, uselist=False How to LIMIT to 1?
I can't make this work with my relation:
mapper(Warehouse, warehouse_table,properties = {
'translation': relation(
WarehouseTranslation,
lazy = False,
uselist = False,
primaryjoin=
and_(
warehouse_table.c.id ==
warehouse_translation_table.c.warehouse_id,
warehouse_translation_table.c.language_id==common.get_language(),
# I want limit 1 here
),
),
rest relations
an aditional subselect will just make it work slower.
On 17 Paź, 21:57, Michael Bayer [EMAIL PROTECTED] wrote:
On Oct 17, 2008, at 3:32 PM, g00fy wrote:
hi,
on Mapper() i have relation() i use primaryjoin, and uselist=False,
but i also want to have LIMIT=1 on my join
it would be much faster!!
SA should add the limit itself when uselist=False
uselist=False is intended for a one-to-one relation where there is
only one child row referencing the parent row. It's not used to limit
the size of a collection that is otherwise many elements.
Since it seems you're wrestling with a large collection, your best
option may be to use a dynamic loader which allows any query
criterion to be used with an ordinary mapped attribute. This is
described in:
http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relatio...
Another option would include setting up your primaryjoin to issue
criterion which matches the exact row you're looking for. Here's an
example:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
metadata =MetaData(engine)
item = Table('item', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', Integer, ForeignKey('parent.id'))
)
parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
)
metadata.create_all()
engine.execute(insert into parent values(1))
engine.execute(insert into parent values(2))
engine.execute(insert into item values(1, 1))
engine.execute(insert into item values(2, 1))
engine.execute(insert into item values(3, 1))
engine.execute(insert into item values(4, 1))
engine.execute(insert into item values(5, 2))
engine.execute(insert into item values(6, 2))
engine.execute(insert into item values(7, 2))
class Parent(object):
pass
class Item(object):
pass
mapper(Parent, parent, properties={
'item':relation(Item, uselist=False,
primaryjoin
=
item
.c
.id
=
=
select
([func
.max
(item
.c
.id
)]).where
(item.c.parent_id==parent.c.id).correlate(parent).as_scalar(),
viewonly=True
)})
mapper(Item, item)
sess = create_session()
p2 = sess.query(Parent).get(2)
assert p2.item.id == 7
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: relation, primaryjoin, uselist=False How to LIMIT to 1?
On Oct 17, 2008, at 4:22 PM, g00fy wrote:
I can't make this work with my relation:
mapper(Warehouse, warehouse_table,properties = {
'translation': relation(
WarehouseTranslation,
lazy = False,
uselist = False,
primaryjoin=
and_(
warehouse_table.c.id ==
warehouse_translation_table.c.warehouse_id,
warehouse_translation_table.c.language_id==common.get_language(),
# I want limit 1 here
),
),
rest relations
an aditional subselect will just make it work slower.
can you see how placing a LIMIT=1 there, especially if you're using
eager loading, will totally break even the most basic operation ?
Such as session.query(Warehouse).all() - you'll only get the first
Warehouse object. The LIMIT idea, without a subquery, can only work
at all in the general case if you're using lazy loading (and
relation() should be configured to work in the genral case). If you
want one query that loads Warehouse objects each with just the first
matching WarehouseTranslation, you have to use a subquery in any case,
even if LIMIT is used. That's just plain SQL.
However, if you really want to load exactly one Warehouse and exactly
one WarehouseTranslation, this is not SQLA default behavior and is
very unique; but its allowed, you just need to be explicit and
construct a query which states that exactly. The example I gave would
use a query like:
p2 = sess.query(Parent).join(Parent.items).\
options(contains_eager(Parent.items))[0]
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Problem with func and string concatenation
On Oct 16, 11:41 am, Adam [EMAIL PROTECTED] wrote:
I'm using Postgres 8.3 and sqlalchemy 0.5.0rc2 - when I'm doing a
select, it seems I can't concatenate a function with another column -
rather that use the || operator, it tries to use the || operator.
Code to reproduce example:
import sqlalchemy as sa
from sqlalchemy import *
from sqlalchemy.sql import *
meta = MetaData()
def get_pg_statement(s):
return s.compile(bind=create_engine('postgres://'))
pt = Table('people', meta,
Column('id', Integer, primary_key=True),
Column('first_name', String(100)),
Column('last_name', String(100))
)
statements = (
select([func.lower(pt.c.first_name)]),
select([pt.c.first_name + pt.c.last_name]),
select([func.lower(pt.c.first_name) + pt.c.first_name +
pt.c.last_name]),
)
print sa.__version__
for s in statements:
print get_pg_statement(s)
Output of sample (line breaks removed):
0.5.0rc2
SELECT lower(people.first_name) AS lower_1 FROM people
SELECT people.first_name || people.last_name AS anon_1 FROM people
SELECT (lower(people.first_name) + people.first_name) ||
people.last_name AS anon_1 FROM people
Note on the third select, the + operator is used rather than ||.
The following error is thrown from PostGres:
ERROR: operator does not exist: text + character varying
LINE 1: SELECT (lower(people.first_name) + people.first_name) ||
peo...
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
When running SELECT (lower(people.first_name) || people.first_name)
|| people.last_name AS anon_1 FROM people the querey executes
properly.
Is there a workaround for this?
send type_=String to each func.lower() call, as in
func.lower(people.first_name, type_=String). This defines the return
type of the function. In some cases this is determined for you (which
we call a generic function) but apparently lower() is not yet on the
list.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How are connections managed?
On Oct 16, 9:39 am, Heston James - Cold Beans [EMAIL PROTECTED] wrote: Afternoon Guys, I have a suspicion that I'm leaving MySQL database connections open when I shouldn't be and I'm trying to understand how they are managed by SQLAlchemy. I currently create an engine instance and bind my session maker too it like this: # Create the engine to the database. engine = create_engine(connection_string, echo=False) # Connect the session. Session = sessionmaker(bind=engine) I then create sessions around my application by using: # Create a new session. session = Session() and once finished with it closing the session like this: # Close the session. session.close() When are new connections established to the database when using this method? And when are they closed again? The only reason I ask is that I've seen a few errors recently on high load instance of my application which struggle to connect to the database, I'm also seeing a few table corruptions and I think they're all related issues from me perhaps creating too many connections. the engine is using a connection pool which by default will keep five connections opened persistently. It also has an overflow of 10 additional connections which are opened on an as-needed basis and closed after usage. At the point of 15 connections in use, the pool throttles additional requests until a connection is available. This is of course all entirely configurable and the docs explain how to do this in detail. Each of your Session instances will procure a connection from the pool when they are first used (such as, issuing a query). They then hold on to that connection persistently, which is considered to be the active transaction, until you rollback(), commit(), or close() the session, or the session is garbage collected. The persistent transaction behavior of session is known in the 0.4 series as transactional and in the 0.5 series as autocommit=False. If you flip this flag, then the Session only pulls connections from the pool for each individual statement execution and/or flush(), and returns it immediately afterwards. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] executemany upate
Is there a way to do executemany() semantic updates? Suppose I have a list of employee id's and I want to do something like: ids = [1, 2, 3, 4, 5, 6] session.execute( tbl_employees.update(tbl_employees.c.id == ids), tbl_employees.c.status=you're fired ) __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
