[sqlalchemy] How do you execute pure SQL against placeholders?
This would seem to be an easy obvious task, but searching both this newsgroup and the SQLAlchemy.org website do not turn up any docs on the process. I want to get a result proxy for executing this SQL, in the processing binding the variable dayno to searchSchedule: def load(sa, dayno): take SQLALCHEMY_OBJECT and a day_number and return a RESULT_PROXY consisting of all records satisfying query sql = SELECT * FROM UserSearches WHERE searchSchedule = ? === call to create my SA object === from sqlalchemy import * class sa(object): def __init__(self, ip=4.12.18.4, db=DATA, engine_echo=True): url = 'mssql://perl2:[EMAIL PROTECTED]:1433/%s' % (ip, db) print Connection url, url self.engine = create_engine(url) self.engine.echo=engine_echo self.metadata = BoundMetaData(self.engine) --~--~-~--~~~---~--~~ 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] prettyprint sql
i have made a hack to make the sql/echo look somewhat better - level-indented + more line-separations. it auto-applies itself over the sqlachemy at runtime. usage: just import sahack4src e.g. SELECT pu_human.address_id AS pu_human_address_id, FROM (SELECT person.address_id AS address_id, FROM human JOIN person ON person.db_id = human.db_id UNION ALL SELECT CAST(NULL AS INTEGER) AS address_id, FROM (SELECT human.age AS age, human.name AS name, FROM human WHERE human.atype = ?) AS bz4human) AS pu_human WHERE pu_human.age ? AND pu_human.friend_id = pu_human.db_id ORDER BY pu_human.oid becomes: SELECT pu_human.address_id AS pu_human_address_id, FROM ( SELECT person.address_id AS address_id, FROM human JOIN person ON person.db_id = human.db_id UNION ALL SELECT CAST(NULL AS INTEGER) AS address_id, FROM ( SELECT human.age AS age, human.name AS name, FROM human WHERE human.atype = ? ) AS bz4human ) AS pu_human WHERE pu_human.age ? AND pu_human.friend_id = pu_human.db_id ORDER BY pu_human.oid === Mike, are u interested in making this inside sqlalchemy? it's something like 5-10 lines changed. i can prepare a patch.. svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- hacksrc.py Description: application/python sahack4echo.py Description: application/python
[sqlalchemy] Joins with 3 tables to order results?
I've got a 'system' table which has a foreign key client_id to a 'client' table, and that has a FK vendor_id to a 'vendor' table. Currently I query and order by client_id and system attribute like:: systems = self.session.query(System).select(or_(System.c.lastseen == None, System.c.lastseen lately), order_by=[System.c.client_id,System.c.lastseen]) then using SQLAlchemy magic I can render the system.client.vendor.name and system.client.name. I want to do a query on 'system', get and order by vendor.name and client.name in that order. But I don't know how to construct a join so I can order this way. Any clues? Thanks. --~--~-~--~~~---~--~~ 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: prettyprint sql
are we talking about adding a couple of \ns around ansicompiler ? sure. on the other hand, if talking about it applied as an optional monkeypatch thing, id prefer it as a logging.Formatter. On Mar 1, 12:57 pm, svilen [EMAIL PROTECTED] wrote: i have made a hack to make the sql/echo look somewhat better - level-indented + more line-separations. it auto-applies itself over the sqlachemy at runtime. usage: just import sahack4src e.g. SELECT pu_human.address_id AS pu_human_address_id, FROM (SELECT person.address_id AS address_id, FROM human JOIN person ON person.db_id = human.db_id UNION ALL SELECT CAST(NULL AS INTEGER) AS address_id, FROM (SELECT human.age AS age, human.name AS name, FROM human WHERE human.atype = ?) AS bz4human) AS pu_human WHERE pu_human.age ? AND pu_human.friend_id = pu_human.db_id ORDER BY pu_human.oid becomes: SELECT pu_human.address_id AS pu_human_address_id, FROM ( SELECT person.address_id AS address_id, FROM human JOIN person ON person.db_id = human.db_id UNION ALL SELECT CAST(NULL AS INTEGER) AS address_id, FROM ( SELECT human.age AS age, human.name AS name, FROM human WHERE human.atype = ? ) AS bz4human ) AS pu_human WHERE pu_human.age ? AND pu_human.friend_id = pu_human.db_id ORDER BY pu_human.oid === Mike, are u interested in making this inside sqlalchemy? it's something like 5-10 lines changed. i can prepare a patch.. svil hacksrc.py 1KDownload sahack4echo.py 1KDownload --~--~-~--~~~---~--~~ 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: Column default that is aware of whole record to be inserted
use a python function as your column default ? http://www.sqlalchemy.org/docs/metadata.myt#metadata_defaults_oninsert On Feb 28, 6:00 pm, askel [EMAIL PROTECTED] wrote: Hello everyone, Is there a way to access current record from DefaultGenerator? I have balances table that collect information on daily basis. Whenever a new record inserted in that table it has to inherit some information from previous day record. Of course, I can implement that using SQL- server capabilities but I'd rather not go that way. So basically, I need an access to a record to be inserted when generator is asked for a value. Thanks --~--~-~--~~~---~--~~ 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 do you execute pure SQL against placeholders?
text(select * from usersearches where searchschedule=:dayno, engine=myengine).execute(dayno=5) ? that would be here: http://www.sqlalchemy.org/docs/sqlconstruction.myt#sql_textual_textual_binds On Mar 1, 10:20 am, metaperl [EMAIL PROTECTED] wrote: This would seem to be an easy obvious task, but searching both this newsgroup and the SQLAlchemy.org website do not turn up any docs on the process. I want to get a result proxy for executing this SQL, in the processing binding the variable dayno to searchSchedule: def load(sa, dayno): take SQLALCHEMY_OBJECT and a day_number and return a RESULT_PROXY consisting of all records satisfying query sql = SELECT * FROM UserSearches WHERE searchSchedule = ? === call to create my SA object === from sqlalchemy import * class sa(object): def __init__(self, ip=4.12.18.4, db=DATA, engine_echo=True): url = 'mssql://perl2:[EMAIL PROTECTED]:1433/%s' % (ip, db) print Connection url, url self.engine = create_engine(url) self.engine.echo=engine_echo self.metadata = BoundMetaData(self.engine) --~--~-~--~~~---~--~~ 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: contains_eager is somehow not loading all the instances.
yeah its a bug, its all fixed (several issues with text columns) in 2368. --~--~-~--~~~---~--~~ 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: contains_eager is somehow not loading all the instances.
Thanks! On Mar 1, 1:31 pm, Michael Bayer [EMAIL PROTECTED] wrote: yeah its a bug, its all fixed (several issues with text columns) in 2368. --~--~-~--~~~---~--~~ 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: Column default that is aware of whole record to be inserted
Michael,
Thanks for reply but I know about that. The question is how do I
access a record that requires default value from withing that
function. Something like the following:
def prev_balance():
new_bal = get_record_that_is_being_inserted() # === that is what
I'm looking for
return
build_subquery_to_retrieve_previous_balance_value(new_bal.account,
new_bal.date) # === I know how to do that part
balances = Table('balances', meta,
Column('account', Integer, ForeignKey('accounts.id'),
nullable=False, primary_key=True),
Column('date', Date, nullable=False, primary_key=True),
Column('value', Numeric(12,2), nullable=False,default=prev_balance),
)
On Mar 1, 4:30 pm, Michael Bayer [EMAIL PROTECTED] wrote:
use a python function as your column default ?
http://www.sqlalchemy.org/docs/metadata.myt#metadata_defaults_oninsert
On Feb 28, 6:00 pm, askel [EMAIL PROTECTED] wrote:
Hello everyone,
Is there a way to access current record from DefaultGenerator? I have
balances table that collect information on daily basis. Whenever a new
record inserted in that table it has to inherit some information
from previous day record. Of course, I can implement that using SQL-
server capabilities but I'd rather not go that way.
So basically, I need an access to a record to be inserted when
generator is asked for a value.
Thanks
--~--~-~--~~~---~--~~
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: Column default that is aware of whole record to be inserted
people ask for this function occasionally, what i dont like about it
is that it would greatly complicate things just to produce
functionality that isnt really needed. for example, if you want one
of the columns in your INSERT to be driven off the values in the other
columns, why not just prepare that data *outside* of the call to
sqlalchemy's insert() ?
On Mar 1, 6:46 pm, askel [EMAIL PROTECTED] wrote:
Michael,
Thanks for reply but I know about that. The question is how do I
access a record that requires default value from withing that
function. Something like the following:
def prev_balance():
new_bal = get_record_that_is_being_inserted() # === that is what
I'm looking for
return
build_subquery_to_retrieve_previous_balance_value(new_bal.account,
new_bal.date) # === I know how to do that part
balances = Table('balances', meta,
Column('account', Integer, ForeignKey('accounts.id'),
nullable=False, primary_key=True),
Column('date', Date, nullable=False, primary_key=True),
Column('value', Numeric(12,2), nullable=False,default=prev_balance),
)
On Mar 1, 4:30 pm, Michael Bayer [EMAIL PROTECTED] wrote:
use a python function as your column default ?
http://www.sqlalchemy.org/docs/metadata.myt#metadata_defaults_oninsert
On Feb 28, 6:00 pm, askel [EMAIL PROTECTED] wrote:
Hello everyone,
Is there a way to access current record from DefaultGenerator? I have
balances table that collect information on daily basis. Whenever a new
record inserted in that table it has to inherit some information
from previous day record. Of course, I can implement that using SQL-
server capabilities but I'd rather not go that way.
So basically, I need an access to a record to be inserted when
generator is asked for a value.
Thanks
--~--~-~--~~~---~--~~
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: Column default that is aware of whole record to be inserted
Michael, On Mar 1, 8:04 pm, Michael Bayer [EMAIL PROTECTED] wrote: people ask for this function occasionally, what i dont like about it is that it would greatly complicate things just to produce functionality that isnt really needed. It wasn't my intention to ask anybody to implement such a functionality. I thought that I simply failed to find it. for example, if you want one of the columns in your INSERT to be driven off the values in the other columns, why not just prepare that data *outside* of the call to sqlalchemy's insert() ? In my case data cannot be prepared outside, well at least not unless I begin transaction, fetch data I need then insert new record and commit transaction. But that doesn't look quite right for me since in plain SQL it would be just one INSERT statement with SELECT. I guess it's just a price I have to pay for using ORM and not dealing with SQL directly. Anyway, thanks for answering. --~--~-~--~~~---~--~~ 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: contains_eager is somehow not loading all the instances.
yeah its a bug, its all fixed (several issues with text columns) in
2368.
On Feb 28, 2007, at 4:46 PM, Dennis wrote:
On Feb 28, 12:30 pm, Michael Bayer [EMAIL PROTECTED] wrote:
you have to show me what youre doing since from my understanding,
youre using literal text, and nothing should get added to that.
Here is a quicky Test:
from sqlalchemy import *
e=create_engine('sqlite://memory')
ts=Table('ts',e,
Column ( 'id',Integer,primary_key=True),
Column ( 'dat',Integer,nullable=False))
ts.create()
sel=select( [ case when id 5 then 'ok' else 'tolow' end as
test ] , from_obj=[ts], use_labels=True )
print str(sel)
___
And here is the output I get:
SELECT case when id 5 then 'ok' else 'tolow' end as test AS
casewhenid5thenokelsetolowendastest
FROM t
Am I doing something wrong with the literal text block? Is there
another way to specify one besides simply passing in a string.
Thanks
Dennis
--~--~-~--~~~---~--~~
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: Column default that is aware of whole record to be inserted
oh youd want insert from a select ? yah we have never added support for that construct directly but you can issue it as text for now... im looking at the default code and it wouldnt be too hard to give it access to the execution context and make the parameters available and all that, it probably should be that way anyway since there is an ExecutionContext object. id want to refactor some things. On Mar 1, 8:07 pm, askel [EMAIL PROTECTED] wrote: Michael, On Mar 1, 8:04 pm, Michael Bayer [EMAIL PROTECTED] wrote: people ask for this function occasionally, what i dont like about it is that it would greatly complicate things just to produce functionality that isnt really needed. It wasn't my intention to ask anybody to implement such a functionality. I thought that I simply failed to find it. for example, if you want one of the columns in your INSERT to be driven off the values in the other columns, why not just prepare that data *outside* of the call to sqlalchemy's insert() ? In my case data cannot be prepared outside, well at least not unless I begin transaction, fetch data I need then insert new record and commit transaction. But that doesn't look quite right for me since in plain SQL it would be just one INSERT statement with SELECT. I guess it's just a price I have to pay for using ORM and not dealing with SQL directly. Anyway, thanks for answering. --~--~-~--~~~---~--~~ 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 do you execute pure SQL against placeholders?
text(select * from usersearches where searchschedule=:dayno, engine=myengine).execute(dayno=5) ? that would be here: http://www.sqlalchemy.org/docs/ sqlconstruction.myt#sql_textual_textual_binds On Mar 1, 2007, at 10:20 AM, metaperl wrote: This would seem to be an easy obvious task, but searching both this newsgroup and the SQLAlchemy.org website do not turn up any docs on the process. I want to get a result proxy for executing this SQL, in the processing binding the variable dayno to searchSchedule: def load(sa, dayno): take SQLALCHEMY_OBJECT and a day_number and return a RESULT_PROXY consisting of all records satisfying query sql = SELECT * FROM UserSearches WHERE searchSchedule = ? === call to create my SA object === from sqlalchemy import * class sa(object): def __init__(self, ip=4.12.18.4, db=DATA, engine_echo=True): url = 'mssql://perl2:[EMAIL PROTECTED]:1433/%s' % (ip, db) print Connection url, url self.engine = create_engine(url) self.engine.echo=engine_echo self.metadata = BoundMetaData(self.engine) --~--~-~--~~~---~--~~ 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: Column default that is aware of whole record to be inserted
use a python function as your column default ? http://www.sqlalchemy.org/docs/metadata.myt#metadata_defaults_oninsert On Feb 28, 2007, at 6:00 PM, askel wrote: Hello everyone, Is there a way to access current record from DefaultGenerator? I have balances table that collect information on daily basis. Whenever a new record inserted in that table it has to inherit some information from previous day record. Of course, I can implement that using SQL- server capabilities but I'd rather not go that way. So basically, I need an access to a record to be inserted when generator is asked for a value. Thanks --~--~-~--~~~---~--~~ 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: query on self-referencing polymorphic mapper
On Mar 1, 2007, at 12:44 PM, svilen wrote: hi. i have the case of polymorphic mapper referencing itself (or other of it's sub-class objects), and i want to query/filter on a value of the referenced object. e.g. all people who have friends of age 25. these are highly highly complex queries and im not sure if the Query SQL compiler is really appropriate for this; you probably want to go with rolling your own select statements for this. that or perform separate queries for each subclass and then combine the results in Python (this might be something SA does automatically in the future, as its the only way Hibernate does polymorphic loads). btw. in the polymorphic_union(), is that forced aliasing on any select() entries really needed? yes. aliasing serves the purpose of ensuring that all column names are unique (which for these queries is essential), and also sqlite requires aliases on all columns in a subquery when selecting from that subquery. --~--~-~--~~~---~--~~ 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] SQA failing on table creation
Here is the dump:
[EMAIL PROTECTED] percious]$ tg-admin sql create
Creating tables at mysql://percious:[EMAIL PROTECTED]:3306/percious
Traceback (most recent call last):
File /home2/percious/bin/tg-admin, line 7, in ?
sys.exit(
File /home2/percious/lib/python2.4/TurboGears-1.0-py2.4.egg/
turbogears/command/base.py, line 389, in main
command.run()
File /home2/percious/lib/python2.4/TurboGears-1.0-py2.4.egg/
turbogears/command/base.py, line 115, in run
sacommand(command, sys.argv)
File string, line 5, in sacommand
File /home2/percious/lib/python2.4/TurboGears-1.0-py2.4.egg/
turbogears/command/base.py, line 70, in sacreate
metadata.create_all()
File build/bdist.linux-i686/egg/sqlalchemy/schema.py, line 891, in
create_all
File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line
434, in create
File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line
458, in _run_visitor
File build/bdist.linux-i686/egg/sqlalchemy/schema.py, line 911, in
accept_schema_visitor
File build/bdist.linux-i686/egg/sqlalchemy/ansisql.py, line 682,
in visit_metadata
File build/bdist.linux-i686/egg/sqlalchemy/schema.py, line 266, in
accept_schema_visitor
File build/bdist.linux-i686/egg/sqlalchemy/ansisql.py, line 717,
in visit_table
File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line
854, in execute
File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line
386, in proxy
File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line
350, in _execute_raw
File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line
369, in _execute
sqlalchemy.exceptions.SQLError: (OperationalError) (1071, 'Specified
key was too long; max key length is 999 bytes') '\nCREATE TABLE
`Album` (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tname
VARCHAR(128), \n\tdirectory VARCHAR(512), \n\t`imageOrder`
VARCHAR(512), \n\t`coverImage` INTEGER, \n\tPRIMARY KEY (id), \n\t
UNIQUE (directory), \n\t FOREIGN KEY(`coverImage`) REFERENCES `Image`
(id)\n)\n\n' ()
Here is the table code:
AlbumTable = Table('Album', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(128)),
Column('directory', Unicode(512), unique=True),
Column('imageOrder', Unicode(512)),
Column('coverImage', Integer, ForeignKey('Image.id')),
)
Mysql version 5.0.27
TIA
-chris
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
