[sqlalchemy] Re: Supporting sybase backend through mx.ODBC driver
Hi Paul, Paul Johnston wrote: Hi, I was wondering if there is any development effort going on to support a sybase backend for sqlalchemy based on the mx.ODBC drivers from egenix (google didn't find anything and there was no relevant thread on this list) ? Are you hard set on mxODBC? PyODBC seems to be a good free alternative, and is already the preferred SQLAlchemy driver for MS-SQL, which may help get you started. My customer is already using mxODBC for directly talking to sybase from his python business logic. I'll nevertheless have a look at the pyODBC driver and try to figure out how much work it would be to support that backend. Paul cheers, alex --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] mySQL and timedelta
Hello everyone,
I'm having some trouble with a query involving a timedelta object
with a mySQL backend (MyISAM). I have a table called Run that has two
fields like so.
sa.Column('starttime', sa.TIMESTAMP),
sa.Column('endtime', sa.TIMESTAMP)
I'm trying to find all rows that have endtime - starttime equal to
some timedelta obtained from a user. The query is constructed like
this
l = datetime.timedelta(seconds=907)
cc = ((Run.c.endtime - Run.c.starttime) == l)
t=q.select(cc)
2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
SELECT runs.status AS runs_status, runs.workdir AS runs_workdir,
runs.machinetype AS runs_machinetype, runs.endtime AS runs_endtime,
runs.hostname AS runs_hostname, runs.cmdline AS runs_cmdline,
runs.incremental AS runs_incremental, runs.`user` AS runs_user,
runs.starttime AS runs_starttime, runs.rid AS runs_rid
FROM runs
WHERE (runs.endtime - runs.starttime) = %s ORDER BY runs.rid
2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
[datetime.timedelta(0, 907)]
The %s looks funny there.
This returns the wrong row and when I do this kind of query via my
script (which is web based), I get messages in the server error log
that look like this
/usr/lib/python2.5/site-packages/sqlalchemy/databases/mysql.py:313:
Warning: Truncated incorrect DOUBLE value: '00:08:40'
cursor.execute(statement, parameters)
I expect the users to input values like HH:MM:SS which I split and use
to create the timedelta object.
I'd appreciate any help .
Peace.
--~--~-~--~~~---~--~~
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: IntegrityError during query?
Hi again, I really must be missing something fundamental here as I cannot seem to solve this problem: I have a loop that queries one table (without any contraints) and writes to a second table (with constraints). Here's what the loop looks like in pseudo-code: while True: 1) query old table and create a work list 2) while items are in the work list: 2.1) create a new object 2.2) save 3) commit I can wrap the save (2.2) and commit (3) in try/except blocks which solves the IntegrityError exceptions at that point. The problem is that I'm getting IntegrityError exceptions in the query section (1) what seem to be deferred INSERTS from the commit (3). How can I turn off the deferred inserts? Mark On Sep 25, 9:27 am, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I have a newbie question: I'm parsing a log file in order to record login-times but I'm getting an IntegrityError on an insert during a query. Does this make sense? Even though I'm going a commit at the botton of the loop should I expect the INSERT to actually happen during a subsequent query? Thanks, Mark --~--~-~--~~~---~--~~ 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: IntegrityError during query?
On Oct 1, 2007, at 11:02 AM, [EMAIL PROTECTED] wrote: Hi again, I really must be missing something fundamental here as I cannot seem to solve this problem: I have a loop that queries one table (without any contraints) and writes to a second table (with constraints). Here's what the loop looks like in pseudo-code: while True: 1) query old table and create a work list 2) while items are in the work list: 2.1) create a new object 2.2) save 3) commit I can wrap the save (2.2) and commit (3) in try/except blocks which solves the IntegrityError exceptions at that point. The problem is that I'm getting IntegrityError exceptions in the query section (1) what seem to be deferred INSERTS from the commit (3). How can I turn off the deferred inserts? some sample code would be helpful here in order to get some context as to what youre doing. if the problem is just that the INSERT's dont occur until you say session.commit(), you can issue session.flush () at any time which will flush all pending changes/new items to the database. --~--~-~--~~~---~--~~ 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 and timedelta
On Oct 1, 2007, at 4:45 AM, Noufal wrote:
Hello everyone,
I'm having some trouble with a query involving a timedelta object
with a mySQL backend (MyISAM). I have a table called Run that has two
fields like so.
sa.Column('starttime', sa.TIMESTAMP),
sa.Column('endtime', sa.TIMESTAMP)
I'm trying to find all rows that have endtime - starttime equal to
some timedelta obtained from a user. The query is constructed like
this
l = datetime.timedelta(seconds=907)
cc = ((Run.c.endtime - Run.c.starttime) == l)
t=q.select(cc)
it would help to know what version youre using. SQLAlchemy might
treat this expression differently in 0.3 as it would in 0.4.
ultimately, the issue is probably that MySQLdb doesn't understand the
timedelta datatype youre sending over.
2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
SELECT runs.status AS runs_status, runs.workdir AS runs_workdir,
runs.machinetype AS runs_machinetype, runs.endtime AS runs_endtime,
runs.hostname AS runs_hostname, runs.cmdline AS runs_cmdline,
runs.incremental AS runs_incremental, runs.`user` AS runs_user,
runs.starttime AS runs_starttime, runs.rid AS runs_rid
FROM runs
WHERE (runs.endtime - runs.starttime) = %s ORDER BY runs.rid
2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
[datetime.timedelta(0, 907)]
The %s looks funny there.
the %s is a bind parameter.
Without trying it (and also maybe Jason has some ideas on this), youd
have to send in the type of value which MySQL produces when doing
math operations with dates. from your log output it seems like it
might be retunring a double, in which case thats the kind of value
youd have to bind to the statement , instead of a python
datetime.timedelta object.
--~--~-~--~~~---~--~~
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: ORDER_BY always in SELECT statements?
On Oct 1, 2007, at 8:20 AM, [EMAIL PROTECTED] wrote: Hi Michael, thanks for the help! The documentation mentions that this option over-rides the per-engine configuration but I couldn't find a create_engine option to set this. Any reference would be greatly appreciated! uhhh there is no per-engine configuration of ORDER BY, those must be some old docs youre reading. ORDER BY is only defaulted in the ORM at the mapper() and relation() level. --~--~-~--~~~---~--~~ 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: IntegrityError during query?
Hi Michael, As I tried to show in the pseudo-code, the INSERTS look like they're happening during the query (in step 1), well after the save/commit. I even tried to add a flush and, when I turn on echo mode, I see inserts happening at the query step. Is this even possible? Mark On Oct 1, 11:49 am, Michael Bayer [EMAIL PROTECTED] wrote: On Oct 1, 2007, at 11:02 AM, [EMAIL PROTECTED] wrote: Hi again, I really must be missing something fundamental here as I cannot seem to solve this problem: I have a loop that queries one table (without any contraints) and writes to a second table (with constraints). Here's what the loop looks like in pseudo-code: while True: 1) query old table and create a work list 2) while items are in the work list: 2.1) create a new object 2.2) save 3) commit I can wrap the save (2.2) and commit (3) in try/except blocks which solves the IntegrityError exceptions at that point. The problem is that I'm getting IntegrityError exceptions in the query section (1) what seem to be deferred INSERTS from the commit (3). How can I turn off the deferred inserts? some sample code would be helpful here in order to get some context as to what youre doing. if the problem is just that the INSERT's dont occur until you say session.commit(), you can issue session.flush () at any time which will flush all pending changes/new items to the database. --~--~-~--~~~---~--~~ 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: IntegrityError during query?
On Oct 1, 2007, at 12:12 PM, [EMAIL PROTECTED] wrote: Hi Michael, As I tried to show in the pseudo-code, the INSERTS look like they're happening during the query (in step 1), well after the save/commit. I even tried to add a flush and, when I turn on echo mode, I see inserts happening at the query step. Is this even possible? Mark OK by code example im looking for: - are you on version 0.3 or 0.4 ? - how are you creating your session ? - using multiple threads ? are you keeping each session local to a single thread ? - whats happening between steps 3 and 1 ? depending on how the session is set up, yes a flush() can be issued right before the query executes (i.e. autoflush). But, according to your workflow below, it should not; since you are calling a commit() at the end. - what kind of IntegrityError youre getting...duplicate row insert ? missing foreign key ? no primary key ? --~--~-~--~~~---~--~~ 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: IntegrityError during query?
Hi Michael, I'm creating the session by: Session = sessionmaker(bind = engine, autoflush = True, transactional = True) session = Session() and I'm not using any threading at all (therefore no thread-local storage). The only thing between the commit and the next query is some reporting of statistics (using sys.stdout). I'm getting a constraint violation IntegrityError. Thanks again for any help! Mark On Oct 1, 12:47 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Oct 1, 2007, at 12:12 PM, [EMAIL PROTECTED] wrote: Hi Michael, As I tried to show in the pseudo-code, the INSERTS look like they're happening during the query (in step 1), well after the save/commit. I even tried to add a flush and, when I turn on echo mode, I see inserts happening at the query step. Is this even possible? Mark OK by code example im looking for: - are you on version 0.3 or 0.4 ? - how are you creating your session ? - using multiple threads ? are you keeping each session local to a single thread ? - whats happening between steps 3 and 1 ? depending on how the session is set up, yes a flush() can be issued right before the query executes (i.e. autoflush). But, according to your workflow below, it should not; since you are calling a commit() at the end. - what kind of IntegrityError youre getting...duplicate row insert ? missing foreign key ? no primary key ? --~--~-~--~~~---~--~~ 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: IntegrityError during query?
On Oct 1, 2007, at 2:29 PM, [EMAIL PROTECTED] wrote: Hi Michael, I'm creating the session by: Session = sessionmaker(bind = engine, autoflush = True, transactional = True) session = Session() and I'm not using any threading at all (therefore no thread-local storage). The only thing between the commit and the next query is some reporting of statistics (using sys.stdout). I'm getting a constraint violation IntegrityError. unique constraint ? PK constraint ? foreign key constraint ?are you doing any explicit INSERT statements of your own independent of the session ? I cant diagnose the problem any further on this end without an explicit example. --~--~-~--~~~---~--~~ 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] INSERT IGNORE
I'm not seeing the ability to use the INSERT IGNORE ... syntax. Is this available anywhere? Jim Musil --~--~-~--~~~---~--~~ 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] ConcurrentModificationError without concurrent modification?
ConcurrentModificationError: Deleted rowcount 3 does not match number of objects deleted 1 raised by this code: with Session() as session: dl = session.merge(dl) session.delete(dl) There are no other sessions alive, so how can that possibly be a concurrent modification? Using sqlalchemy 0.4 beta 5, python 2.5, sqllite. dl has a many-to-many relationship associated with it. Any ideas why this is happening? Thanks, -Dan --~--~-~--~~~---~--~~ 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: ConcurrentModificationError without concurrent modification?
On Oct 1, 2007, at 7:25 PM, Dan Eloff wrote: ConcurrentModificationError: Deleted rowcount 3 does not match number of objects deleted 1 raised by this code: with Session() as session: dl = session.merge(dl) session.delete(dl) There are no other sessions alive, so how can that possibly be a concurrent modification? Using sqlalchemy 0.4 beta 5, python 2.5, sqllite. dl has a many-to-many relationship associated with it. Any ideas why this is happening? most likely too many association rows are present in the m2m table. --~--~-~--~~~---~--~~ 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: ConcurrentModificationError without concurrent modification?
Also, I think we're going to change the name of that exception. its not necessarily Concurrent modification...Hibernate calls it StaleStateException which might be better. On Oct 1, 2007, at 7:25 PM, Dan Eloff wrote: ConcurrentModificationError: Deleted rowcount 3 does not match number of objects deleted 1 raised by this code: with Session() as session: dl = session.merge(dl) session.delete(dl) There are no other sessions alive, so how can that possibly be a concurrent modification? Using sqlalchemy 0.4 beta 5, python 2.5, sqllite. dl has a many-to-many relationship associated with it. Any ideas why this is happening? Thanks, -Dan --~--~-~--~~~---~--~~ 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: limit=bindparam(result_limit)
I am trying to cut down on time the db spends compiling statements.
Using bind params
allows the query to look the same to the db, thus allowing it to not
parse it again. It's the
same use case that makes people want to use bind params anywhere. Am I
missing
something here? I'm a relative newbie to mysql and sqlalchemy, so I
may be missing information, but I'm
assuming that using a bindparam in sqlalchemy will result in a
prepared statement executed
using the assigned values that I want. If it is using a prepared
statement,
I'd expect the query to look something like
select * from table where limit=?
For Oracle, the rownum is simply part of the where clause, so I
wouldn't expect an issue with using
a bindparam for the limit.
On Sep 22, 8:09 am, Michael Bayer [EMAIL PROTECTED] wrote:
limit is not really portable to all databases; in some cases
(particularly Oracle) SQLAlchemy has to use some completely different
syntaxes to come up with LIMIT.
Additionally, the purpose of a bind parameter is to represent data
that is compared against or inserted into a column expression.
Whereas the LIMIT expression is part of the SQL construct
itself...seems like PG and sqlite both allow it though but im not
sure how oracle, mssql can deal with it (im pretty sure mysql allows
it).
more importantly whats the use case here ? just trying to cut down
on the time SQLAlchemy spends compile statements ?
On Sep 18, 2007, at 5:30 PM, dykang wrote:
Hi,
I was trying to write a query where the limit clause was actually a
bind param, however it appears that, (in 3.10), this isn't possible.
Am I missing something, or is this a bug?
for example I'm just doing
s = select ([Table], whereclause, limit=bindparam('mylimit'))
s.execute(mylimit=5)
Can someone tell me if this is not a supported behavior, or what I
need to do for this to work?
Thanks,
D
--~--~-~--~~~---~--~~
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: ConcurrentModificationError without concurrent modification?
On 10/1/07, Michael Bayer [EMAIL PROTECTED] wrote: most likely too many association rows are present in the m2m table. Do you mean there were redundant associations? How can I check if that's the cause of the problem? Thanks, -Dan --~--~-~--~~~---~--~~ 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: limit=bindparam(result_limit)
On Oct 1, 2007, at 8:35 PM, dykang wrote: I am trying to cut down on time the db spends compiling statements. Using bind params allows the query to look the same to the db, thus allowing it to not parse it again. It's the same use case that makes people want to use bind params anywhere. Am I missing something here? I'm a relative newbie to mysql and sqlalchemy, so I may be missing information, but I'm assuming that using a bindparam in sqlalchemy will result in a prepared statement executed using the assigned values that I want. If it is using a prepared statement, I'd expect the query to look something like select * from table where limit=? we'd have to enhance statement compilation to support this feature (its not a big change). --~--~-~--~~~---~--~~ 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: ConcurrentModificationError without concurrent modification?
On Oct 1, 2007, at 9:22 PM, Dan Eloff wrote: On 10/1/07, Michael Bayer [EMAIL PROTECTED] wrote: most likely too many association rows are present in the m2m table. Do you mean there were redundant associations? How can I check if that's the cause of the problem? the stack trace for the error would originate from sqlalchemy's file 'dependency.py' around line 379. if the m2m table contains multiple, duplicate rows, this problem would occur. placing a unique index on the table (or making both columns of the association table a composite primary key) would prevent the dupe rows from being inserted in the first place. as to the reason the rows may have been inserted in that way, id look at standalone INSERT statements which may be affecting it, as well as redundant mappings against the association table, such as a mapper() defined on it at the same time the table is used as the secondary argument elsewhere. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
