[sqlalchemy] Re: Building an or_ filter in loop
this would even be easier (and correcter if the terms contain %)
written as:
cond = or_(*[Fruit.name.contains(term) for term in terms])
On 8 mei, 21:59, Kyle Schaffrick k...@raidi.us wrote:
On Fri, 8 May 2009 12:52:09 -0700 (PDT)
Bryan bryanv...@gmail.com wrote:
I can't figure out a clean way of adding a bunch of filter terms to a
query in a loop joined by an OR clause. Successive calls to filter
join the expressions by AND. I would like to do something like the
following, but have the expressions joined by OR
terms = ['apple', 'orange', 'peach']
q = Session.query(Fruit)
for term in terms:
q = q.filter(Fruit.name.like('%' + term + '%')
Desired pseudo-sql:
SELECT * FROM fruit WHERE name like '%apple%' OR name like '%orange%'
OR name like '%peach%'
I think this might do what you want:
cond = or_(*[ Fruit.name.like('%' + term + '%') for term in terms ])
q = Session.query(Fruit).filter(cond)
-Kyle
--~--~-~--~~~---~--~~
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: pyodbc issues
Could this be related to this change:
http://www.sqlalchemy.org/trac/changeset/5564
I encountered a similar problem with mssql/pyodbc today (the echo
output showed the correct statements and params, but no changes seemed
to be made in the db at all). The problem only occurred when using the
orm/session layer, same statements with engine.execute seemed to work.
I managed to fix it by reverting the change with the following
monkeypatch:
from sqlalchemy.databases.mssql import MSSQLDialect
MSSQLDialect.do_begin = lambda self, conn: pass
Not sure this is related though (or if this is a good solution), just
thought I'd let it know in case it helps.
greetings,
Steven
On 25 apr, 15:05, Michael Trier mtr...@gmail.com wrote:
On Apr 24, 2009, at 7:50 PM, Michael Mileusnich
justmike2...@gmail.com wrote:
Wow..your example worked for me. Could the kwargs the issue?
No. Likely you have some sort of conflict on the dbapi side.
If it's possible for you to send me your actual code
(mtr...@gmail.com) I'd be happy to try and figure out the issue.
On Fri, Apr 24, 2009 at 3:14 PM, mtrier mtr...@gmail.com wrote:
On Apr 24, 2009, at 4:02 AM, Michael Mileusnich wrote:
I have formated my PC with Vista 32. I also Installed SQL
Server 2008
Express. Installed Python 2.6 and pyodbc...SAME ISSUE. I would
be willing
to have somebody overlook my python code. With echo on
everything looks
like it should be INSERTING.
I've written this script based on the information you have supplied.
It works fine for me:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('mssql://sprint:spr...@localhost/sprint',
echo=True)
metadata = MetaData(engine)
Session = scoped_session(sessionmaker(bind=engine, autoflush=False,
autocommit=True))
action_table = Table(
'ACTIONS', metadata,
Column('ACTIONID', String(48), primary_key=True),
Column('TITLE', String(128)),
Column('CMDLINE', String(512)),
Column('STDIN', Text),
Column('STARTINDIR', String(512)),
Column('PRIO', Integer),
)
class action(object):
def __init__(self, ACTIONID, CMDLINE):
self.ACTIONID = ACTIONID
self.CMDLINE = CMDLINE
def __repr__(self):
return action('%s', '%s') % (self.ACTIONID, self.CMDLINE)
mapper(action, action_table)
metadata.create_all()
session = Session()
new_action = action(ACTIONID = '500', CMDLINE = 'sol')
session.add(new_action)
session.flush()
session.expunge_all()
act = session.query(action).filter_by(ACTIONID='500').one()
assert new_action.ACTIONID == act.ACTIONID
--
Would you please try it and let me know what results you get. The
following is my output.
S:\sqlalchemy.git\libpython msssqlprob.py
2009-04-24 16:10:30,473 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT user_name() as user_name;
2009-04-24 16:10:30,552 INFO sqlalchemy.engine.base.Engine.0x...6110
[]
2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110
[u'dbo']
2009-04-24 16:10:30,582 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TAB
LE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].
[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLU
MN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110
['ACTIONS', u'dbo']
2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110
CREATE TABLE [ACTIONS] (
[ACTIONID] VARCHAR(48) NOT NULL,
[TITLE] VARCHAR(128) NULL,
[CMDLINE] VARCHAR(512) NULL,
[STDIN] TEXT NULL,
[STARTINDIR] VARCHAR(512) NULL,
[PRIO] INTEGER NULL,
PRIMARY KEY ([ACTIONID])
)
2009-04-24 16:10:30,630 INFO sqlalchemy.engine.base.Engine.0x...6110
()
2009-04-24 16:10:30,661 INFO sqlalchemy.engine.base.Engine.0x...6110
COMMIT
2009-04-24 16:10:30,693 INFO sqlalchemy.engine.base.Engine.0x...6110
BEGIN
2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110
INSERT INTO [ACTIONS] ([ACTIONID], [TITLE], [CMDLIN
E], [STDIN], [STARTINDIR], [PRIO]) VALUES (?, ?, ?, ?, ?, ?)
2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110
['500', None, 'sol', None, None, None]
2009-04-24 16:10:30,723 INFO sqlalchemy.engine.base.Engine.0x...6110
COMMIT
2009-04-24 16:10:30,723 INFO
[sqlalchemy] Re: pyodbc issues
Oops, make that:
MSSQLDialect.do_begin = lambda self, conn: None
(pass is not allowed in lambda statements ;-) I had already corrected
the mistake in my code, only to make it again posting the message,
sorry...)
On 27 apr, 22:33, polaar steven.vereec...@gmail.com wrote:
Could this be related to this
change:http://www.sqlalchemy.org/trac/changeset/5564
I encountered a similar problem with mssql/pyodbc today (the echo
output showed the correct statements and params, but no changes seemed
to be made in the db at all). The problem only occurred when using the
orm/session layer, same statements with engine.execute seemed to work.
I managed to fix it by reverting the change with the following
monkeypatch:
from sqlalchemy.databases.mssql import MSSQLDialect
MSSQLDialect.do_begin = lambda self, conn: pass
Not sure this is related though (or if this is a good solution), just
thought I'd let it know in case it helps.
greetings,
Steven
On 25 apr, 15:05, Michael Trier mtr...@gmail.com wrote:
On Apr 24, 2009, at 7:50 PM, Michael Mileusnich
justmike2...@gmail.com wrote:
Wow..your example worked for me. Could the kwargs the issue?
No. Likely you have some sort of conflict on the dbapi side.
If it's possible for you to send me your actual code
(mtr...@gmail.com) I'd be happy to try and figure out the issue.
On Fri, Apr 24, 2009 at 3:14 PM, mtrier mtr...@gmail.com wrote:
On Apr 24, 2009, at 4:02 AM, Michael Mileusnich wrote:
I have formated my PC with Vista 32. I also Installed SQL
Server 2008
Express. Installed Python 2.6 and pyodbc...SAME ISSUE. I would
be willing
to have somebody overlook my python code. With echo on
everything looks
like it should be INSERTING.
I've written this script based on the information you have supplied.
It works fine for me:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('mssql://sprint:spr...@localhost/sprint',
echo=True)
metadata = MetaData(engine)
Session = scoped_session(sessionmaker(bind=engine, autoflush=False,
autocommit=True))
action_table = Table(
'ACTIONS', metadata,
Column('ACTIONID', String(48), primary_key=True),
Column('TITLE', String(128)),
Column('CMDLINE', String(512)),
Column('STDIN', Text),
Column('STARTINDIR', String(512)),
Column('PRIO', Integer),
)
class action(object):
def __init__(self, ACTIONID, CMDLINE):
self.ACTIONID = ACTIONID
self.CMDLINE = CMDLINE
def __repr__(self):
return action('%s', '%s') % (self.ACTIONID, self.CMDLINE)
mapper(action, action_table)
metadata.create_all()
session = Session()
new_action = action(ACTIONID = '500', CMDLINE = 'sol')
session.add(new_action)
session.flush()
session.expunge_all()
act = session.query(action).filter_by(ACTIONID='500').one()
assert new_action.ACTIONID == act.ACTIONID
--
Would you please try it and let me know what results you get. The
following is my output.
S:\sqlalchemy.git\libpython msssqlprob.py
2009-04-24 16:10:30,473 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT user_name() as user_name;
2009-04-24 16:10:30,552 INFO sqlalchemy.engine.base.Engine.0x...6110
[]
2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110
[u'dbo']
2009-04-24 16:10:30,582 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TAB
LE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].
[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLU
MN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110
['ACTIONS', u'dbo']
2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110
CREATE TABLE [ACTIONS] (
[ACTIONID] VARCHAR(48) NOT NULL,
[TITLE] VARCHAR(128) NULL,
[CMDLINE] VARCHAR(512) NULL,
[STDIN] TEXT NULL,
[STARTINDIR] VARCHAR(512) NULL,
[PRIO] INTEGER NULL,
PRIMARY KEY ([ACTIONID])
)
2009-04-24 16:10:30,630 INFO sqlalchemy.engine.base.Engine.0x...6110
()
2009-04-24 16:10:30,661 INFO sqlalchemy.engine.base.Engine.0x...6110
COMMIT
2009-04-24 16:10:30,693 INFO sqlalchemy.engine.base.Engine.0x...6110
BEGIN
2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x
[sqlalchemy] pyodbc and inserts... again
I've just noticed a remaining problem with the pyodbc/inserts with triggers/scope_identity()/set nocount on/nextset() thing ;-) (it's still a workaround if I understand correctly?) If nocount is off (eg. turned off again by the trigger as it seems in my case), MSSQLDialect_pyodbc.do_execute jumps over the rowcount to get to the resultset, but it is possible that there are more rowcounts than foreseen (multiple inserts by the trigger). I've tried the following change in MSSQLDialect_pyodbc.do_execute() with good results: old version, which jumps over one set in case of an exception: try: row = cursor.fetchone() except pyodbc.Error, e: # if nocount OFF fetchone throws an exception and we have to jump over # the rowcount to the resultset cursor.nextset() row = cursor.fetchone() new version, which keeps jumping as long as there are errors: while True: try: row = cursor.fetchone() break except pyodbc.Error, e: # if nocount OFF fetchone throws an exception and we have to jump over # the rowcount to the resultset cursor.nextset() Probably still not ideal, but it seems better than the current one... greetings, Steven --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
Sorry for the delay ;-) No objections. I was just trying to figure out
what was happening. (didn't get at first that module selection is a
bit ugly also meant that it didn't work if you specified the module)
But you already have a patch it seems. I'll try and test it out.
Steven
On 15 mrt, 15:43, Rick Morrison [EMAIL PROTECTED] wrote:
Sorry, Stephen, I replied too early; your second email arrived before the
first. A whole day before the first.
So until we get a real cleanup, you're looking to try modules in this order:
['pyodbc', 'adodbapi', 'pymssql']
Sounds OK to me -- any objections out there?
Rick
On 3/14/07, Rick Morrison [EMAIL PROTECTED] wrote:
It's the second case, that is, it sniffs out what modules are installed.
As I said before, this
(along with other modules that effectively do the same thing), is up for a
clean-up soon, see ticket #480.
Rick
On 3/14/07, polaar [EMAIL PROTECTED] wrote:
{'pyodbc': use_pyodbc, 'adodbapi': use_adodbapi, 'pyodbc':
use_pyodbc}.get(module.__name__, use_default)()
Sorry, should be pymssql instead of pyodbc twice, but I guess you got
that...
--~--~-~--~~~---~--~~
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: pyodbc and tables with triggers
On 12 mrt, 21:47, polaar [EMAIL PROTECTED] wrote:
FYI, specifying module=pyodbc didn't seem to help wrt the
ConcurrentModificationError. Didn't have very much time, had a (very)
quick look at the code in mssql.py, and at first sight, it would seem
that sane_rowcount is a global variable that is only set in the
use_pyodbc() (resp. adodbapy/pymssql) function, which in turn is only
called from use-default(), this would seem to mean only when you don't
specify a module... Either I'm completely wrong (which is very well
possible ;-), as I said, I only took a quick look, and I'm not
familiar with the code), or this means that you may not have adodbapi
(or pymssql) installed in order to use pyodbc correctly???
Update: it indeed seems to work like that. I tried changing the order
of preference in mssql.py so that it first tries pydobc, and that
seems to work: the ConcurrentModificationError no longer occurs. I now
also get a warning about using pyodbc that I didn't get before. (by
the way: I did have to keep the 'set nocount on' in order to prevent
the invalid cursor state problem)
I guess something could be done with changing the following line from
the __init__ method of class MSSQLDialect:
self.module = module or dbmodule or use_default()
to something that calls use_pyodbc/use_pymssql/use_adodbapi based on
module.__name__? (I'm not sure though: use_default seems to be called
already when the mssql is imported and it sets the global dbmodule, so
I'm not confident that this is where it should be done*)
Something like this?
{'pyodbc': use_pyodbc, 'adodbapi': use_adodbapi, 'pyodbc':
use_pyodbc}.get(module.__name__, use_default)()
Steven
* can't test it at home (using linux), and as using python at work is
mostly 'under the radar', I can't spend a lot of time on it there, so
sorry if I can't provide you with a well-tested patch ;-)
--~--~-~--~~~---~--~~
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: pyodbc and tables with triggers
FYI, specifying module=pyodbc didn't seem to help wrt the ConcurrentModificationError. Didn't have very much time, had a (very) quick look at the code in mssql.py, and at first sight, it would seem that sane_rowcount is a global variable that is only set in the use_pyodbc() (resp. adodbapy/pymssql) function, which in turn is only called from use-default(), this would seem to mean only when you don't specify a module... Either I'm completely wrong (which is very well possible ;-), as I said, I only took a quick look, and I'm not familiar with the code), or this means that you may not have adodbapi (or pymssql) installed in order to use pyodbc correctly??? On 9 mrt, 23:29, polaar [EMAIL PROTECTED] wrote: Yes, but I'm starting to think I'm doing something wrong ;-) I suppose I should call create_engine with the module=pyodbc? I was just using the creator argument (as I was doing already because I needed to change the connectionstring to use integrated security anyway), and just switched that from adodbapi to pyodbc. So maybe it's still using the default adodbapi settngs... Hmm, seems to make sense... oops... (well, it's not really clear from the docs that this is used for anything else than determining which module to use to create the connection, which seems unnecessary if you create it yourself) I'll try it on monday... On 9 mrt, 22:08, Rick Morrison [EMAIL PROTECTED] wrote: This is still with pyodbc? The MSSQL module should already set sane_rowcount to False for that dialect, as per the pyodbc site, they don't implement rowcount. Rick --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
Hmmm, seems the set nocount on trick now causes problems on deletes: ConcurrentModificationError is thrown because Updated rowcount -1 does not match number of objects updated 1. Which seems strange because I thought rowcount -1 simply meant that the count cannot be determined, not that there is something wrong. There seems to be a supports_sane_rowcount check (for MySQL according to the docs), wouldn't it make sense to treat a rowcount of -1 the same? Or should one just never use set nocount on when using the orm (which would mean back to the original problem)? Steven On Mar 7, 5:28 pm, polaar [EMAIL PROTECTED] wrote: On Mar 7, 3:29 pm, Tim Golden [EMAIL PROTECTED] wrote: code from sqlalchemy import * metadata = BoundMetaData (mssql://VODEV1/TimHolding) test = Table (test, metadata, autoload=True) result = test.insert ().execute (code = ABC) print result.last_inserted_ids () # = [1] /code which is what I expected. If I explicitly set NOCOUNT OFF for my session (in case it's on by default) using: metadata.engine.raw_connection ().execute (SET NOCOUNT OFF) then it still works. Is my case the situation you're describing? Or have I misunderstood somthing? My fault: I forgot to tell you that I was using a mapped class, and it's the sqlalchemy-generated 'select @@identity' that causes the problem. (you can see that it does that in the log output) --~--~-~--~~~---~--~~ 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: pyodbc and tables with triggers
On Mar 7, 3:29 pm, Tim Golden [EMAIL PROTECTED] wrote: code from sqlalchemy import * metadata = BoundMetaData (mssql://VODEV1/TimHolding) test = Table (test, metadata, autoload=True) result = test.insert ().execute (code = ABC) print result.last_inserted_ids () # = [1] /code which is what I expected. If I explicitly set NOCOUNT OFF for my session (in case it's on by default) using: metadata.engine.raw_connection ().execute (SET NOCOUNT OFF) then it still works. Is my case the situation you're describing? Or have I misunderstood somthing? My fault: I forgot to tell you that I was using a mapped class, and it's the sqlalchemy-generated 'select @@identity' that causes the problem. (you can see that it does that in the log output) --~--~-~--~~~---~--~~ 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] pyodbc and tables with triggers
Hi,
I recently tried out sqlalchemy with mssql via pyodbc (after being
bitten by the adodbapi bug with the truncated parameters), and noticed
the following problem:
On inserting records into tables with triggers, pyodbc fails on the
'select @@identity as lastrowid' statement with an 'invalid cursor
state' error.
I've managed to work around this by doing set nocount on when
creating the connection, via a creator like this:
def create_connection():
conn = pyodbc.connect(connectionstring)
cur = conn.cursor()
cur.execute('set nocount on')
cur.close()
return conn
I don't know if there's a better way, but it seems to work for me. I
just mention it because it might be a useful tip, and also because it
seems like something that should be handled by the sqlalchemy mssql
driver?
greetings,
Steven
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
