[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: 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 "", 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 {}
> >
--~--~-~--~~~---~--~~
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 "", 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 {}
>>>
--~--~-~--~~~---~--~~
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: Strange deferred loading behavior...
Thanks for the reply. And you are right, column c2 has a default value defined on it. So, how do l configure SA to not automatically mark "c2" as "deferred"? Sounds like the latest trunk has a different implementation of this. Short of getting the latest 0.4.1, is there a way I can configure my current SA (version 0.4.0)? Thanks again, Ben On Nov 2, 7:21 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Nov 2, 2007, at 12:41 AM, Bruza wrote: > > > > > I am quite baffled by the "deferred loading" behavior on a class > > member in the following code (see below). Looks like if I create an > > object (t1) with some field (c2) having None as value, then after I > > save, commit, and closed the object in a SQLAlchemy session, I cannot > > update the c2 field. It will give me an InvalidRequestError error. > > > However, if I loaded the same object (into t2) from DB (even though > > t2.c2 field and attribute still having None as value), I can modify > > t2.c2 field even after I commit, and close the session that associated > > with t2. So, is this very confusing, or did I miss some of the reasons > > in this behavior? > > what youre not showing me here, that is the most crucial part of > this, is what kind of column "c2" is. from what I can see, it > appears that you have a SQL-side default set up on c2. when the > object is inserted into the DB and you havent set a non-None value on > "c2", the default will generate inline with the INSERT statement; SA > then marks the column as "deferred" so that when next accessed, it > will load the newly generated value. in the second case, you've > loaded the object from the DB so "c2" just populates just like "c1" > and its immediately available without a second SQL statement. > > If you get the latest trunk or wait for version 0.4.1 (possibly this > weekend), the behavior of "deferred" column attributes has changed > such that you can set a new value without forcing a load of the > previous value, so this particular error won't occur (trying to read > "c1" when its deferred and theres no Session will still raise an > error, however). --~--~-~--~~~---~--~~ 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] Strange deferred loading behavior...
I am quite baffled by the "deferred loading" behavior on a class
member in the following code (see below). Looks like if I create an
object (t1) with some field (c2) having None as value, then after I
save, commit, and closed the object in a SQLAlchemy session, I cannot
update the c2 field. It will give me an InvalidRequestError error.
However, if I loaded the same object (into t2) from DB (even though
t2.c2 field and attribute still having None as value), I can modify
t2.c2 field even after I commit, and close the session that associated
with t2. So, is this very confusing, or did I miss some of the reasons
in this behavior?
Ben
sqlalchemy.exceptions.InvalidRequestError: Parent instance is not bound to a Session; deferred load operation of
attribute 'c2' cannot proceed
class Test(Cachable):
def __init__(self, c1, c2=None):
self.c1 = c1
self.c2 = c2
engine = create_engine(url, echo=True)
meta = MetaData()
meta.bind = engine
SessionMaker = sessionmaker(bind=engine, autoflush=True,
transactional=True)
table = Table('t_Test', meta, autoload=True)
mapper(Test, table, order_by=None)
t1 = Test(1)
session = SessionMaker()
session.save(t1)
session.commit()
session.close()
#t1.c2 = 10 # this will cause "InvalidRequestError"
session = SessionMaker()
t2 = session.query(Test).filter_by(c1 = 1).one()
session.commit()
session.close()
t2.c2 = 10 # However, this will NOT cause "InvalidRequestError"
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
