[sqlalchemy] Re: insert to the database
I have fixed the problem
2007/11/9, lur ibargutxi [EMAIL PROTECTED]:
Table definition:
tables['indicators'] = Table('indicators', metadata, autoload=True)
tables['indicatorgroups'] = Table('indicatorgroups', metadata,
autoload=True)
##indicatorgroups table has two columns: idindicatorgroup, name
##indicators table has several columns: idindicator, idindicatorgroup(FK),
...
Mapper:
mappers['indicatorgroups'] = mapper(IndicatorGroups,
tables['indicatorgroups'])
mappers['indicators'] = mapper(Indicators, tables['indicators'],
properties = {
'idindicatorgroup' :
relation(IndicatorGroups),
'idindicatorgroup2' :
relation(IndicatorGroups),
because idindicatorgroup is the column name of the mySQL table and i have to
put another name
},allow_column_override=True)
Once i do this I have create a method in order to insert data in my mySQL
db:
class Insert:
implements(IInsert)
def insert(self, file):
import pdb
db = getUtility(IDatabase, name=' db.query')
session = db.session
sniffer = csv.Sniffer()
dialect = sniffer.sniff(file.read())
file.seek(0)
csvReader = csv.reader(file, dialect=dialect)
csvReader.next()
for i in csvReader:
group, subgroup, indicator, code = i
indg=IndicatorGroups()
indg.name=group
session.save(indg)
ind=Indicators()
ind.code=code
ind.name=indicator
ind.idindicatorgroup=indg
session.save(ind)
session.flush ()
but when execute session.flush() I have this error:
Traceback (innermost last):
Module ZPublisher.Publish, line 115, in publish
Module ZPublisher.mapply, line 88, in mapply
Module ZPublisher.Publish , line 41, in call_object
Module Products.odr.lugabe_db.browser.csv_insert, line 26, in __call__
Module Products.odr.lugabe_db.insert, line 52, in insert
Module sqlalchemy.orm.session, line 681, in flush
Module sqlalchemy.orm.unitofwork, line 216, in flush
Module sqlalchemy.orm.unitofwork, line 432, in execute
Module sqlalchemy.orm.unitofwork, line 1051, in execute
Module sqlalchemy.orm.unitofwork, line 1068, in execute_save_steps
Module sqlalchemy.orm.unitofwork, line 1081, in execute_dependencies
Module sqlalchemy.orm.unitofwork, line 1062, in execute_dependency
Module sqlalchemy.orm.unitofwork, line 1017, in execute
Module sqlalchemy.orm.dependency , line 282, in process_dependencies
Module sqlalchemy.orm.dependency, line 317, in _synchronize
Module sqlalchemy.orm.sync, line 91, in execute
Module sqlalchemy.orm.sync, line 143, in execute
Module sqlalchemy.orm.mapper , line 936, in set_attr_by_column
Module sqlalchemy.orm.util, line 101, in __getitem__
KeyError: Column(u'idindicatorgroup', MSInteger(length=11), ForeignKey(u'
indicatorgroups.idindicatorgroup'), nullable=False,
default=PassiveDefault(u'0'))
And i don't know what I am doing wrong
thanks a lot
2007/11/7, Michael Bayer [EMAIL PROTECTED]:
On Nov 7, 2007, at 5:20 AM, lur ibargutxi wrote:
Does anyone knows what am I doing wrong??
nopewould need to see an entire reproducing test case for that one.
--
Lur Ibargutxi
[EMAIL PROTECTED]
--
Lur Ibargutxi
[EMAIL PROTECTED]
--~--~-~--~~~---~--~~
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] Earn Money Online! No Registration Fees. Guaranteed Payments.
http://www.moneycosmos.com/?r=321740 --~--~-~--~~~---~--~~ 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] SQLAlchemy Book... ?
Hi SQLAlchemists ! Pardon me if this question has already been posted before... But is there any book (or book in preparation) that cover one or more of those subjects : - The SQLAlchemy's History. - The SQLAlchemy's Philosophy. - How to extend and modify the main components of SQLAlchemy. - How to tweak SQLAlchemy for performance, and the pitfalls to avoid in that matter. - Some case studies of SQLAlchemy's utilisation in industry-grade applications. I know there is already a (fantastic!) online documentation for SqlAL that covers many if not all of those subjects, but it would be great to have a book to buy so i could (financially) support your project... --~--~-~--~~~---~--~~ 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] Call or Send SMS to any phone anywhere in the world Free!
Call or Send SMS to any phone anywhere in the world Free! - http://offr.biz/HLGB7321740QUQKUQA --~--~-~--~~~---~--~~ 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] MySQL query parameter binding...
This is driving me nuts... The very very simple SQL query below using
:parameter
always gives me syntax error. However, the same query using constant
'1' then it
works fine. I hope this is not because some stupid mistake I made at
4:00 AM...
Can anybody help?
Thanks,
Ben
c.execute('select * from t_test where c1=:c1', {'c1':1})
2007-11-09 04:53:34,953 INFO sqlalchemy.engine.base.Engine.0x..10
select * from
t_test where c1=:c1
2007-11-09 04:53:34,953 INFO sqlalchemy.engine.base.Engine.0x..10
{'c1': 1}
Traceback (most recent call last):
File stdin, line 1, in ?
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\orm\session.py, line 527, in execute
return self.__connection(engine,
close_with_result=True).execute(clause, par
ams or {}, **kwargs)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 779, in execute
return Connection.executors[c](self, object, multiparams, params)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 789, in _execute_text
self.__execute_raw(context)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 852, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0
], context=context)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 869, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) (1064, You
have an e
rror in your SQL syntax; check the manual that corresponds to your
MySQL server
version for the right syntax to use near ':c1' at line 1) 'select *
from t_test
where c1=:c1' {'c1': 1}
c.execute('select * from t_test where c1=1')
2007-11-09 04:53:44,515 INFO sqlalchemy.engine.base.Engine.0x..10
select * from
t_test where c1=1
2007-11-09 04:53:44,515 INFO sqlalchemy.engine.base.Engine.0x..10 {}
sqlalchemy.engine.base.ResultProxy object at 0x00AEE110
--~--~-~--~~~---~--~~
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] Earn Money Online! No Registration Fees. Guaranteed Payments.
http://www.moneycosmos.com/?r=321740 --~--~-~--~~~---~--~~ 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: mysql exceptions
On Nov 8, 2007, at 10:11 PM, david wrote: Any ideas on how to fix, or what the nature of the issue is, or how to better isolate/debug would be much appreciated. its most lkely concurrent access on a single mysql connection. ensure that you arent sharing a single instance of Session or Connection across two or more threads. --~--~-~--~~~---~--~~ 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: SQLAlchemy Book... ?
On Nov 9, 2007, at 5:56 AM, Palindrom wrote: Hi SQLAlchemists ! Pardon me if this question has already been posted before... But is there any book (or book in preparation) that cover one or more of those subjects : - The SQLAlchemy's History. - The SQLAlchemy's Philosophy. - How to extend and modify the main components of SQLAlchemy. - How to tweak SQLAlchemy for performance, and the pitfalls to avoid in that matter. - Some case studies of SQLAlchemy's utilisation in industry-grade applications. I know there is already a (fantastic!) online documentation for SqlAL that covers many if not all of those subjects, but it would be great to have a book to buy so i could (financially) support your project... there is at least one book deal with Prentice, and possibly a second with OReilly. Myself and Mark Ramm are signed to be the authors of the Prentice book, however the process has been going slowly...the release of version 0.4 changed a lot of the targets we were to be writing about so we've had to rethink a bit. --~--~-~--~~~---~--~~ 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: SQLAlchemy Book... ?
I will definitively buy both these books when they go out ! Thanks again for your toolkit ! On Nov 9, 3:54 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Nov 9, 2007, at 5:56 AM, Palindrom wrote: Hi SQLAlchemists ! Pardon me if this question has already been posted before... But is there any book (or book in preparation) that cover one or more of those subjects : - The SQLAlchemy's History. - The SQLAlchemy's Philosophy. - How to extend and modify the main components of SQLAlchemy. - How to tweak SQLAlchemy for performance, and the pitfalls to avoid in that matter. - Some case studies of SQLAlchemy's utilisation in industry-grade applications. I know there is already a (fantastic!) online documentation for SqlAL that covers many if not all of those subjects, but it would be great to have a book to buy so i could (financially) support your project... there is at least one book deal with Prentice, and possibly a second with OReilly. Myself and Mark Ramm are signed to be the authors of the Prentice book, however the process has been going slowly...the release of version 0.4 changed a lot of the targets we were to be writing about so we've had to rethink a bit. --~--~-~--~~~---~--~~ 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: MySQL query parameter binding...
MysqlDB uses format style bind parameters, i.e. %s. if youd like
SQLAlchemy to convert :c1 to an appropriate bind param for MySQL,
use c.execute(text('select * from t_test where c1=:c1'), {'c1':1}).
On Nov 9, 2007, at 6:56 AM, Bruza wrote:
This is driving me nuts... The very very simple SQL query below using
:parameter
always gives me syntax error. However, the same query using constant
'1' then it
works fine. I hope this is not because some stupid mistake I made at
4:00 AM...
Can anybody help?
Thanks,
Ben
c.execute('select * from t_test where c1=:c1', {'c1':1})
2007-11-09 04:53:34,953 INFO sqlalchemy.engine.base.Engine.0x..10
select * from
t_test where c1=:c1
2007-11-09 04:53:34,953 INFO sqlalchemy.engine.base.Engine.0x..10
{'c1': 1}
Traceback (most recent call last):
File stdin, line 1, in ?
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\orm\session.py, line 527, in execute
return self.__connection(engine,
close_with_result=True).execute(clause, par
ams or {}, **kwargs)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 779, in execute
return Connection.executors[c](self, object, multiparams, params)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 789, in _execute_text
self.__execute_raw(context)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 852, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0
], context=context)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 869, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) (1064, You
have an e
rror in your SQL syntax; check the manual that corresponds to your
MySQL server
version for the right syntax to use near ':c1' at line 1) 'select *
from t_test
where c1=:c1' {'c1': 1}
c.execute('select * from t_test where c1=1')
2007-11-09 04:53:44,515 INFO sqlalchemy.engine.base.Engine.0x..10
select * from
t_test where c1=1
2007-11-09 04:53:44,515 INFO sqlalchemy.engine.base.Engine.0x..10 {}
sqlalchemy.engine.base.ResultProxy object at 0x00AEE110
--~--~-~--~~~---~--~~
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: mixed joined+concrete inheritance broken/r3735
yeah this is the same thing. if you get A's ID column in there instead of C's the problem would not occuri think this is why our own test suite doesn't have these issues. ive made the A-B FK match previous checkin recursive, so it also matches A-C,D,E,, in r3759. On Nov 9, 2007, at 11:30 AM, svilen wrote: On Friday 09 November 2007 18:13:13 Michael Bayer wrote: actually, what would prevent the issue in the first place would be if you use the A_tbl.db_id column in your poly union instead of B_tbl.db_id. in 0.4, mappers always use the base table's column as the primary key column and it actually would not even search for B_tbl.db_id. hmm i'll check if i can do this way (as i also support 0.3.x). in the mean time, one more of the same... On Nov 9, 2007, at 10:12 AM, Michael Bayer wrote: r3756 fixes it. On Nov 9, 2007, at 2:31 AM, [EMAIL PROTECTED] wrote: sorry, here the case (zip) it uses my own polymunion() as the SA.util one cannot handle mixed inheritances. i have a A-B-C test case where B inherits A via joined, and C inherits B via concrete; anbd there are links to each other, e.g. A points to B. it used to work before r3735. now query(A) gives: NoSuchColumnError: Could not locate column in row for column 'A_tbl.db_id' if A-B link is not assigned, it works. t2-ABCD.zip --~--~-~--~~~---~--~~ 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] SQL execution order in the unit of work
Hi. It seems that from SQLAlchemy 0.3.7(?) the unit of work, after a flush, executes the SQL operations in a different order. As an example, assuming this schema CREATE TABLE A ( x INTEGER PRIMARY KEY ); CREATE TABLE B ( y INTEGER PRIMARY KEY REFERENCES A(x) ); in 0.36 I can execute, in a session transaction: a = A(x=10) sess.save(a) b = B(x=10) sess.save(b) sess.flush() This no longer works on 0.3.10, where I need to do a flush after `a` creation. Is this a feature? Is it possible to force the unit of work to execute queries in the right order, in order to avoid an intermediate flush? Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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: SQL execution order in the unit of work
what do your Table objects look like ? a ForeignKey() object must be present on the y column of B in order for the unit of work to know the proper order of operations (or the mappers must have explicit primaryjoin/foreign_keys parameters configured). On Nov 9, 10:42 am, Manlio Perillo [EMAIL PROTECTED] wrote: Hi. It seems that from SQLAlchemy 0.3.7(?) the unit of work, after a flush, executes the SQL operations in a different order. As an example, assuming this schema CREATE TABLE A ( x INTEGER PRIMARY KEY ); CREATE TABLE B ( y INTEGER PRIMARY KEY REFERENCES A(x) ); in 0.36 I can execute, in a session transaction: a = A(x=10) sess.save(a) b = B(x=10) sess.save(b) sess.flush() This no longer works on 0.3.10, where I need to do a flush after `a` creation. Is this a feature? Is it possible to force the unit of work to execute queries in the right order, in order to avoid an intermediate flush? Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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: SQL execution order in the unit of work
Sorry, I forgot to add that the mappers A and B must have a relation()
specified in order for unit of work to determine the order of
operations. this has always been the case in all versions.
illustrated in the attached script.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
metadata = MetaData(engine)
engine.execute(
CREATE TABLE A (
x INTEGER PRIMARY KEY
))
engine.execute(CREATE TABLE B (
y INTEGER PRIMARY KEY REFERENCES A(x)
)
)
tableA = Table('A', metadata, autoload=True)
tableB = Table('B', metadata, autoload=True)
class A(object):
def __init__(self, x):
self.x = x
class B(object):
def __init__(self, y):
self.y = y
mapper(A, tableA, properties={
'b':relation(B)
})
sess = create_session()
mapper(B, tableB)
a = A(x=10)
sess.save(a)
b = B(y=10)
sess.save(b)
sess.flush()
On Nov 9, 2007, at 10:42 AM, Manlio Perillo wrote:
Hi.
It seems that from SQLAlchemy 0.3.7(?) the unit of work, after a
flush,
executes the SQL operations in a different order.
As an example, assuming this schema
CREATE TABLE A (
x INTEGER PRIMARY KEY
);
CREATE TABLE B (
y INTEGER PRIMARY KEY REFERENCES A(x)
);
in 0.36 I can execute, in a session transaction:
a = A(x=10)
sess.save(a)
b = B(x=10)
sess.save(b)
sess.flush()
This no longer works on 0.3.10, where I need to do a flush after `a`
creation.
Is this a feature?
Is it possible to force the unit of work to execute queries in the
right
order, in order to avoid an intermediate flush?
Thanks Manlio Perillo
--~--~-~--~~~---~--~~
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: MySQL query parameter binding...
By using %s, does that mean MySQL does not support binding of
parameter
and will have to pass the entire SQL statement as one text string?
c.execute(select * from t_test where c1=%s % '1234567')
works, but this means the parameter was first substituted into the
query string
(by Python) before sending it to MySQL. I think that is why people
uses :c1
notation in query so that parameters are sent as binary format
separated from
the original query string itself...
On Nov 9, 6:56 am, Michael Bayer [EMAIL PROTECTED] wrote:
MysqlDB uses format style bind parameters, i.e. %s. if youd like
SQLAlchemy to convert :c1 to an appropriate bind param for MySQL,
use c.execute(text('select * from t_test where c1=:c1'), {'c1':1}).
On Nov 9, 2007, at 6:56 AM, Bruza wrote:
This is driving me nuts... The very very simple SQL query below using
:parameter
always gives me syntax error. However, the same query using constant
'1' then it
works fine. I hope this is not because some stupid mistake I made at
4:00 AM...
Can anybody help?
Thanks,
Ben
c.execute('select * from t_test where c1=:c1', {'c1':1})
2007-11-09 04:53:34,953 INFO sqlalchemy.engine.base.Engine.0x..10
select * from
t_test where c1=:c1
2007-11-09 04:53:34,953 INFO sqlalchemy.engine.base.Engine.0x..10
{'c1': 1}
Traceback (most recent call last):
File stdin, line 1, in ?
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\orm\session.py, line 527, in execute
return self.__connection(engine,
close_with_result=True).execute(clause, par
ams or {}, **kwargs)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 779, in execute
return Connection.executors[c](self, object, multiparams, params)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 789, in _execute_text
self.__execute_raw(context)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 852, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0
], context=context)
File C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py, line 869, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) (1064, You
have an e
rror in your SQL syntax; check the manual that corresponds to your
MySQL server
version for the right syntax to use near ':c1' at line 1) 'select *
from t_test
where c1=:c1' {'c1': 1}
c.execute('select * from t_test where c1=1')
2007-11-09 04:53:44,515 INFO sqlalchemy.engine.base.Engine.0x..10
select * from
t_test where c1=1
2007-11-09 04:53:44,515 INFO sqlalchemy.engine.base.Engine.0x..10 {}
sqlalchemy.engine.base.ResultProxy object at 0x00AEE110
--~--~-~--~~~---~--~~
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: MySQL query parameter binding...
Bruza wrote: By using %s, does that mean MySQL does not support binding of parameter and will have to pass the entire SQL statement as one text string? c.execute(select * from t_test where c1=%s % '1234567') That should be a comma separating the bind values, not a % format operator: c.execute(select * from t_test where c1=%s, '1234567') This db-api uses '%s' notation as its placeholder marker. It's equivalent to '?' or ':foo'. There's a whole mess of different possible styles in db-api for specifying binds. If you use sqlalchemy's text(), you can use ':foo' notation cross platform and not have to care about the db-api's bind implementation or typos like the % above. works, but this means the parameter was first substituted into the query string (by Python) before sending it to MySQL. I think that is why people uses :c1 notation in query so that parameters are sent as binary format separated from the original query string itself... --~--~-~--~~~---~--~~ 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: mixed joined+concrete inheritance broken/r3735
yeah this is the same thing. if you get A's ID column in there instead of C's the problem would not occuri think this is why our own test suite doesn't have these issues. ive made the A-B FK match previous checkin recursive, so it also matches A-C,D,E,, in r3759. actually, what would prevent the issue in the first place would be if you use the A_tbl.db_id column in your poly union instead of B_tbl.db_id. in 0.4, mappers always use the base table's column as the primary key column and it actually would not even search for B_tbl.db_id. hmm i'll check if i can do this way (as i also support 0.3.x). in the mean time, one more of the same... and how exactly to guess through the A.join(B).join(C)... that C.dbid is equivalent with A.dbid? ask mapper for equivalence? or should that be additional info given to polumunion maker and coming from inheritance chain? --~--~-~--~~~---~--~~ 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: MySQL query parameter binding...
Thanks for the explanation. I got it now. This is one more example that the S in SQL was never meant to stand for Standard :-)... Ben On Nov 9, 1:39 pm, jason kirtland [EMAIL PROTECTED] wrote: Bruza wrote: By using %s, does that mean MySQL does not support binding of parameter and will have to pass the entire SQL statement as one text string? c.execute(select * from t_test where c1=%s % '1234567') That should be a comma separating the bind values, not a % format operator: c.execute(select * from t_test where c1=%s, '1234567') This db-api uses '%s' notation as its placeholder marker. It's equivalent to '?' or ':foo'. There's a whole mess of different possible styles in db-api for specifying binds. If you use sqlalchemy's text(), you can use ':foo' notation cross platform and not have to care about the db-api's bind implementation or typos like the % above. works, but this means the parameter was first substituted into the query string (by Python) before sending it to MySQL. I think that is why people uses :c1 notation in query so that parameters are sent as binary format separated from the original query string itself... --~--~-~--~~~---~--~~ 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: r3727 / AbstractClauseProcessor problem
one more error in ACP, took me a day to find and separate.
it's very simple and very basic... ClauseAdapter does not work.
--
from sqlalchemy import *
from sqlalchemy.sql.util import ClauseAdapter
m = MetaData()
a=Table( 'a',m,
Column( 'id',Integer, primary_key=True),
Column( 'xxx_id', Integer, ForeignKey( 'a.id', name='adf',
use_alter=True ) )
)
e = (a.c.id == a.c.xxx_id)
print e
b = a.alias()
#print b.c.id == 0
r = ClauseAdapter( b, include= set([ a.c.id ]),
equivalents= { a.c.id: set([ a.c.id]) }
).traverse( e)
print e
# results
#in r3726: (OK)
a.id = a.xxx_id
a_1.id = a.xxx_id
#in r3727 - as well as in r3760:
a.id = a.xxx_id
a.id = a.xxx_id
it does generate proper thing in ClauseAdapter.conv_element()/newcol but
it gets lost in the way
--~--~-~--~~~---~--~~
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: mixed joined+concrete inheritance broken/r3735
On Nov 9, 2007, at 5:10 PM, [EMAIL PROTECTED] wrote: yeah this is the same thing. if you get A's ID column in there instead of C's the problem would not occuri think this is why our own test suite doesn't have these issues. ive made the A-B FK match previous checkin recursive, so it also matches A- C,D,E,, in r3759. actually, what would prevent the issue in the first place would be if you use the A_tbl.db_id column in your poly union instead of B_tbl.db_id. in 0.4, mappers always use the base table's column as the primary key column and it actually would not even search for B_tbl.db_id. hmm i'll check if i can do this way (as i also support 0.3.x). in the mean time, one more of the same... and how exactly to guess through the A.join(B).join(C)... that C.dbid is equivalent with A.dbid? ask mapper for equivalence? or should that be additional info given to polumunion maker and coming from inheritance chain? well the mapper is figuring it out right now by following the foreign key chain from c.id to a.id. so it is possible. but ideally the polyunion maker should have all the info it needs to construct the best query. most of the world isnt mixing concrete inheritance with joined table inheritance so the whole polyunion idea is not too crucial in 0.4. I dont konw of any ORM that can do nearly the mixing of styles that we do in that regard. --~--~-~--~~~---~--~~ 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: SQL execution order in the unit of work
On Nov 9, 2007, at 4:23 PM, Manlio Perillo wrote: Michael Bayer ha scritto: Sorry, I forgot to add that the mappers A and B must have a relation() specified in order for unit of work to determine the order of operations. this has always been the case in all versions. illustrated in the attached script. But I have a piece a code that works with SQLAlchemy 0.3.6 and fails with never versions. The schema is at: http://hg.mperillo.ath.cx/nweb/nauth/file/68ab5b8e71f8/nauth/schema.py and the code is at: http://hg.mperillo.ath.cx/nweb/nauth/file/68ab5b8e71f8/test/test_nauth.py in the test suit named RealmTestCase (in the setUp method) column 248. Well thats a lot of code to read, but I can see that your mappers have no relations set between them. SQLAlchemy has never made any guarantees of insert order among two different tables that have no explicitly defined relation to each other at the mapper level; and in fact the test case I posted earlier still inserts into B before A even on 0.3.6. If your application save()s A and B, which have no relation() to each other, and it requires that A be inserted before B, you are relying on behavior that is currently undefined. If your app runs on 0.3.6 with linux, I bet it would fail on OSX which usually orders dictionaries differently than the same app would on linux. this is not to say it might be an *interesting* feature for mappers to take foreign keys on tables which have no corresponding relation() into account when flushing, although i think it might be somewhat surprising behavior and id be concerned about the feature adding unnecessary complexity. --~--~-~--~~~---~--~~ 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: r3727 / AbstractClauseProcessor problem
On Nov 9, 2007, at 6:49 PM, [EMAIL PROTECTED] wrote:
om sqlalchemy import *
from sqlalchemy.sql.util import ClauseAdapter
m = MetaData()
a=Table( 'a',m,
Column( 'id',Integer, primary_key=True),
Column( 'xxx_id', Integer, ForeignKey( 'a.id', name='adf',
use_alter=True ) )
)
e = (a.c.id == a.c.xxx_id)
print e
b = a.alias()
#print b.c.id == 0
r = ClauseAdapter( b, include= set([ a.c.id ]),
equivalents= { a.c.id: set([ a.c.id]) }
).traverse( e)
print e
# results
#in r3726: (OK)
a.id = a.xxx_id
a_1.id = a.xxx_id
#in r3727 - as well as in r3760:
a.id = a.xxx_id
a.id = a.xxx_id
no, it works, it just clones in all cases:
sql_util.ClauseAdapter( b, include= set([ a.c.id ]),
equivalents= { a.c.id: set([ a.c.id]) }
).traverse( e)
assert str(e) == a_1.id = a.xxx_id
--~--~-~--~~~---~--~~
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: r3727 / AbstractClauseProcessor problem
om sqlalchemy import *
from sqlalchemy.sql.util import ClauseAdapter
m = MetaData()
a=Table( 'a',m,
Column( 'id',Integer, primary_key=True),
Column( 'xxx_id', Integer, ForeignKey( 'a.id', name='adf',
use_alter=True ) )
)
e = (a.c.id == a.c.xxx_id)
print e
b = a.alias()
#print b.c.id == 0
r = ClauseAdapter( b, include= set([ a.c.id ]),
equivalents= { a.c.id: set([ a.c.id]) }
).traverse( e)
print e
# results
#in r3726: (OK)
a.id = a.xxx_id
a_1.id = a.xxx_id
#in r3727 - as well as in r3760:
a.id = a.xxx_id
a.id = a.xxx_id
no, it works, it just clones in all cases:
sql_util.ClauseAdapter( b, include= set([ a.c.id ]),
equivalents= { a.c.id: set([ a.c.id]) }
).traverse( e)
assert str(e) == a_1.id = a.xxx_id
huh? it dies here. r3727 or 3760 all the same, py2.5..., did remove all
*pyc
--~--~-~--~~~---~--~~
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: r3727 / AbstractClauseProcessor problem
On Nov 9, 2007, at 7:26 PM, Michael Bayer wrote:
On Nov 9, 2007, at 6:49 PM, [EMAIL PROTECTED] wrote:
om sqlalchemy import *
from sqlalchemy.sql.util import ClauseAdapter
m = MetaData()
a=Table( 'a',m,
Column( 'id',Integer, primary_key=True),
Column( 'xxx_id', Integer, ForeignKey( 'a.id', name='adf',
use_alter=True ) )
)
e = (a.c.id == a.c.xxx_id)
print e
b = a.alias()
#print b.c.id == 0
r = ClauseAdapter( b, include= set([ a.c.id ]),
equivalents= { a.c.id: set([ a.c.id]) }
).traverse( e)
print e
# results
#in r3726: (OK)
a.id = a.xxx_id
a_1.id = a.xxx_id
#in r3727 - as well as in r3760:
a.id = a.xxx_id
a.id = a.xxx_id
no, it works, it just clones in all cases:
sql_util.ClauseAdapter( b, include= set([ a.c.id ]),
equivalents= { a.c.id: set([ a.c.id]) }
).traverse( e)
assert str(e) == a_1.id = a.xxx_id
oof, try again, notice the assignment:
e = sql_util.ClauseAdapter( b, include= set([ a.c.id ]),
equivalents= { a.c.id: set([ a.c.id]) }
).traverse( e)
print e
assert str(e) == a_1.id = a.xxx_id
--~--~-~--~~~---~--~~
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] stuck on self referrencing mapper thingy
I've tried to find something similar in the SA 0.3 docs but haven't
managed yet. They are dense though so I could easily have missed
something. Apologies if this seems a simple problem! :/
I have a product table, some are allowed to be collections ( ie gift
basket. ) They will have children, which are in turn products. I put in
a boolean attribute so I can control whether they are collections, and I
only need one layer deep, so products can be collections or children,
and not both. Each product can be the child of many collections.
I was hoping I could handle this by having a many to many table of
parent products to children so I could do:
collection.children
Below is what I have tried and failed with:
# many to many of collection-products to products
collections_products_table = Table('collections_products', metadata,
Column('collection_id', Integer, ForeignKey('products.id') ),
Column('product_id', Integer, ForeignKey('products.id') ),
)
#mapper
assign_mapper(session.context, Product, product_table, properties={
'children': relation( Product, secondary=collections_products_table,
lazy=True, )
})
or
assign_mapper(session.context, Product, product_table, properties={
'children': relation( Product, secondary=collections_products_table,
primaryjoin =
collections_products_table.c.collection_id==product_table.c.id,
lazy=True ),
})
Neither of the above work. Tg loads ok but when I try to make a product
I get the following:
ArgumentError: Error determining primary and/or secondary join for
relationship 'children' between mappers 'Mapper|Product|products' and
'Mapper|Product|products'. If the underlying error cannot be corrected,
you should specify the 'primaryjoin' (and 'secondaryjoin', if there is
an association table present) keyword arguments to the relation()
function (or for backrefs, by specifying the backref using the backref()
function with keyword arguments) to explicitly specify the join
conditions. Nested error is Cant determine join between 'products' and
'collections_products'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
I'm in over my head here, so if anyone has the time to comment on the
above that would be luverly.
Thanks
Iain
--~--~-~--~~~---~--~~
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: mysql exceptions
Well, Michael, what you say sounds reasonable. However, I think
something more strange is going on (unless I am making a very stupid
mistake, which is entirely likely)
I am using the current pylons framework. I have tried to follow their
recommendations to the letter. All of my queries are in a single file,
and all use the single Session global variable created by
scoped_session() for the session.
I have created test cases (inside the framework) to isolate what is
happening. I then exercise them by posting scads of http requests from
multiple processes to the paster server. Currently, I am using a test
run of 10 concurrent processes each sending 1000 requests.
The errors seem to be specific to a *table*.
Here's an example - 2 tables author_t, and keyword_t
author_t = Table('author', meta,
Column('authorkey', Unicode(35), primary_key=True),
Column('lastname', Unicode),
Column('firstname', Unicode),
)
keyword_t = Table('keyword', meta,
Column('word', Unicode(20), primary_key = True),
Column('stem', Unicode(20)),
Column('freq', Integer),
)
1. when I do the following, *never* have seen the error (keyword set
to a unicode string):
s1 = select([keyword_t.c.word,],
keyword_t.c.word == keyword)
keyword = Session.execute(s1).fetchone()
2. However, when I do the following, I *always* find errors in a run
(authorkey set to a u string):
s1 = select([author_t.c.authorkey,],
author_t.c.authorkey == authorkey)
author = Session.execute(s1).fetchone()
So I am totally confused. What I am doing should be very simple. The
tables are simple. The relations are simple.
Can you shed any light?
Thanks a million for your help.
David
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
