[sqlalchemy] Re: MySQL has gone away (again)
On Thu, Jun 4, 2009 at 4:20 PM, Michael Bayermike...@zzzcomputing.com wrote: the connection went from good to dead within a few seconds (assuming SQL was successfully emitted on the previous checkout). Your database was restarted or a network failure occurred. There is no other option? I'm pretty sure that DB was not restarted, network failure is of course possible but still.. (this is the same LAN). But, assuming this is external problem - is there any way to tell SQLAlchemy to try another connection for the same request (instead of returning HTTP 500 for user), or maybe other pooling strategy or even something else? Thanks! Kamil Kamil Gorlo wrote: Hi, I know this problem shows on group from time to time, but suggested solutions does not work for me (pool_recycle). Here is my situation: 1. I am using SQLAlchemy 0.4.7p1-2 from Pylons 0.9.6.2-2 on Debian Lenny (xen-amd64) 2. From time to time I see in logs: MySQL has gone away and this is reason why some of HTTP requests made to my Pylons app fail. 3. My settings in configuration file are as follows: ... sqlalchemy.url = mysql://USER:p...@host:3306/DBNAME?charset=utf8 sqlalchemy.pool_recycle = 60 #(wait_timeout on MySQL side is default, which is 8 hours AFAIK) sqlalchemy.convert_unicode = True ... 4. I have logging enabled, and here is what I see when request fail (please look at connection identified as at 2238cd0, its lifetime is one minute but it looks that in very last moment (the same second which it should be destroyed) this connection is returned from pool, possibly because of some http request - unfortunately after that we can see that error occurs). Also what is surprise for me why is whole pool recreating after this? P.S. Third column is thread id. 16:19:07,621 INFO 1115703632 [sqlalchemy.pool.QueuePool.0x..10] Closing connection _mysql.connection open to 'DB_HOST' at 2262480 16:19:07,624 INFO 1115703632 [sqlalchemy.pool.QueuePool.0x..10] Created new connection _mysql.connection open to 'DB_HOST' at 2238cd0 16:19:07,624 INFO 1115703632 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 2238cd0 checked out from pool 16:19:07,740 INFO 1115703632 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 2238cd0 being returned to pool 16:20:02,291 INFO 1107310928 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 2364540 checked out from pool 16:20:02,413 INFO 1107310928 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 2364540 being returned to pool 16:20:03,477 INFO 1132489040 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 222afb0 checked out from pool 16:20:03,516 INFO 1132489040 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 222afb0 being returned to pool 16:20:07,573 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 2238cd0 checked out from pool 16:20:07,574 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Invalidate connection _mysql.connection open to 'DB_HOST' at 2238cd0 (reason: OperationalError:(2006, 'MySQL server has gone away')) 16:20:07,574 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Closing connection _mysql.connection open to 'DB_HOST' at 2238cd0 16:20:07,574 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Connection None being returned to pool 16:20:07,575 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Closing connection _mysql.connection open to 'DB_HOST' at 2364540 16:20:07,575 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Closing connection _mysql.connection open to 'DB_HOST' at 222afb0 16:20:07,575 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Pool disposed. Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0 16:20:07,575 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Pool recreating Any ideas? Thanks for your help! Cheers, -- Kamil Gorlo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy for c++
2009/6/8 Jarrod Chesney jarrod.ches...@gmail.com Hi All Does anyone knows where i can find information about using SQLAlchemy from c++ or even if its possible. SQLAlchemy is written in pure python. So, you question should be: * How to embed the Python interpreter in my C++ program? or * How to communicate with services written in Python in my C++ program? -- XUE Can --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] insert statment
Hello ,
I am trying to insert in the table using two ways in the values which
i show below
engine = sqlalchemy.create_engine(to poastgres)
metadata = MetaData()
t1 = Table('master',metadata) # assume master has 2 feilds name , city
t1.insert({'name':'','city':'bank'})
engine,execute(t1)
This works for fine me.
If i make values like this
tt = [('asasas','belhium'),('sdsd':'sdsds')]
t1.insert(values=tt)
i get error
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) syntax
error at or near )
LINE 1: INSERT INTO abc () VALUES ()
^
'INSERT INTO abc () VALUES ()' {}
Can any one guide whts wrong... i jnow value is not being passed so
anyother 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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Formatting return of 'query'
Hello, Can a query be written to return required columns along with some delimiter? Example : #User is a class, it has firstname, lastname, age, password q = session.query(User.firstname,User.lastname,User.age,User.password).all() #this would return [(fname,lname,22,pwd),] How can I modify this query to return something like : [(fname~lname~22~pwd)...] with '~' being preferred delimiter. I would like to know if I can return something like above directly from the query itself. Thanks, Harish --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Engine.execute interpreting all parameters as string data
I'm new to SQLAlchemy (and Python in general) and have been struggling
to get a basic execution wrapper working - the goal is to use of raw
batches of parameterized SQL - no ORM.
The engine initializes and I can run queries through it without a
problem, however the generated SQL from the execute statement seems to
interpret everything as string data.
The line:
engine.execute(text(select result = dateadd(day, :days, getdate())),
dict(days = 7))
...yields the following error:
ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL
Server]Argument data type varchar is invalid for argument 2 of dateadd
function. (8116) (SQLPrepare)') 'select result = dateadd(day, ?,
getdate())' [7]
Am I overlooking something simple here? Is there a way to have the
engine substitute parameters as per the Python datatype?
Thanks for any feedback!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Formatting return of 'query'
Harish Vishwanath ha scritto: cut How can I modify this query to return something like : [(fname~lname~22~pwd)...] with '~' being preferred delimiter. SA return a list or record, what exactly you are searching for? a string or something else I would like to know if I can return something like above directly from the query itself. something like ? [ '~'.join(x) for x in qry.fetchall() ] Glauco --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Engine.execute interpreting all parameters as string data
Malherbe wrote:
I'm new to SQLAlchemy (and Python in general) and have been struggling
to get a basic execution wrapper working - the goal is to use of raw
batches of parameterized SQL - no ORM.
The engine initializes and I can run queries through it without a
problem, however the generated SQL from the execute statement seems to
interpret everything as string data.
The line:
engine.execute(text(select result = dateadd(day, :days, getdate())),
dict(days = 7))
...yields the following error:
ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL
Server]Argument data type varchar is invalid for argument 2 of dateadd
function. (8116) (SQLPrepare)') 'select result = dateadd(day, ?,
getdate())' [7]
Am I overlooking something simple here? Is there a way to have the
engine substitute parameters as per the Python datatype?
parameters work by passing them to the execute() or executemany() method
of the underlying DBAPI cursor. SQLAlchemy usually does not affect these
values except in specific cases, such as unicode strings that need to be
encoded to utf-8 or dates that needs to be turned into strings. For plain
python types like ints, floats, etc. the DBAPI is usually able to handle
these straight through and SQLAlchemy doesn't affect them. Unfortunately,
you're using FreeTDS, which is the most buggy and difficult DBAPI out
there (and SQL server the most finnicky database), so the issue probably
lies in some combination of SQL Server and FreeTDS not liking what is
being sent. Also the format of the SQL you're using select x = foo()
seems unusual but I'm not deeply familiar with the quirks of SQL server.
you might want to try a raw DBAPI script both with FreeTDS and perhaps
without, using Pyodbc, to narrow the issue down to FreeTDS. There also
might be a CAST() syntax you can add to your statement - something along
the lines of dateadd(day, CAST ? AS INT, getdate()).
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MySQL has gone away (again)
Kamil Gorlo wrote: On Thu, Jun 4, 2009 at 4:20 PM, Michael Bayermike...@zzzcomputing.com wrote: the connection went from good to dead within a few seconds (assuming SQL was successfully emitted on the previous checkout). Your database was restarted or a network failure occurred. There is no other option? I'm pretty sure that DB was not restarted, network failure is of course possible but still.. (this is the same LAN). is there some condition that reproduces the issue ? does a high degree of concurrency increase the likelihood of a failure ? But, assuming this is external problem - is there any way to tell SQLAlchemy to try another connection for the same request (instead of returning HTTP 500 for user), or maybe other pooling strategy or even something else? SQLA doesn't do this. In most situations a connection dropped means the contents of a transaction with any number of statements has been lost - so you'd have to write your application such that the full steps required to rerun your transaction can be replicated. But I'd go for isolating why you're getting connection drops over that solution. Thanks! Kamil Kamil Gorlo wrote: Hi, I know this problem shows on group from time to time, but suggested solutions does not work for me (pool_recycle). Here is my situation: 1. I am using SQLAlchemy 0.4.7p1-2 from Pylons 0.9.6.2-2 on Debian Lenny (xen-amd64) 2. From time to time I see in logs: MySQL has gone away and this is reason why some of HTTP requests made to my Pylons app fail. 3. My settings in configuration file are as follows: ... sqlalchemy.url = mysql://USER:p...@host:3306/DBNAME?charset=utf8 sqlalchemy.pool_recycle = 60 #(wait_timeout on MySQL side is default, which is 8 hours AFAIK) sqlalchemy.convert_unicode = True ... 4. I have logging enabled, and here is what I see when request fail (please look at connection identified as at 2238cd0, its lifetime is one minute but it looks that in very last moment (the same second which it should be destroyed) this connection is returned from pool, possibly because of some http request - unfortunately after that we can see that error occurs). Also what is surprise for me why is whole pool recreating after this? P.S. Third column is thread id. 16:19:07,621 INFO 1115703632 [sqlalchemy.pool.QueuePool.0x..10] Closing connection _mysql.connection open to 'DB_HOST' at 2262480 16:19:07,624 INFO 1115703632 [sqlalchemy.pool.QueuePool.0x..10] Created new connection _mysql.connection open to 'DB_HOST' at 2238cd0 16:19:07,624 INFO 1115703632 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 2238cd0 checked out from pool 16:19:07,740 INFO 1115703632 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 2238cd0 being returned to pool 16:20:02,291 INFO 1107310928 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 2364540 checked out from pool 16:20:02,413 INFO 1107310928 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 2364540 being returned to pool 16:20:03,477 INFO 1132489040 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 222afb0 checked out from pool 16:20:03,516 INFO 1132489040 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 222afb0 being returned to pool 16:20:07,573 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Connection _mysql.connection open to 'DB_HOST' at 2238cd0 checked out from pool 16:20:07,574 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Invalidate connection _mysql.connection open to 'DB_HOST' at 2238cd0 (reason: OperationalError:(2006, 'MySQL server has gone away')) 16:20:07,574 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Closing connection _mysql.connection open to 'DB_HOST' at 2238cd0 16:20:07,574 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Connection None being returned to pool 16:20:07,575 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Closing connection _mysql.connection open to 'DB_HOST' at 2364540 16:20:07,575 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Closing connection _mysql.connection open to 'DB_HOST' at 222afb0 16:20:07,575 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Pool disposed. Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0 16:20:07,575 INFO 1124096336 [sqlalchemy.pool.QueuePool.0x..10] Pool recreating Any ideas? Thanks for your help! Cheers, -- Kamil Gorlo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at
[sqlalchemy] Re: MySQL has gone away (again)
Kamil Gorlo wrote:
On Thu, Jun 4, 2009 at 4:20 PM, Michael Bayermike...@zzzcomputing.com wrote:
the connection went from good to dead within a few seconds (assuming SQL
was successfully emitted on the previous checkout). Your database was
restarted or a network failure occurred.
There is no other option? I'm pretty sure that DB was not restarted,
network failure is of course possible but still.. (this is the same
LAN).
Another cause of went away messages is a query that exceeds the
configured memory resources on the server. Taking a look at MySQL's
logs may shed more light give hints for which buffers need tuning if
that's the problem.
But, assuming this is external problem - is there any way to tell
SQLAlchemy to try another connection for the same request (instead of
returning HTTP 500 for user), or maybe other pooling strategy or even
something else?
Yes, with a simple pool event listener you can ensure the liveliness of
connections before the pool hands them out for use. Usage example is
attached.
Cheers,
Jason
class LookLively(object):
Ensures that MySQL connections checked out of the
pool are alive.
def checkout(self, dbapi_con, con_record, con_proxy):
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError, ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
# caught by pool, which will retry with a new connection
raise exc.DisconnectionError()
else:
raise
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
from sqlalchemy import exc
class LookLively(object):
Ensures that MySQL connections checked out of the pool are alive.
Specific to the MySQLdb DB-API. Note that this can not totally
guarantee live connections- the remote side can drop the connection
in the time between ping and the connection reaching user code.
This is a simplistic implementation. If there's a lot of pool churn
(i.e. implicit connections checking in and out all the time), one
possible and easy optimization would be to add a timer check:
1) On check-in, record the current time (integer part) into the
connection record's .properties
2) On check-out, compare the current integer time to the (possibly
empty) record in .properties. If it is still the same second as
when the connection was last checked in, skip the ping. The
connection is probably fine.
Something much like this logic will go into the SQLAlchemy core
eventually.
-jek
def checkout(self, dbapi_con, con_record, con_proxy):
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError, ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
raise exc.DisconnectionError()
else:
raise
# To see a connection die post-ping, take the sleep out of reap()
# below and run this in a tight loop. It should happen eventually on
# a fast machine.
#
# $ while thisscript.py; do echo; done
if __name__ == '__main__':
import sys, time
if len(sys.argv) 1:
from pkg_resources import require
require('mysql-python==%s' % sys.argv[1])
from sqlalchemy import *
e = create_engine('mysql:///test?unix_socket=/var/tmp/mysql.sock',
max_overflow=0, pool_size=2, # constrain our test
listeners=[LookLively()])
# reserve a connection.
reaper = e.connect()
def reap(id):
reaper.execute(text('KILL :thread'), thread=id)
time.sleep(0.15) # give the thread a chance to die
c2 = e.connect()
c2_threadid = c2.scalar('SELECT CONNECTION_ID()')
print Connection c2 id=%s % c2_threadid
# return c2 to the pool. (the db-api connection will remain open)
c2.close()
del c2
reap(c2_threadid)
c2 = e.connect()
new_threadid = c2.scalar('SELECT CONNECTION_ID()')
print Connection c2 now has id=%s % new_threadid
try:
# connection is still alive, kill it mid-stream
reap(new_threadid)
c2.execute('SELECT 1')
assert False
except Exception, ex:
print Expected: Did not reconnect mid-transaction, exception:, ex
c2 = e.connect()
final_threadid = c2.scalar('SELECT CONNECTION_ID()')
print Connection c2 now has id=%s % final_threadid
[sqlalchemy] Re: Engine.execute interpreting all parameters as string data
I was about to follow up to Mike's suggestion of a CAST with That
will never work because ... but I decided to try it out and .. it
works!!!:
for i in e.execute(sql.text(select result = dateadd(day, CONVERT
(INTEGER,:days), getdate())), dict(days = 7)):
print i
(datetime.datetime(2009, 6, 15, 11, 32, 21, 88),)
for i in e.execute(sql.text(select dateadd(day, CONVERT
(INTEGER,:days) AS result, getdate())), dict(days = 7)):
print i
# This fails with:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000]
[FreeTDS][SQL Server]Statement(s) could not be prepared. (8180)')
u'select dateadd(day, CONVERT(INTEGER,?) AS result, getdate())' [7]
This is very interesting and surprising to me as my understanding of
the rules for placeholders for prepared statements in SQL Server and
Sybase limit their use to:
• In place of one or more values in an insert statement
• In the set clause of an update statement
• In the where clause of a select or update statement
I don't know what it is about the result= syntax that makes it work
but it is good to know. However, there are a couple of problems with
dateadd and friends (datediff,datepart, datename) in mssql and
Sybase: the datepart parameter is not bindable (or, rather, I have
never been able to figure out how to do it). It must be *unquoted*
literal in the set:
yearyy
quarter qq
month mm
weekwk
day dd
dayofyear dy
weekday dw
hourhh
minute mi
second ss
millisecond ms
calweekofyear cwk
calyearofweek cyr
caldayofweekcdw
Mike, have you any suggestions on how to declare the various date-
related function in SA so that the first parameter is passed through
as an unquoted literal (maybe with some client-side checks to make
sure it is in the list above) and not as a bind parameter?
pjjH
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy for c++
If you are looking for ORM in c++, maybe this conversation can help: http://stackoverflow.com/questions/74141/good-orm-for-c-solutions - Didip - On Sun, Jun 7, 2009 at 11:56 PM, Jarrod Chesney jarrod.ches...@gmail.comwrote: Hi All Does anyone knows where i can find information about using SQLAlchemy from c++ or even if its possible. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: insert statment
You have Syntax Error here:
('sdsd':'sdsds')
That one should be tuple right?
- Didip -
On Mon, Jun 8, 2009 at 6:14 AM, Ash ashishsinghbha...@gmail.com wrote:
Hello ,
I am trying to insert in the table using two ways in the values which
i show below
engine = sqlalchemy.create_engine(to poastgres)
metadata = MetaData()
t1 = Table('master',metadata) # assume master has 2 feilds name , city
t1.insert({'name':'','city':'bank'})
engine,execute(t1)
This works for fine me.
If i make values like this
tt = [('asasas','belhium'),('sdsd':'sdsds')]
t1.insert(values=tt)
i get error
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) syntax
error at or near )
LINE 1: INSERT INTO abc () VALUES ()
^
'INSERT INTO abc () VALUES ()' {}
Can any one guide whts wrong... i jnow value is not being passed so
anyother 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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Testing and version 0.5.4
Hi. I am wanting to do some testing of an application that uses SQLAlchemy 0.5.4, but I need to load some initial data to the database. When I searched for this in google, python-fixture appears as the answer. But checking the page of python-fixture it doesn't seems work for 0.5. The question are: - Is the documentation of fixture old, and it is working with 0.5? - Which frameworks are you using for filling the database? Thanks in advance, Tomas Zulberti --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Engine.execute interpreting all parameters as string data
Thanks for the reply -
A cast works fine here, but it is a workaround that would be nice to
avoid. I'll see what kind of behavior I get by dropping back to just
pyodbc. My other concern is that all numeric parameters are being
treated as varchar and undergoing implicit conversions in SQL Server -
which for whatever reason won't fly inside date functions hence the
error.
This also exposes a bit of a misconception on my part: I had always
assumed that parameterized queries were simply parse/validation
operations handled by the connection API which then sent massaged SQL
to the db. Not so! I feel like I just learned that you have to put oil
in your car. What else don't I know?
Are there more robust ways to get SA running with SQL Server than
pyodbc-unixODBC-FreeTDS?
Oh, and 'select x = foo' is just a T-SQL construct for column
aliasing, works the same as 'select foo as x'...
On Jun 8, 10:25 am, Michael Bayer mike...@zzzcomputing.com wrote:
Malherbe wrote:
I'm new to SQLAlchemy (and Python in general) and have been struggling
to get a basic execution wrapper working - the goal is to use of raw
batches of parameterized SQL - no ORM.
The engine initializes and I can run queries through it without a
problem, however the generated SQL from the execute statement seems to
interpret everything as string data.
The line:
engine.execute(text(select result = dateadd(day, :days, getdate())),
dict(days = 7))
...yields the following error:
ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL
Server]Argument data type varchar is invalid for argument 2 of dateadd
function. (8116) (SQLPrepare)') 'select result = dateadd(day, ?,
getdate())' [7]
Am I overlooking something simple here? Is there a way to have the
engine substitute parameters as per the Python datatype?
parameters work by passing them to the execute() or executemany() method
of the underlying DBAPI cursor. SQLAlchemy usually does not affect these
values except in specific cases, such as unicode strings that need to be
encoded to utf-8 or dates that needs to be turned into strings. For plain
python types like ints, floats, etc. the DBAPI is usually able to handle
these straight through and SQLAlchemy doesn't affect them. Unfortunately,
you're using FreeTDS, which is the most buggy and difficult DBAPI out
there (and SQL server the most finnicky database), so the issue probably
lies in some combination of SQL Server and FreeTDS not liking what is
being sent. Also the format of the SQL you're using select x = foo()
seems unusual but I'm not deeply familiar with the quirks of SQL server.
you might want to try a raw DBAPI script both with FreeTDS and perhaps
without, using Pyodbc, to narrow the issue down to FreeTDS. There also
might be a CAST() syntax you can add to your statement - something along
the lines of dateadd(day, CAST ? AS INT, getdate()).
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Formatting return of 'query'
I have something like this to serialize a result-set to delimited file- format. It is not very pretty and probably not at all pythonic but I find it handy. pjjH def as_delimited(q, *args): csvdata = StringIO() w = writer(csvdata, delimiter='|') for i in q.values(*args): w.writerow(i) yield csvdata.getvalue() csvdata.truncate(0) q = session.query(User) for i in as_delimited(q, User.firstname,User.lastname,User.age,User.password): print i, On Jun 8, 10:18 am, Glauco gla...@sferacarta.com wrote: Harish Vishwanath ha scritto: cut How can I modify this query to return something like : [(fname~lname~22~pwd)...] with '~' being preferred delimiter. SA return a list or record, what exactly you are searching for? a string or something else I would like to know if I can return something like above directly from the query itself. something like ? [ '~'.join(x) for x in qry.fetchall() ] Glauco --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
I have looked into this and considered what you have said. I think I have come up with a potential solution. It seems to be that the most common driver for mssql on non-windows platforms is going to be freeTDS. Since there appears to be no way of knowing what ODBC is using under the hood we should be able to safely assume that they are using freeTDS if not on windows. Further, there should be nominal overhead in setting nocount on. So perhaps the following diff will offer what is needed to address this specific issue. --- sqlalchemy/databases/mssql.py 2009-06-01 13:00:36.0 -0400 +++ sqlalchemy/databases/mssql.py 2009-06-08 15:31:22.0 -0400 @@ -239,7 +239,7 @@ does **not** work around -import datetime, decimal, inspect, operator, re, sys, urllib +import datetime, decimal, inspect, operator, re, sys, urllib, os from sqlalchemy import sql, schema, exc, util from sqlalchemy import Table, MetaData, Column, ForeignKey, String, Integer @@ -982,6 +982,8 @@ super(MSSQLExecutionContext_pyodbc, self).pre_exec() if self.compiled.isinsert and self.HASIDENT and not self.IINSERT \ and len(self.parameters) == 1 and self.dialect.use_scope_identity: +if os.name != 'nt': +self.cursor.execute(SET NOCOUNT ON) self.statement += ; select scope_identity() def post_exec(self): @@ -996,6 +998,8 @@ except pyodbc.Error, e: self.cursor.nextset() self._last_inserted_ids = [int(row[0])] +if os.name != 'nt': +self.cursor.execute(SET NOCOUNT OFF) else: super(MSSQLExecutionContext_pyodbc, self).post_exec() On May 9, 12:41 am, mtrier mtr...@gmail.com wrote: If you change the test case to indicate that supports_unicode and supports_unicode_statements = False, then it runs just fine with the fix. Without the fix it fails as well, which indicates to me the issue is in FreeTDS. I'd like to commit this but I want to have a discussion with Mike Bayer first to be sure he's okay with it. I had a discussion with Mike Bayer and he expressed that he was uncomfortable committing a hack that just hides the problem instead of figuring out and fixing the problem properly. As we got into the code we began to question some of the design choices surrounding that bit of code, specifically the use of the ; select scope_identity() part. I spent quite a bit of time last night digging into the whole issue and here are my findings. First I removed the pyodbc specific code and just tried to use the base dialect code which doesn't do the ; select scope_identity() hack but instead actually calls a separate execute in the post_exec to get the identity value. This resulted in returning None values every time. I thought it was an issue with pyodbc since they indicate so in their documentation, but it turns out a raw pyodbc script produces the correct results. I finally discovered that the reason we're getting None in this case is do to the prepared queries. Basically the prepared query is in a different scope than the post_exec so it can't get the identity value. Changing this to not use scope_identity but to use @@identity works properly. Now clearly that's not the desired solution since that will be affected by triggers, etc..., and likely the reason for the odd implementation we see of ; select scope_identity. This ensured that the identity was retrieved in the same scope, prepared statement, as the initial insert. I say all the above just more for reference documentation and not as a solution to the problem. Once I got passed the identity issue I was able to get back to the initial Invalid Cursor State problem. After lots of traces it's clear that this cursor problem is a result of something that FreeTDS is doing when retrieving the identity, but only in the case of a transaction. The problem is related to the fact that in those cases the cursor is returning more than one result. That particular cursor error occurs when you try to select an identity but have not fully selected al the results from the cursor. The perplexing part is that the return value of the second result is always None which indicates that there are no more results. Here's a breakdown of what I saw: 1. fetchall()[0] - this will cause the problem to go away as indicated above because it's fully selecting all results before the identity is retrieved. 2. fetchone; fetchone() - if I add two fetchone() statements it will also cause the problem to go away. This clearly indicates that there is a second result. 3. session.commit() - Adding a session.commit() following each insert also causes the problem to go away. So clearly it's being influenced by the open transaction. I proved this by writing raw pyodbc outside of a transaction which worked fine. So the end result of all this is that I know the
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
I'd rather a flag, or better yet in 0.6 a freetds specific dialect/url, i.e. mssql+freetds://url. ddorothy wrote: I have looked into this and considered what you have said. I think I have come up with a potential solution. It seems to be that the most common driver for mssql on non-windows platforms is going to be freeTDS. Since there appears to be no way of knowing what ODBC is using under the hood we should be able to safely assume that they are using freeTDS if not on windows. Further, there should be nominal overhead in setting nocount on. So perhaps the following diff will offer what is needed to address this specific issue. --- sqlalchemy/databases/mssql.py 2009-06-01 13:00:36.0 -0400 +++ sqlalchemy/databases/mssql.py 2009-06-08 15:31:22.0 -0400 @@ -239,7 +239,7 @@ does **not** work around -import datetime, decimal, inspect, operator, re, sys, urllib +import datetime, decimal, inspect, operator, re, sys, urllib, os from sqlalchemy import sql, schema, exc, util from sqlalchemy import Table, MetaData, Column, ForeignKey, String, Integer @@ -982,6 +982,8 @@ super(MSSQLExecutionContext_pyodbc, self).pre_exec() if self.compiled.isinsert and self.HASIDENT and not self.IINSERT \ and len(self.parameters) == 1 and self.dialect.use_scope_identity: +if os.name != 'nt': +self.cursor.execute(SET NOCOUNT ON) self.statement += ; select scope_identity() def post_exec(self): @@ -996,6 +998,8 @@ except pyodbc.Error, e: self.cursor.nextset() self._last_inserted_ids = [int(row[0])] +if os.name != 'nt': +self.cursor.execute(SET NOCOUNT OFF) else: super(MSSQLExecutionContext_pyodbc, self).post_exec() On May 9, 12:41 am, mtrier mtr...@gmail.com wrote: If you change the test case to indicate that supports_unicode and supports_unicode_statements = False, then it runs just fine with the fix. Without the fix it fails as well, which indicates to me the issue is in FreeTDS. I'd like to commit this but I want to have a discussion with Mike Bayer first to be sure he's okay with it. I had a discussion with Mike Bayer and he expressed that he was uncomfortable committing a hack that just hides the problem instead of figuring out and fixing the problem properly. As we got into the code we began to question some of the design choices surrounding that bit of code, specifically the use of the ; select scope_identity() part. I spent quite a bit of time last night digging into the whole issue and here are my findings. First I removed the pyodbc specific code and just tried to use the base dialect code which doesn't do the ; select scope_identity() hack but instead actually calls a separate execute in the post_exec to get the identity value. This resulted in returning None values every time. I thought it was an issue with pyodbc since they indicate so in their documentation, but it turns out a raw pyodbc script produces the correct results. I finally discovered that the reason we're getting None in this case is do to the prepared queries. Basically the prepared query is in a different scope than the post_exec so it can't get the identity value. Changing this to not use scope_identity but to use @@identity works properly. Now clearly that's not the desired solution since that will be affected by triggers, etc..., and likely the reason for the odd implementation we see of ; select scope_identity. This ensured that the identity was retrieved in the same scope, prepared statement, as the initial insert. I say all the above just more for reference documentation and not as a solution to the problem. Once I got passed the identity issue I was able to get back to the initial Invalid Cursor State problem. After lots of traces it's clear that this cursor problem is a result of something that FreeTDS is doing when retrieving the identity, but only in the case of a transaction. The problem is related to the fact that in those cases the cursor is returning more than one result. That particular cursor error occurs when you try to select an identity but have not fully selected al the results from the cursor. The perplexing part is that the return value of the second result is always None which indicates that there are no more results. Here's a breakdown of what I saw: 1. fetchall()[0] - this will cause the problem to go away as indicated above because it's fully selecting all results before the identity is retrieved. 2. fetchone; fetchone() - if I add two fetchone() statements it will also cause the problem to go away. This clearly indicates that there is a second result. 3. session.commit() - Adding a session.commit() following each insert also causes the problem to go away. So clearly it's being influenced by the
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Mon, Jun 8, 2009 at 5:29 PM, Michael Bayer mike...@zzzcomputing.comwrote: I'd rather a flag, or better yet in 0.6 a freetds specific dialect/url, i.e. mssql+freetds://url. I personally like the freetds dialect idea because there's a lot more issues specific to freetds than just this one. -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] unit tests migrating to nose
hey gang - We've ported the unit tests over to nose, and our work is currently in a dev branch in SVN which you can see at http://svn.sqlalchemy.org/sqlalchemy/branches/nosetests . This port is against the 0.5 trunk, and the plan is to merge it to both 0.5 trunk and to 0.6 where we would migrate the additional tests that have been added in 0.6. For those who run the tests, it basically means the tests are run via nosetests instead of python test/alltests.py - and instead of setting up PYTHONPATH, you just need to have SQLA installed anywhere via setuptools since we use an entrypoint plugin for nose.It is a nicer environment with less code, and we get the usage of all the great nose plugins for free like coverage and profiling. We still have a significant amount of test helpers but their organization is now much more straightforward. The question is, is a change in unit test style within the 0.5 series going to be painful for anyone ?I'm targeting both 0.5 and 0.6 since the vast majority of tests between the two are the same, and I plan on merging lots more fixes/tests between the two. Also I'd just like to get off of the old unittest framework. I'd like to know if anyone has SQLA involved in some automated build process involving running its tests which would be impacted by this, because if it really is a problem I'll move the nose switch to be strictly 0.6. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Engine.execute interpreting all parameters as string data
A little testing confirms that the problem is bubbling up from below
SA. The equivalent statement run through pyodbc yields the same error.
Out of curiosity, I checked to see if Python types were converted as
expected in a different context.
for pytype in (1, 1.1, decimal.Decimal('1.1'), '1',
datetime.datetime.now(), True, None):
cursor.execute(select cast(sql_variant_property(?, 'BaseType') as
varchar) as sqltype, pytype)
print type(pytype), ' = ', cursor.fetchone().sqltype
No surprises there:
type 'int' = int
type 'float' = float
class 'decimal.Decimal' = numeric
type 'str' = varchar
type 'datetime.datetime' = datetime
type 'bool' = bit
type 'NoneType' = None
However, adding a unicode type to the test list gives us nice [HY004]
[FreeTDS][SQL Server]Invalid data type (0) (SQLBindParameter) error
message. Using +/- operators on datetimes also throws a type error.
Anyway, this is probably veering outside of the scope of an SQLAlchemy
group topic, but I'll try and find another (free) alternative to
FreeTDS and see how the same tests fare...
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: insert statment
Sorry, Its my typing mistake :( . I put : insted , . But still my
question yeat remained unanswered. :(
On Jun 8, 7:50 pm, Didip Kerabat did...@gmail.com wrote:
You have Syntax Error here:
('sdsd':'sdsds')
That one should be tuple right?
- Didip -
On Mon, Jun 8, 2009 at 6:14 AM, Ash ashishsinghbha...@gmail.com wrote:
Hello ,
I am trying to insert in the table using two ways in the values which
i show below
engine = sqlalchemy.create_engine(to poastgres)
metadata = MetaData()
t1 = Table('master',metadata) # assume master has 2 feilds name , city
t1.insert({'name':'','city':'bank'})
engine,execute(t1)
This works for fine me.
If i make values like this
tt = [('asasas','belhium'),('sdsd':'sdsds')]
t1.insert(values=tt)
i get error
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) syntax
error at or near )
LINE 1: INSERT INTO abc () VALUES ()
^
'INSERT INTO abc () VALUES ()' {}
Can any one guide whts wrong... i jnow value is not being passed so
anyother 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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: insert statment
you need to use the executemany form described at
http://www.sqlalchemy.org/docs/05/sqlexpression.html#executing-multiple-statements
.
On Jun 8, 2009, at 11:54 PM, Ashish Bhatia wrote:
Sorry, Its my typing mistake :( . I put : insted , . But still my
question yeat remained unanswered. :(
On Jun 8, 7:50 pm, Didip Kerabat did...@gmail.com wrote:
You have Syntax Error here:
('sdsd':'sdsds')
That one should be tuple right?
- Didip -
On Mon, Jun 8, 2009 at 6:14 AM, Ash ashishsinghbha...@gmail.com
wrote:
Hello ,
I am trying to insert in the table using two ways in the values
which
i show below
engine = sqlalchemy.create_engine(to poastgres)
metadata = MetaData()
t1 = Table('master',metadata) # assume master has 2 feilds name ,
city
t1.insert({'name':'','city':'bank'})
engine,execute(t1)
This works for fine me.
If i make values like this
tt = [('asasas','belhium'),('sdsd':'sdsds')]
t1.insert(values=tt)
i get error
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) syntax
error at or near )
LINE 1: INSERT INTO abc () VALUES ()
^
'INSERT INTO abc () VALUES ()' {}
Can any one guide whts wrong... i jnow value is not being passed so
anyother 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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
