[sqlalchemy] [ANN] - Monetdb backend
Folks- Just thought I'd spam the list here (in case anyone who hasn't seen my IRC spam recently). I'm working on a monetdb[0] backend for SQLAlchemy. Monetdb is a column oriented db[1]. Basically that means it's not fast at transactions per se, but can be 10+X faster on select queries. So if you are messing with reports or cubes you might be interested. Here's a paper showing ~4X increase over oracle (using 1 AthlonMP 1.5Ghz on monetdb and a 16 CPU Itanium Oracle machine) [2], and a 7X improvement over Mysql. (They also have another new db kernel that runs everything in the CPU cache and gives another 7X performance boost over standard Monetdb, but that isn't generally available... keep your fingers crossed). I just thought I'd plug it, since it appears that many haven't heard of column oriented dbs, or Monetdb (it's open source too!). My code is attached to ticket 874[3]. Disclaimer, this is a pre alpha backend. I'm still getting some 24 failures on the SQL testcases alone. But for some stuff it might be useable. (Don't expect much/any ORM stuff to work yet). WRT Monetdb itself, the developers have been a pleasure to work with. They are quick to respond to questions and fix bugs(almost as fast as the SA folks ;)). I'd recommend using their nightly build script, if you are running Linux, it's one command to build it. (Plus there are probably some recent fixes that you'd want (the python client had a few bugs that have been fixed in the nightlies)). Give it a try if you're in the market for some speedy queries. -matt ps - Random trivia: Monetdb hails from the same labs that guido/python sprang from ;) [0] - http://monetdb.cwi.nl/ [1] - http://en.wikipedia.org/wiki/Column-oriented_DBMS [2] - http://pages.cs.wisc.edu/~cs764-1/monetdbx100.pdf [3] - http://www.sqlalchemy.org/trac/ticket/874 --~--~-~--~~~---~--~~ 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: FYI: SQLAutocode 0.4.1 and 0.5 released
On Nov 13, 2007 5:14 AM, Simon Pamies [EMAIL PROTECTED] wrote:
Hi,
I'm very pleased to announce the release of SQLAutocode 0.4.1 and
0.5. This tool enables SQLAlchemy users to automagically generate
python code from an existing database layout and even has the
Hey Simon, I found your code today and started to hack on it to do db
migrations. Since it is out of the scope of your project, I'll
probably just write my own. But before I came to that conclusion, I
did some cleaning up of the autocode.py. Feel free to accept it if
you want. Here's the overview of the changes in the attached diff:
* Remove import * - I know SA does this all over the place, but when
you have various projects that you are doing this with (ie both
autocode and SA) it makes it difficult to know what came from where.
Especially if you are an outside developer
* added a _main() function
* changed getopt to optparse
* added newlines after else:
* changed if foo is True: to if foo:
cheers,
matt
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
=== modified file 'autocode.py'
--- autocode.py 2007-11-13 22:18:59 +
+++ autocode.py 2007-11-13 23:34:54 +
@@ -1,104 +1,93 @@
-
import sys
-import sqlalchemy
-if sqlalchemy.__version__ != 'svn':
-if sqlalchemy.__version__.split('.')[1] != '4':
+import os
+import optparse
+
+
+import sqlalchemy as sa
+if sa.__version__ != 'svn':
+if sa.__version__.split('.')[1] != '4':
print 'Not compatible with this version of SQLAlchemy! Only works for
the 0.4.x line!'
sys.exit(7)
-del sqlalchemy
-
-
-from sqlalchemy import engine, MetaData
-
import constants
-from loader import *
-from formatter import *
-
-if __name__ == '__main__':
-
-import sys, getopt, os
-
-args, longargs = ('hu:o:s:t:i3e', ['help', 'url=', 'output=', 'schema=',
'tables=', 'noindex', 'z3c', 'example'])
-
-try:
-optlist, args = getopt.getopt(sys.argv[1:], args, longargs)
-except getopt.GetoptError:
-print sys.stderr, 'Error: Unknown arguments.'
-print sys.stderr, constants.USAGE
-sys.exit(255)
-
-if len(optlist)==0:
-print sys.stderr, 'Error: No arguments passed.'
-print sys.stderr, constants.USAGE
-sys.exit(0)
-
-url, output, schema, tables, \
-filehandle, noindex, \
-example, z3c = (None, None, None, None, None, None, False, False)
-for opt, arg in optlist:
-if opt in ['-h', '--help']:
-print sys.stderr, constants.USAGE
-sys.exit(0)
-
-if opt in ['-u', '--url']:
-url = arg
-
-if opt in ['-i', '--noindex']:
-noindex = True
-
-if opt in ['-e', '--example']:
-example = True
-
-if opt in ['-3', '--z3c']:
-z3c = True
-constants.TAB = 26*' '
-
-if opt in ['-o', '--output']:
-output = arg
-
-if os.path.exists(output):
-print sys.stderr, 'Output file exists - it will be
overwritten!'
-resp = raw_input('Overwrite (Y/N): ')
-if resp.strip().lower() != 'y':
-print Aborted.
-sys.exit(0)
-
-else: os.unlink(output)
-
-filehandle = open(output, 'wU')
-
-if opt in ['-s', '--schema']:
-schema = arg.strip()
-
-if opt in ['-t', '--tables']:
-tables = arg.split(',')
+import loader
+import formatter
+
+def _main(prog_args=None):
+if prog_args is None:
+prog_args = sys.argv
+
+parser = optparse.OptionParser(Generates python code for a given
database schema.)
+
+parser.add_option(-u, --url,
+ help=Database url (e.g.: postgres://postgres:[EMAIL
PROTECTED]/Database),
+ action=store, dest=url, default=None)
+parser.add_option(-o, --output,
+ help=Where to put the output (default is stdout),
+ action=store, dest=output, default=None)
+parser.add_option(-s, --schema,
+ help=Name of the schema to output (default is
'default'),
+ action=store, dest=schema, default=None)
+parser.add_option(-t, --tables,
+ help=Name of tables to inspect (default is 'all').
Support globbing character to select more tables. ex.: -t Download* will
generate a model for all tables starting with Download,
+ action=store, dest=tables, default=None)
+parser.add_option(-i, --noindex,
+
[sqlalchemy] Re: FYI: SQLAutocode 0.4.1 and 0.5 released
On Nov 13, 2007 4:44 PM, m h [EMAIL PROTECTED] wrote: On Nov 13, 2007 5:14 AM, Simon Pamies [EMAIL PROTECTED] wrote: Hi, I'm very pleased to announce the release of SQLAutocode 0.4.1 and 0.5. This tool enables SQLAlchemy users to automagically generate python code from an existing database layout and even has the Hey Simon, I found your code today and started to hack on it to do db migrations. Since it is out of the scope of your project, I'll probably just write my own. But before I came to that conclusion, I did some cleaning up of the autocode.py. Feel free to accept it if you want. Here's the overview of the changes in the attached diff: * Remove import * - I know SA does this all over the place, but when you have various projects that you are doing this with (ie both autocode and SA) it makes it difficult to know what came from where. Especially if you are an outside developer * added a _main() function * changed getopt to optparse * added newlines after else: * changed if foo is True: to if foo: cheers, matt I should note that I didn't test all the options... I might have introduced some bugs in the optparse conversion. --~--~-~--~~~---~--~~ 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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
On 9/26/07, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 25, 2007, at 7:30 PM, m h wrote: Have updated the ticket with my fix change the dbapi_type of OracleText from CLOB to NUMBER I'm not sure if this breaks others code the bug is, the type of the bind parameters should be coming out as VARCHAR, not CLOB. NUMBER is definitely wrong. perchance, is the type of the column cal_dim.c.adwkenddt a CLOB ? (i.e. String with no length?) you didnt put the table on the ticket so i actually cannot test it (its an incomplete test case). just add a length and the whole thing should work. Nope, not a CLOB, it's a DATE field. --~--~-~--~~~---~--~~ 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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Have updated the ticket with my fix change the dbapi_type of OracleText from CLOB to NUMBER I'm not sure if this breaks others code http://www.sqlalchemy.org/trac/ticket/793 --~--~-~--~~~---~--~~ 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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Here's another simple testcase that fails for Oracle beta5 but works
with 0.3.7. It's about the simplest query I can come up with. Have a
table with a date column in it and query against it using the
to_date function.
def test_to_date():
start_date = '10/05/04'
where = cal_dim.c.adwkenddt == func.to_date(start_date,'MM/DD/RR')
query = select([cal_dim.c.adwkenddt],
whereclause=where
)
result = query.execute()
for r in result:
print r
This fails for beta5, works for .3.7. The error is::
Traceback (most recent call last):
File s4.py, line 88, in ?
test_to_date()
File s4.py, line 59, in test_to_date
result = query.execute()
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 973, in execute
return compiled.execute(*multiparams, **params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT AD_WEEK_CALENDAR_DIM.adwkenddt \nFROM
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt =
to_date(:to_date, :to_date_1)' {'to_date_1': 'MM/DD/RR', 'to_date':
'10/05/04'}
Am still confused as to the problem here I run the same query
through text and it works::
def test_to_date_text():
s = text(SELECT AD_WEEK_CALENDAR_DIM.adwkenddt
FROM AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt = to_date(:to_date, :to_date_1))
result = connection.execute(s,
to_date_1= 'MM/DD/RR',
to_date= '10/05/04')
for r in result:
print r
--~--~-~--~~~---~--~~
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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
NP, I know Oracle is a drag but that's what the client has.
Perhaps if you could clarify why I'm confused I might be able to help
debug. (I've stepped through expression.py all day long). I feel
like I just need a little hint or push in the right direction.
Eventually you get to a cx cursor and call execute with the statement
on it. How does the same statement/parameter combo fail with SQL
generate, yet succeed using `text` or plain cx? If I can get over
that hump, I think I might be able to give you a patch.
On 9/24/07, Michael Bayer [EMAIL PROTECTED] wrote:
sorry, i havent forgotten you. just have to get the time to power
up my oracle box. thanks for putting in the ticket.
On Sep 24, 2007, at 7:22 PM, m h wrote:
Here's another simple testcase that fails for Oracle beta5 but works
with 0.3.7. It's about the simplest query I can come up with. Have a
table with a date column in it and query against it using the
to_date function.
def test_to_date():
start_date = '10/05/04'
where = cal_dim.c.adwkenddt == func.to_date(start_date,'MM/DD/RR')
query = select([cal_dim.c.adwkenddt],
whereclause=where
)
result = query.execute()
for r in result:
print r
This fails for beta5, works for .3.7. The error is::
Traceback (most recent call last):
File s4.py, line 88, in ?
test_to_date()
File s4.py, line 59, in test_to_date
result = query.execute()
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 973, in execute
return compiled.execute(*multiparams, **params)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT AD_WEEK_CALENDAR_DIM.adwkenddt \nFROM
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt =
to_date(:to_date, :to_date_1)' {'to_date_1': 'MM/DD/RR', 'to_date':
'10/05/04'}
Am still confused as to the problem here I run the same query
through text and it works::
def test_to_date_text():
s = text(SELECT AD_WEEK_CALENDAR_DIM.adwkenddt
FROM AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt = to_date
(:to_date, :to_date_1))
result = connection.execute(s,
to_date_1= 'MM/DD/RR',
to_date= '10/05/04')
for r in result:
print r
--~--~-~--~~~---~--~~
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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Thanks for the response! On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote: well the issue is not even the bind params its the result coming back from cx_oracle. Again, I'm confused how this query works from straight cx and .3.7 but fails when .4beta calls it. Could you enlighten me on what I'm missing? its not well documented/consistent which oracle types come back as a LOB and also come back with a cx_oracle LOB object, and which ones do not. its possible that just upgrading your cx_oracle will fix the issue here...see if you can try that first (and possibly identify which column in that SQL statement might be LOB-like). Upgraded to cx_Oracle 4.3.1 from 4.2 and got the same error. All the varchar2 columns have sizes (between 2 and 50) Also keep in mind if you are using any String column types without a length, they come back as LOBs as well. As I said above it appears that all the strings come with lengths. But doesn't the error indicate that a string (CLOB/VARCHAR) appeared where a NUMBER was expected? So to one who doesn't have much experience with the SA code, I would think that somehow one of the numbers or aggregates are getting confused and getting turned into CLOBS somehow... (DatabaseError) ORA-00932: inconsistent datatypes: expected NUMBER got CLOB the last resort will be that we just need to add an isinstance() check into convert_result_value to ensure that all LOBs get the read () call and all strings do not. Hmmm, this appears to be a regression since .3.7 worked I'm assumming new CLOB code somewhere is overeager in converting to CLOBs. You mentioned previously that this is an error on the coming back into SA side. I'm not sure how that is possible if the error is thrown on the cursor.execute. How will convert_result_value do anything if the error is coming from cxOracle before SA even has a result to look at? Thanks again for the response! -matt --~--~-~--~~~---~--~~ 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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
I guess to help validate my confusion, in my debugging I replaced the line 867 in base.py:: raise exceptions.DBAPIError.instance(statement, parameters, e) with:: raise Then it doesn't give me the useful statement and parameters, but it tells me exactly where it failed:: ... File /home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py, line 850, in __execute_raw self._cursor_execute(context.cursor, context.statement, parameters, context=context) File /home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py, line 858, in _cursor_execute self.dialect.do_execute(cursor, statement, parameters, context=context) File /home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/default.py, line 117, in do_execute cursor.execute(statement, parameters) cx_Oracle.DatabaseError: ORA-00932: inconsistent datatypes: expected NUMBER got CLOB So the failure is when CursorFairy executes the execute method on the cx cursor. Where does the SA converting returned types come into play on this? thanks -matt --~--~-~--~~~---~--~~ 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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Sorry, my mess of emails appears to have confused you (youre looking at the error with .3.10 not .4beta) On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 22, 2007, at 3:45 PM, m h wrote: So the failure is when CursorFairy executes the execute method on the cx cursor. Where does the SA converting returned types come into play on this? because you sent me this stack trace: File /home/matt/work/vpython/lib/python2.4/site-packages/ SQLAlchemy-0.3.10-py2.4.egg/sqlalchemy/databases/oracle.py, line 117, in convert_result_value return value.read() AttributeError: 'str' object has no attribute 'read' that line of code is only called within result-set processing, well after any statement execution occurs. Sorry, that was the error when I tried (just for fun with .3.10) now youre talking about getting an exception upon execute, which means the above stack trace should be totally impossible to get. Also, youre saying that removing the DBAPI adaptation somehow made the oracle error visible where it was not earlier, which is also strange; so I'd at least advise using the latest trunk and not beta5 since i think we might have made some corrections to the exception handling. Hmmm, this appears to be a regression since .3.7 worked I'm assumming new CLOB code somewhere is overeager in converting to CLOBs. we arent converting CLOBs or anything like that. the only thing that is different from 0.3.7 to now is that we do a little bit of conversion of Numeric types to Decimal objects. so the new error youre getting now puts it back in the execution camp. you need to narrow down the query to the specific column expression and/or parameter thats making it break. --~--~-~--~~~---~--~~ 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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote:
so the new error youre getting now puts it back in the execution
camp. you need to narrow down the query to the specific column
expression and/or parameter thats making it break.
Here's a simple query that fails. The deal breaker is the between
function for dates. When I add the between the query fails (note that
it's a stupid query but it gives the error)...
AD_WEEK_CALENDAR_DIM table
ADWKENDDT type: DATE
PRODUCT_DIM table
---
DEPTNM type: VARCHAR2(30)
::
#python code
#..set username, password, etc
engine = create_engine(oracle://%s:[EMAIL PROTECTED]:1521/%s%(u, p, host, db))
connection = engine.connect()
meta = MetaData(engine)
cal_dim = Table('AD_WEEK_CALENDAR_DIM', meta, autoload=True)
prod_dim = Table('PRODUCT_DIM', meta, autoload=True)
def fail_query():
start_date = '12/26/03'
end_date = '12/30/03'
where =
and_(cal_dim.c.adwkenddt.between(func.to_date(start_date,'MM/DD/RR'),
func.to_date(end_date, 'MM/DD/RR'))
)
query = select([prod_dim.c.deptnm.label(department)],
whereclause=where
)
compiled = query.compile()
result = query.execute()
for r in result:
print r
fail_query()
Here's the error:
Traceback (most recent call last):
Traceback (most recent call last):
File s4.py, line 43, in ?
fail_query()
File s4.py, line 39, in fail_query
result = query.execute()
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 971, in execute
return self.compile(bind=self.bind, parameters=compile_params,
inline=(len(multiparams) 1)).execute(*multiparams, **params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT PRODUCT_DIM.deptnm AS department \nFROM PRODUCT_DIM,
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt
BETWEEN to_date(:to_date, :to_date_1) AND to_date(:to_date_2,
:to_date_3)' {'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR',
'to_date_1': 'MM/DD/RR', 'to_date': '12/26/03'}
Here's the log statement/params::
SELECT PRODUCT_DIM.deptnm AS department
FROM PRODUCT_DIM, AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt BETWEEN to_date(:to_date,
:to_date_1) AND to_date(:to_date_2, :to_date_3)
{'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR', 'to_date_1':
'MM/DD/RR', 'to_date': '12/26/03'}
--~--~-~--~~~---~--~~
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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Ok, it's caused by func.to_date. Here's an even simpler testcase.
Try to convert a date to a string::
def test2():
start_date = '12/26/03'
query = select([func.to_date(cal_dim.c.adwkenddt,'MM/DD/RR')])
compiled = query.compile()
result = query.execute()
for r in result:
print r
On 9/22/07, m h [EMAIL PROTECTED] wrote:
On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote:
so the new error youre getting now puts it back in the execution
camp. you need to narrow down the query to the specific column
expression and/or parameter thats making it break.
Here's a simple query that fails. The deal breaker is the between
function for dates. When I add the between the query fails (note that
it's a stupid query but it gives the error)...
AD_WEEK_CALENDAR_DIM table
ADWKENDDT type: DATE
PRODUCT_DIM table
---
DEPTNM type: VARCHAR2(30)
::
#python code
#..set username, password, etc
engine = create_engine(oracle://%s:[EMAIL PROTECTED]:1521/%s%(u, p, host,
db))
connection = engine.connect()
meta = MetaData(engine)
cal_dim = Table('AD_WEEK_CALENDAR_DIM', meta, autoload=True)
prod_dim = Table('PRODUCT_DIM', meta, autoload=True)
def fail_query():
start_date = '12/26/03'
end_date = '12/30/03'
where =
and_(cal_dim.c.adwkenddt.between(func.to_date(start_date,'MM/DD/RR'),
func.to_date(end_date,
'MM/DD/RR'))
)
query = select([prod_dim.c.deptnm.label(department)],
whereclause=where
)
compiled = query.compile()
result = query.execute()
for r in result:
print r
fail_query()
Here's the error:
Traceback (most recent call last):
Traceback (most recent call last):
File s4.py, line 43, in ?
fail_query()
File s4.py, line 39, in fail_query
result = query.execute()
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 971, in execute
return self.compile(bind=self.bind, parameters=compile_params,
inline=(len(multiparams) 1)).execute(*multiparams, **params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT PRODUCT_DIM.deptnm AS department \nFROM PRODUCT_DIM,
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt
BETWEEN to_date(:to_date, :to_date_1) AND to_date(:to_date_2,
:to_date_3)' {'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR',
'to_date_1': 'MM/DD/RR', 'to_date': '12/26/03'}
Here's the log statement/params::
SELECT PRODUCT_DIM.deptnm AS department
FROM PRODUCT_DIM, AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt BETWEEN to_date(:to_date,
:to_date_1) AND to_date(:to_date_2, :to_date_3)
{'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR', 'to_date_1':
'MM/DD/RR', 'to_date': '12/26/03'}
--~--~-~--~~~---~--~~
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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Disregard this testcase it's late at night no need to convert
a date to a date but the previous testcase fails for .4beta and
works for .3.7!!!
On 9/22/07, m h [EMAIL PROTECTED] wrote:
Ok, it's caused by func.to_date. Here's an even simpler testcase.
Try to convert a date to a string::
def test2():
start_date = '12/26/03'
query = select([func.to_date(cal_dim.c.adwkenddt,'MM/DD/RR')])
compiled = query.compile()
result = query.execute()
for r in result:
print r
On 9/22/07, m h [EMAIL PROTECTED] wrote:
On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote:
so the new error youre getting now puts it back in the execution
camp. you need to narrow down the query to the specific column
expression and/or parameter thats making it break.
Here's a simple query that fails. The deal breaker is the between
function for dates. When I add the between the query fails (note that
it's a stupid query but it gives the error)...
AD_WEEK_CALENDAR_DIM table
ADWKENDDT type: DATE
PRODUCT_DIM table
---
DEPTNM type: VARCHAR2(30)
::
#python code
#..set username, password, etc
engine = create_engine(oracle://%s:[EMAIL PROTECTED]:1521/%s%(u, p, host,
db))
connection = engine.connect()
meta = MetaData(engine)
cal_dim = Table('AD_WEEK_CALENDAR_DIM', meta, autoload=True)
prod_dim = Table('PRODUCT_DIM', meta, autoload=True)
def fail_query():
start_date = '12/26/03'
end_date = '12/30/03'
where =
and_(cal_dim.c.adwkenddt.between(func.to_date(start_date,'MM/DD/RR'),
func.to_date(end_date,
'MM/DD/RR'))
)
query = select([prod_dim.c.deptnm.label(department)],
whereclause=where
)
compiled = query.compile()
result = query.execute()
for r in result:
print r
fail_query()
Here's the error:
Traceback (most recent call last):
Traceback (most recent call last):
File s4.py, line 43, in ?
fail_query()
File s4.py, line 39, in fail_query
result = query.execute()
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 971, in execute
return self.compile(bind=self.bind, parameters=compile_params,
inline=(len(multiparams) 1)).execute(*multiparams, **params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT PRODUCT_DIM.deptnm AS department \nFROM PRODUCT_DIM,
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt
BETWEEN to_date(:to_date, :to_date_1) AND to_date(:to_date_2,
:to_date_3)' {'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR',
'to_date_1': 'MM/DD/RR', 'to_date': '12/26/03'}
Here's the log statement/params::
SELECT PRODUCT_DIM.deptnm AS department
FROM PRODUCT_DIM, AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt BETWEEN to_date(:to_date,
:to_date_1) AND to_date(:to_date_2, :to_date_3)
{'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR', 'to_date_1':
'MM/DD/RR', 'to_date': '12/26/03'}
--~--~-~--~~~---~--~~
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] 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Ok, so this looks impossible to me, but it's happening. I figure people with more experience debugging SA might be able to help. I've got a generated query (no ORM). I was using 0.3.7 with no problems, but thought I'd give .4 a try to see if I can take advantage of performance improvements. From reading the migration documentation it appeared all I really needed to do was change:: meta = BoundMetaData(engine) to:: meta = MetaData(engine) So I did that and now when I run my query now it throws an error:: ... File /home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py, line 867, in _cursor_execute raise exceptions.DBAPIError.instance(statement, parameters, e) sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932: inconsistent datatypes: expected NUMBER got CLOB In my log file, it appears that the statement generated for 0.3.7 and 0.4.0beta5 is the same. The parameters differ a little. One key is somehow converted (incorrectly) to a float instead of a string (like it was in 0.3.7). I thought that might be the problem, but that is the opposite of what the error message is. So after digging/stepping through SA for a bit (hey, at least I learned how to use pdb from emacs!), I didn't see anything useful. Then I tried running the query using only cx_Oracle. I used the same statement and parameter from the 0.4.beta5 version and lo and behold, it worked!?!?! (I even changed the parameter that was a float to a string and it still worked...) So now I'm just confused. I'm not sure how the same query can work from straight cx_Oracle, but fails with the _CursorFairy tries to execute it... Has something/much changed with Oracle since 0.3.7 (I know it's ~6 months old)? The Changelog [0] lists oracle improvements in beta2, auto_convert_lobs in 0.3.9 Could these be responsible? Any hints or suggestions would be great! Thanks much. I'm just confused right now, and would like to leave that state ;) -matt 0 - http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/CHANGES --~--~-~--~~~---~--~~ 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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Just for kicks I tried using .3.10 and it failed giving the same error that was reported in the Aug 18 thread autoloading Oracle tables with column defaults:: File /home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.3.10-py2.4.egg/sqlalchemy/databases/oracle.py, line 117, in convert_result_value return value.read() AttributeError: 'str' object has no attribute 'read' I can stay with .3.7, but I'm just wondering what the deal is with beta5. --~--~-~--~~~---~--~~ 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] Viewing SQL of literals....
Hey-
I think this is a simple question but I couldn't find it in the docs
(or wiki or faq).
I'm trying to debug a query that has some string literals in it (ie
calendar.c.week.between(func.to_date('09/19/06','MM/DD/RR')-84,func.to_date('09/19/06','MM/DD/RR')
)
and when I view the sql (via print str(select_statement)), I get:
CALENDAR.week BETWEEN to_date(:to_date, :to_date_1) - :to_date_2 AND
to_date(:to_date_3, :to_date_4)
So how do I view the dict containing :to_date[n]?
thanks
matt
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
