[sqlalchemy] Re: How to deal with non-ASCII(such as Chinese)table name?
I mean non-ascii column names defined in my database table.
SqlServer, Oracle, MySql...all of the mainstream DBMS support this
feature,
In east asia(China, Japan, Korea), non-ascii column names and table
names,together with non-ascii strings in record
are widely used.
On 3月25日, 下午10时55分, Michael Bayer [EMAIL PROTECTED] wrote:
non-ascii attribute names in your Python code ? or non-ascii column
names defined in your database table ? I didnt know the latter was
possible with most databases.
On Mar 25, 2007, at 1:04 AM, Chen Houwu wrote:
from the sqlalchemy documentation,
I found when table definition
(e.g
users_table = Table('users', metadata,
... Column('user_id', Integer, primary_key=True),
... Column('user_name', String(40)),
... Column('password', String(10))
... )
)
and a class
(e.g.
class User(object):
... pass
)
are mapped together,
the column name in the column definition(e.g. 'password') are mapped
into an attribute of the class,
but python only accept ASCII attribute name.
so,
What if the above password is a word in Chinese GB2312 encoding?
Does it mean I can't deal with column name which is not ASCII?
--~--~-~--~~~---~--~~
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: Fix on ADODBAPI
Ok I saw fixes in the current trunk:
class MSSQLDialect_pymssql(MSSQLDialect):
def make_connect_string(self, keys):
if keys.get('port'):
# pymssql expects port as host:port, not a separate arg
keys['host'] = ''.join([keys.get('host', ''), ':',
str(keys['port'])])
del keys['port']
return [[], keys]
I guess the right separator is not ':' but ','. G just a way from
MS to disagree from standarts.
--~--~-~--~~~---~--~~
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: [PATCH] Using entry points to load database dialects
dialects can be used on their own without the engine being present
(such as, to generate SQL), also you can construct an engine passing
in your own module object which might have been procured from
somewhere else (or could be a mock object,for example).
On Mar 26, 2007, at 11:45 PM, Monty Taylor wrote:
Always one in every bunch. :)
I hear what you're saying about the import errors. But does it really
help to allow work to get done before throwing the error? I would
think
you'd want to know right up front if you don't have a driver loaded
rather then letting a program actually get started up and think you
can
write data (think fat client app) only to get a connection exception.
But I, of course, could be very wrong about this. I am about many
things...
Monty
Michael Bayer wrote:
yeah i dont like setup.py develop either :)but anyway, patch is
good. one thing i have to nail down though is ticket #480. the
main point of that ticket is to cleanly isolate ImportErrors of
actual DBAPI modules apart from the containing dialect module
itself. the dialects are catching all the DBAPI-related
ImportErrors though so its not necessarily blocking this patch (its
just they cant report them nicely).
On Mar 26, 2007, at 1:34 PM, Monty Taylor wrote:
Michael Bayer wrote:
i think using entry points to load in external database dialects
is a
great idea.
though the current six core dialects i think i still want to
load via
__import__ though since im a big fan of running SA straight out of
the source directory (and therefore thered be no entry points for
those in that case).
so probably a check via __import__('sqlalchemy.databases') first,
then an entry point lookup. does that work ?
Here is a patch that implements use of entry points to load
dialects.
The largest change is actually adding a get_dialect to replace the
functionality of get_module, since entry points really want to
return
classes, and we only ever use the dialect class from the returned
module
anyway...
This does not break code that I have that loads the mysql
dialect, and
it does work with my new code that adds a new dialect - although I
suppose it's possible it could have broken something I didn't find.
As a side note, I agree with Gaetan - you can run entry points and
stuff
out of the current directory, especially if you use setup.py
develop ...
but this code does the entry points second, after a check for the
module
the old way.
Monty
=== modified file 'lib/sqlalchemy/engine/strategies.py'
--- lib/sqlalchemy/engine/strategies.py 2007-02-25 22:44:52 +
+++ lib/sqlalchemy/engine/strategies.py 2007-03-26 17:03:13 +
@@ -42,16 +42,16 @@
u = url.make_url(name_or_url)
# get module from sqlalchemy.databases
-module = u.get_module()
+dialect_cls = u.get_dialect()
dialect_args = {}
# consume dialect arguments from kwargs
-for k in util.get_cls_kwargs(module.dialect):
+for k in util.get_cls_kwargs(dialect_cls):
if k in kwargs:
dialect_args[k] = kwargs.pop(k)
# create dialect
-dialect = module.dialect(**dialect_args)
+dialect = dialect_cls(**dialect_args)
# assemble connection arguments
(cargs, cparams) = dialect.create_connect_args(u)
@@ -71,7 +71,7 @@
raise exceptions.DBAPIError(Connection
failed, e)
creator = kwargs.pop('creator', connect)
-poolclass = kwargs.pop('poolclass', getattr(module,
'poolclass', poollib.QueuePool))
+poolclass = kwargs.pop('poolclass', getattr
(dialect_cls, 'poolclass', poollib.QueuePool))
pool_args = {}
# consume pool arguments from kwargs, translating a
few of the arguments
for k in util.get_cls_kwargs(poolclass):
=== modified file 'lib/sqlalchemy/engine/url.py'
--- lib/sqlalchemy/engine/url.py2007-03-18 22:35:19 +
+++ lib/sqlalchemy/engine/url.py2007-03-26 16:47:01 +
@@ -2,6 +2,7 @@
import cgi
import sys
import urllib
+import pkg_resources
from sqlalchemy import exceptions
Provide the URL object as well as the make_url parsing
function.
@@ -69,6 +70,23 @@
s += '?' + .join([%s=%s % (k, self.query[k]) for
k in keys])
return s
+def get_dialect(self):
+Return the SQLAlchemy database dialect class
corresponding to this URL's driver name.
+dialect=None
+try:
+ module=getattr(__import__('sqlalchemy.databases.%s' %
self.drivername).databases, self.drivername)
+ dialect=module.dialect
+except ImportError:
+if sys.exc_info()[2].tb_next is None:
+ for res in pkg_resources.iter_entry_points
('sqlalchemy.databases'):
+if res.name==self.drivername:
+ dialect=res.load()
+
[sqlalchemy] Re: deferred join against details table in polymorphic union for performance
On Mar 27, 2007, at 2:08 AM, chris e wrote: specific to the identity of the object. Is there currently a way, or a plan to support, splitting the polymorphic query into two queries? The first would get the base table, the second would retrieve the details based on the discovered table. This way only two tables would be queried instead of n where n is the number of polymorphic identities. what if you are querying for 10 objects that are of different polymorphic identities ?I do have some interest in expanding the mapper architecture to allow a single Query operation to result in multiple immediate select statements instead of one, this is how Hibernate usually does it in the default case. I hadnt yet noticed how hibernate does joined-table inheritance, if it is breaking up the table queries in the way that you mention. As yet I havent delved into how such an architecture could be added and it would be a very complicated refactoring. you can of course perform an operation like this manually - just query the primary table yourself, then issue the join against the two tables into instances(), or query against the specific subclass you've identified.if you have set up polymorphic loaders at every level, one useful thing on SA's side would be to allow a polymorphic=False option to Query to disable the polymorphic load. Our DBAs have concerns that as our tables grow, possibly to the size of 2.5million rows, that unioning against multiple tables, despite the fact that we are unioning against a primary key, will become non- performant. I know I could write a custom mapper to resolve this issue, however, I thought I would bring this up since it may affect other users, and there may already be a way to solve this easily of which I am not aware. i think their concerns are valid, i wouldnt rely heavily on polymorphic loads for a high volume situation. but in the bigger sense, id be concerned about a complicated schema of joined table inheritances being able to scale very well in any case regardless of ORM (this goes to the slogan on SA's homepage...DBs act less and less like object collections the more performance starts to matter...). --~--~-~--~~~---~--~~ 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: backref VS relation on both sides
didnt get your attachment. but yes, they are different. during a
many-to-many flush() operation, the two relationships know to
communicate which one actually dealt with the row in the many-to-many
table, without it, youll get duplicate association rows.
additionally, the automatic management of the bi-directional
attribute in Python tends to smooth out operations significantly,
although two separate attributes should still work. but keep in mind
if you dont set up backref, you have to manually assign to each
collection in both directions. i dont know why youd want to do it
that way.
On Mar 27, 2007, at 7:40 AM, Gaetan de Menten wrote:
Hi list,
A while ago, someone came up with a problem on the Elixir list. The
problem is that in elixir we force users to define relationships on
both sides of the relationship (ie, there is no concept of backref).
And I (mistakenly) thought that having two mappers with relations to
each other was the same as having one mapper with a relation to the
other and a backref, so I didn't define backrefs for those
relationships.
It seems like, in fact, it's not exactly the same: the difference seem
to be with the automatic update of the other attribute (the backref)
*even before a flush is done*, as demonstrated at:
http://www.sqlalchemy.org/docs/
datamapping.html#datamapping_relations_backreferences
Now the question is: shouldn't these two ways to define relations
be equivalent?
Attached is a test case, demonstrating that the second way doesn't
work (in case it can be of any use).
BTW: there are several mistakes in the example there.
Address = mapper(Address, addresses_table)
User = mapper(User, users_table, properties = {
'addresses' : relation(Address, backref='user')
}
)
should read:
mapper(Address, addresses_table)
mapper(User, users_table, properties = {
'addresses' : relation(Address, backref='user')
}
)
and
a1.user is user and a2.user is user
True
should be:
a1.user is u and a2.user is u
True
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
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] query stopped working in 0.3.6
I upgraded to 0.3.6 from 0.3.5 and one of my querys stopped working:
s =
model_table.select(~(model_table.c.therapeutic_area.in_('Respiratory','Diabetes',
'Inflammation','CVD')),
order_by=[model_table.c.model_acronym])
All my other query still work fine. Although this is the only one
using in_() and ~.
The funny thing is that the created sql appears to be exactly the same
as before. Here is the error:
2007-03-26 15:11:20,851 INFO sqlalchemy.engine.base.Engine.0x..d0
SELECT model.i
nvestigator_isid, model.active, model.model_id, model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,851 sqlalchemy.engine.base.Engine.0x..d0 INFO
SELECT model.i
nvestigator_isid, model.active, model.model_id, model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
ROLLBACK
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
ROLLBACK
Traceback (most recent call last):
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-project
\start-sr
t.py, line 23, in ?
from srt.controllers import Root
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-project
\srt\cont
rollers.py, line 9, in ?
model_list = [('Respiratory',[('','Please select a model')]
+dbmodel.model_li
st('Respiratory')),
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-project
\srt\mode
l.py, line 158, in model_list
rs = s.execute()
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\sql.
py, line 776, in execute
return self.compile(engine=self.engine,
parameters=compile_params).execute(*
multiparams, **params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\sql.
py, line 669, in execute
return e.execute_compiled(self, *multiparams, **params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 726, in execute_compiled
return connection.execute_compiled(compiled, *multiparams,
**params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 503, in execute_compiled
proxy(str(compiled), parameters)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 499, in proxy
self._execute_raw(statement, parameters, cursor=cursor,
context=context)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 547, in _execute_raw
self._execute(cursor, statement, parameters, context=context)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 566, in _execute
raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-01460:
unimplemented or unre
asonable conversion requested
'SELECT model.investigator_isid, model.active, model.model_id,
model.model_acro
nym, model.model_name, model.therapeutic_area \nFROM model \nWHERE
model.therape
utic_area NOT IN
(:model_therapeutic_area, :model_therapeutic_ar_1, :model_thera
peutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_acronym' {'model_ther
apeutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeuti
c_ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
--~--~-~--~~~---~--~~
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: func() bug ?
On Mar 27, 2007, at 10:00 AM, Julien Cigar wrote: Hello, I'm using SQLAlchemy 0.3.5, and it seems that the func() output is broken with some functions. I use the ANY function of PostgreSQL with something like : func.any(q.c.habitats)==filter_habitat SQLAlchemy translates this in: WHERE any(habitats) = %(any)s, which is incorrect. For example: the result should be : iasdev= select true as result where 'abc' = ANY(array['abc', 'def']); result t where SQLAlchemy generates the query as : iasdev= select true as result where ANY(array['abc', 'def']) = 'abc'; ERROR: syntax error at or near ANY at character 29 LINE 1: select true as result where ANY(array['abc', 'def']) = 'abc'... Is this a bug ? (or maybe it's possible to keep the order...) ? can i have some complete code examples please ? i dont understand how your snippet would produce a full SELECT statement. the phrase youve shown me translates exactly as specified, assuming filter_habitat is ia non ClauseElement:: func.any(q.c.habitats)==filter_habitat should be: any(habitats) = %(any)s --~--~-~--~~~---~--~~ 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: deferred join against details table in polymorphic union for performance
On Tuesday 27 March 2007 17:18:04 Michael Bayer wrote: On Mar 27, 2007, at 3:53 AM, [EMAIL PROTECTED] wrote: Lets say u have 5 leaf types in 4-5 levels of the hierarchy tree, say that makes 10 tables total. say E object is a leaf and comes from A.join(B).join(C).join(E) - so E is split amongst all A B C E tables. Which is the detail table? you have to understandnobody inherits more than one level deep in almost any inheritance situation. thats just you :) yeaa. seems so. he means: select * from base_table where id=7 fetch row - type of object is A select * from joined_table_A where id = 7 fetch row, assemble into A instance - done i got it this far; but this is applicable only for single lazy relation. if i want all them for which name.starswith(abc), and some are A, some are B, some are XYZ? Then instead of 1 big huge polymoprhic thing, i have to issue n queries (where n is number of leaf types), that is all those selectables that go in the polymunion's dictionary. i'm sure this could be faster in some cases (simple means fast), but then just use non-polymorphic selectables direct and keep the polymorphism switching on the python side --- a (pseudo-)polymorphic-mapper that does polymorphism in python only but issues several direct selectables to SQL. Something of sorts? --~--~-~--~~~---~--~~ 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: query stopped working in 0.3.6
Here is the surrounding code:
def model_list(therapeutic_area='All'):
if therapeutic_area == 'All':
s = model_table.select(order_by=[model_table.c.model_acronym])
elif therapeutic_area == 'Other':
s =
model_table.select(~(model_table.c.therapeutic_area.in_('Respiratory','Diabetes',
'Inflammation','CVD')),
order_by=[model_table.c.model_acronym])
else:
s =
model_table.select(model_table.c.therapeutic_area==therapeutic_area,order_by=[model_table.c.model_acronym])
rs = s.execute()
return [(str(row['model_id']),(row['model_acronym'] + -
+row['model_name'])[:80]) for row in rs.fetchall()]
On Mar 27, 10:29 am, shday [EMAIL PROTECTED] wrote:
I upgraded to 0.3.6 from 0.3.5 and one of my querys stopped working:
s =
model_table.select(~(model_table.c.therapeutic_area.in_('Respiratory','Diabetes',
'Inflammation','CVD')),
order_by=[model_table.c.model_acronym])
All my other query still work fine. Although this is the only one
using in_() and ~.
The funny thing is that the created sql appears to be exactly the same
as before. Here is the error:
2007-03-26 15:11:20,851 INFO sqlalchemy.engine.base.Engine.0x..d0
SELECT model.i
nvestigator_isid, model.active, model.model_id, model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,851 sqlalchemy.engine.base.Engine.0x..d0 INFO
SELECT model.i
nvestigator_isid, model.active, model.model_id, model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
ROLLBACK
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
ROLLBACK
Traceback (most recent call last):
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-project
\start-sr
t.py, line 23, in ?
from srt.controllers import Root
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-project
\srt\cont
rollers.py, line 9, in ?
model_list = [('Respiratory',[('','Please select a model')]
+dbmodel.model_li
st('Respiratory')),
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-project
\srt\mode
l.py, line 158, in model_list
rs = s.execute()
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\sql.
py, line 776, in execute
return self.compile(engine=self.engine,
parameters=compile_params).execute(*
multiparams, **params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\sql.
py, line 669, in execute
return e.execute_compiled(self, *multiparams, **params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 726, in execute_compiled
return connection.execute_compiled(compiled, *multiparams,
**params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 503, in execute_compiled
proxy(str(compiled), parameters)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 499, in proxy
self._execute_raw(statement, parameters, cursor=cursor,
context=context)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 547, in _execute_raw
self._execute(cursor, statement, parameters, context=context)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 566, in _execute
raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-01460:
unimplemented or unre
asonable conversion requested
'SELECT model.investigator_isid, model.active, model.model_id,
model.model_acro
nym, model.model_name, model.therapeutic_area \nFROM model \nWHERE
model.therape
utic_area NOT IN
(:model_therapeutic_area, :model_therapeutic_ar_1, :model_thera
peutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_acronym' {'model_ther
apeutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeuti
c_ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
[sqlalchemy] Re: How to deal with non-ASCII(such as Chinese)table name?
But following the second post (see below), I have read the
documentation,
though not very carefully, and have not validated it by test code,
but the documentation really shows the solution to my problem.
Have I misunderstood the documentation?
--
second post
--
try Column()'s parameters quote=.. and key=..,
doc/docstrings.html#docstrings_sqlalchemy.schema_Column
and/or mapper's explicit column-naming:
doc/adv_datamapping.html#advdatamapping_properties_colname
---
On 3月27日, 下午10时24分, Michael Bayer [EMAIL PROTECTED] wrote:
then there is currently no fix for your situation until this feature
is added to SA.
On Mar 27, 2007, at 7:27 AM, Chen Houwu wrote:
I mean non-ascii column names defined in my database table.
SqlServer, Oracle, MySql...all of the mainstream DBMS support this
feature,
In east asia(China, Japan, Korea), non-ascii column names and table
names,together with non-ascii strings in record
are widely used.
On 3月25日, 下午10时55分, Michael Bayer [EMAIL PROTECTED]
wrote:
non-ascii attribute names in your Python code ? or non-ascii column
names defined in your database table ? I didnt know the latter was
possible with most databases.
On Mar 25, 2007, at 1:04 AM, Chen Houwu wrote:
from the sqlalchemy documentation,
I found when table definition
(e.g
users_table = Table('users', metadata,
... Column('user_id', Integer, primary_key=True),
... Column('user_name', String(40)),
... Column('password', String(10))
... )
)
and a class
(e.g.
class User(object):
... pass
)
are mapped together,
the column name in the column definition(e.g. 'password') are mapped
into an attribute of the class,
but python only accept ASCII attribute name.
so,
What if the above password is a word in Chinese GB2312 encoding?
Does it mean I can't deal with column name which is not ASCII?
--~--~-~--~~~---~--~~
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: backref VS relation on both sides
On 3/27/07, Michael Bayer [EMAIL PROTECTED] wrote:
didnt get your attachment.
Yeah, I forgot it, as usual, but it wasn't very import anyway...
but yes, they are different. during a
many-to-many flush() operation, the two relationships know to
communicate which one actually dealt with the row in the many-to-many
table, without it, youll get duplicate association rows.
additionally, the automatic management of the bi-directional
attribute in Python tends to smooth out operations significantly,
although two separate attributes should still work.
It seems like my question wasn't clear, sorry about that, but your
answer contained what I wanted to know anyway. Just for the record (no
need to answer again), what I meant was: is it a bug that these two
ways behave differently or is it an intended behavior?.
Anyway, thanks for the clarification.
On Mar 27, 2007, at 7:40 AM, Gaetan de Menten wrote:
Hi list,
A while ago, someone came up with a problem on the Elixir list. The
problem is that in elixir we force users to define relationships on
both sides of the relationship (ie, there is no concept of backref).
And I (mistakenly) thought that having two mappers with relations to
each other was the same as having one mapper with a relation to the
other and a backref, so I didn't define backrefs for those
relationships.
It seems like, in fact, it's not exactly the same: the difference seem
to be with the automatic update of the other attribute (the backref)
*even before a flush is done*, as demonstrated at:
http://www.sqlalchemy.org/docs/
datamapping.html#datamapping_relations_backreferences
Now the question is: shouldn't these two ways to define relations
be equivalent?
Attached is a test case, demonstrating that the second way doesn't
work (in case it can be of any use).
BTW: there are several mistakes in the example there.
Address = mapper(Address, addresses_table)
User = mapper(User, users_table, properties = {
'addresses' : relation(Address, backref='user')
}
)
should read:
mapper(Address, addresses_table)
mapper(User, users_table, properties = {
'addresses' : relation(Address, backref='user')
}
)
and
a1.user is user and a2.user is user
True
should be:
a1.user is u and a2.user is u
True
--
Gaëtan de Menten
http://openhex.org
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
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: How to deal with non-ASCII(such as Chinese)table name?
from the docstring in
class Column:
def __init__()
name
The name of this column. This should be the identical name as it
appears, or will appear, in the database.
key
Defaults to None: an optional alias name for this column. The
column will then be identified everywhere in an application, including
the column list on its Table, by this key, and not the given name.
Generated SQL, however, will still reference the column by its actual
name.
If I use a ascii key, and a non-ascii name according to the column in
the database, is there anything wrong?
I *won't* solve the problem by Overriding Column name in mapper
function, which is showed as following.
-
Overriding Column Names
When mappers are constructed, by default the column names in the Table
metadata are used as the names of attributes on the mapped class. This
can be customzed within the properties by stating the key/column
combinations explicitly:
user_mapper = mapper(User, users_table, properties={
'id' : users_table.c.user_id,
'name' : users_table.c.user_name,
})
---
On 3月27日, 下午10时55分, Chen Houwu [EMAIL PROTECTED] wrote:
But following the second post (see below), I have read the
documentation,
though not very carefully, and have not validated it by test code,
but the documentation really shows the solution to my problem.
Have I misunderstood the documentation?
--
second post
--
try Column()'s parameters quote=.. and key=..,
doc/docstrings.html#docstrings_sqlalchemy.schema_Column
and/or mapper's explicit column-naming:
doc/adv_datamapping.html#advdatamapping_properties_colname
---
On 3月27日, 下午10时24分, Michael Bayer [EMAIL PROTECTED] wrote:
then there is currently no fix for your situation until this feature
is added to SA.
On Mar 27, 2007, at 7:27 AM, Chen Houwu wrote:
I mean non-ascii column names defined in my database table.
SqlServer, Oracle, MySql...all of the mainstream DBMS support this
feature,
In east asia(China, Japan, Korea), non-ascii column names and table
names,together with non-ascii strings in record
are widely used.
On 3月25日, 下午10时55分, Michael Bayer [EMAIL PROTECTED]
wrote:
non-ascii attribute names in your Python code ? or non-ascii column
names defined in your database table ? I didnt know the latter was
possible with most databases.
On Mar 25, 2007, at 1:04 AM, Chen Houwu wrote:
from the sqlalchemy documentation,
I found when table definition
(e.g
users_table = Table('users', metadata,
... Column('user_id', Integer, primary_key=True),
... Column('user_name', String(40)),
... Column('password', String(10))
... )
)
and a class
(e.g.
class User(object):
... pass
)
are mapped together,
the column name in the column definition(e.g. 'password') are mapped
into an attribute of the class,
but python only accept ASCII attribute name.
so,
What if the above password is a word in Chinese GB2312 encoding?
Does it mean I can't deal with column name which is not ASCII?
--~--~-~--~~~---~--~~
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: How to deal with non-ASCII(such as Chinese)table name?
On Tuesday 27 March 2007 18:12:37 Chen Houwu wrote: from the docstring in class Column: def __init__() name The name of this column. This should be the identical name as it appears, or will appear, in the database. key Defaults to None: an optional alias name for this column. The column will then be identified everywhere in an application, including the column list on its Table, by this key, and not the given name. Generated SQL, however, will still reference the column by its actual name. If I use a ascii key, and a non-ascii name according to the column in the database, is there anything wrong? well, try it... and tell me, i also have cyrrilic to handle... --~--~-~--~~~---~--~~ 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: How to deal with non-ASCII(such as Chinese)table name?
I am doing some experimenting with this, and it would appear that
Psycopg2 (not sure about postgres itself) can not handle unicode
column names. sqlite and mysql adapters do handle unicode table and
column names.
so in changeset 2447, I have made some adjustments so that unicode
table names and column names are supported. there is also a unit
test that does some INSERTs and tests out a basic mapping with one
relationship both lazy- and eager-loaded, and it passes on sqlite and
mysql so far. however the names I am using have just a single non-
ascii character present; with a full set of chinese characters, that
might raise more issues.
In particular the following regular expression has to work with
chinese characters (which it *should*...)
match = re.compile(r'(?!:):([\w_]+)', re.UNICODE)
the main thing being that the \w matcher will allow all characters
in your column name to be matched as alphanumeric with the UNICODE
flag enabled.
So you should check out the latest trunk and begin working with it.
I would label this support as preliminary since more testing will
be needed.
Additionally, check out the unit tests test/sql/unicode.py, which
illustrates two ways to do mappings with unicode names. one defines
a key inside each Column with a plain string name, the other
defines a plain string name on the mapper itself.
On Mar 27, 2007, at 7:27 AM, Chen Houwu wrote:
I mean non-ascii column names defined in my database table.
SqlServer, Oracle, MySql...all of the mainstream DBMS support this
feature,
In east asia(China, Japan, Korea), non-ascii column names and table
names,together with non-ascii strings in record
are widely used.
On 3月25日, 下午10时55分, Michael Bayer [EMAIL PROTECTED]
wrote:
non-ascii attribute names in your Python code ? or non-ascii column
names defined in your database table ? I didnt know the latter was
possible with most databases.
On Mar 25, 2007, at 1:04 AM, Chen Houwu wrote:
from the sqlalchemy documentation,
I found when table definition
(e.g
users_table = Table('users', metadata,
... Column('user_id', Integer, primary_key=True),
... Column('user_name', String(40)),
... Column('password', String(10))
... )
)
and a class
(e.g.
class User(object):
... pass
)
are mapped together,
the column name in the column definition(e.g. 'password') are mapped
into an attribute of the class,
but python only accept ASCII attribute name.
so,
What if the above password is a word in Chinese GB2312 encoding?
Does it mean I can't deal with column name which is not ASCII?
--~--~-~--~~~---~--~~
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: query stopped working in 0.3.6
this is most likely a typing error and id need to see the types of
columns being used. in particular if you have any String columns
without a size, they are now interpreted as CLOBs which might be
where its tripping up.
On Mar 27, 2007, at 10:35 AM, shday wrote:
Here is the surrounding code:
def model_list(therapeutic_area='All'):
if therapeutic_area == 'All':
s = model_table.select(order_by=[model_table.c.model_acronym])
elif therapeutic_area == 'Other':
s =
model_table.select(~(model_table.c.therapeutic_area.in_
('Respiratory','Diabetes',
'Inflammation','CVD')),
order_by=[model_table.c.model_acronym])
else:
s =
model_table.select
(model_table.c.therapeutic_area==therapeutic_area,order_by=
[model_table.c.model_acronym])
rs = s.execute()
return [(str(row['model_id']),(row['model_acronym'] + -
+row['model_name'])[:80]) for row in rs.fetchall()]
On Mar 27, 10:29 am, shday [EMAIL PROTECTED] wrote:
I upgraded to 0.3.6 from 0.3.5 and one of my querys stopped working:
s =
model_table.select(~(model_table.c.therapeutic_area.in_
('Respiratory','Diab etes',
'Inflammation','CVD')),
order_by=
[model_table.c.model_acronym])
All my other query still work fine. Although this is the only one
using in_() and ~.
The funny thing is that the created sql appears to be exactly the
same
as before. Here is the error:
2007-03-26 15:11:20,851 INFO sqlalchemy.engine.base.Engine.0x..d0
SELECT model.i
nvestigator_isid, model.active, model.model_id, model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,851 sqlalchemy.engine.base.Engine.0x..d0 INFO
SELECT model.i
nvestigator_isid, model.active, model.model_id, model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
ROLLBACK
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
ROLLBACK
Traceback (most recent call last):
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\start-sr
t.py, line 23, in ?
from srt.controllers import Root
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\srt\cont
rollers.py, line 9, in ?
model_list = [('Respiratory',[('','Please select a model')]
+dbmodel.model_li
st('Respiratory')),
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\srt\mode
l.py, line 158, in model_list
rs = s.execute()
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\sql.
py, line 776, in execute
return self.compile(engine=self.engine,
parameters=compile_params).execute(*
multiparams, **params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\sql.
py, line 669, in execute
return e.execute_compiled(self, *multiparams, **params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 726, in execute_compiled
return connection.execute_compiled(compiled, *multiparams,
**params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 503, in execute_compiled
proxy(str(compiled), parameters)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 499, in proxy
self._execute_raw(statement, parameters, cursor=cursor,
context=context)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 547, in _execute_raw
self._execute(cursor, statement, parameters, context=context)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 566, in _execute
raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-01460:
unimplemented or unre
asonable conversion requested
'SELECT model.investigator_isid, model.active, model.model_id,
model.model_acro
nym, model.model_name, model.therapeutic_area \nFROM model \nWHERE
model.therape
utic_area NOT IN
[sqlalchemy] Re: query stopped working in 0.3.6
Hi,
The table is reflected, with one column overridden, here:
model_table = Table('model',metadata,
Column('model_id',Numeric(precision=6,length=0),Sequence('model_seq'),
primary_key=True, nullable=False),
autoload=True)
Here is what the resulting Table object looks like:
Table('model',DynamicMetaData(),
Column('investigator_isid',OracleString(length=8),nullable=False),
Column('active',OracleNumeric(precision=1,length=0),nullable=False,default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0x0193E850)),
Column('model_id',Numeric(precision=6,length=0),primary_key=True,nullable=False,
default=Sequence('model_seq',start=None,increment=None,optional=False)),
Column('model_acronym',OracleString(length=32),nullable=False),
Column('model_name',OracleString(length=256),nullable=False),
Column('therapeutic_area',OracleString(length=32)),
schema=None)
On Mar 27, 12:12 pm, Michael Bayer [EMAIL PROTECTED] wrote:
this is most likely a typing error and id need to see the types of
columns being used. in particular if you have any String columns
without a size, they are now interpreted as CLOBs which might be
where its tripping up.
On Mar 27, 2007, at 10:35 AM, shday wrote:
Here is the surrounding code:
def model_list(therapeutic_area='All'):
if therapeutic_area == 'All':
s = model_table.select(order_by=[model_table.c.model_acronym])
elif therapeutic_area == 'Other':
s =
model_table.select(~(model_table.c.therapeutic_area.in_
('Respiratory','Diabetes',
'Inflammation','CVD')),
order_by=[model_table.c.model_acronym])
else:
s =
model_table.select
(model_table.c.therapeutic_area==therapeutic_area,order_by=
[model_table.c.model_acronym])
rs = s.execute()
return [(str(row['model_id']),(row['model_acronym'] + -
+row['model_name'])[:80]) for row in rs.fetchall()]
On Mar 27, 10:29 am, shday [EMAIL PROTECTED] wrote:
I upgraded to 0.3.6 from 0.3.5 and one of my querys stopped working:
s =
model_table.select(~(model_table.c.therapeutic_area.in_
('Respiratory','Diab etes',
'Inflammation','CVD')),
order_by=
[model_table.c.model_acronym])
All my other query still work fine. Although this is the only one
using in_() and ~.
The funny thing is that the created sql appears to be exactly the
same
as before. Here is the error:
2007-03-26 15:11:20,851 INFO sqlalchemy.engine.base.Engine.0x..d0
SELECT model.i
nvestigator_isid, model.active, model.model_id, model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,851 sqlalchemy.engine.base.Engine.0x..d0 INFO
SELECT model.i
nvestigator_isid, model.active, model.model_id, model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
ROLLBACK
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
ROLLBACK
Traceback (most recent call last):
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\start-sr
t.py, line 23, in ?
from srt.controllers import Root
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\srt\cont
rollers.py, line 9, in ?
model_list = [('Respiratory',[('','Please select a model')]
+dbmodel.model_li
st('Respiratory')),
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\srt\mode
l.py, line 158, in model_list
rs = s.execute()
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\sql.
py, line 776, in execute
return self.compile(engine=self.engine,
parameters=compile_params).execute(*
multiparams, **params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\sql.
py, line 669, in execute
return e.execute_compiled(self, *multiparams, **params)
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\engi
ne\base.py, line 726, in execute_compiled
return
[sqlalchemy] Re: query stopped working in 0.3.6
Okay, I changed the NLS_LANG setting on the oracle client to match
that of the server, and now it works. I found out about it here:
http://www.theserverside.com/discussions/thread.tss?thread_id=14634
On Mar 27, 1:27 pm, shday [EMAIL PROTECTED] wrote:
Hi,
The table is reflected, with one column overridden, here:
model_table = Table('model',metadata,
Column('model_id',Numeric(precision=6,length=0),Sequence('model_seq'),
primary_key=True, nullable=False),
autoload=True)
Here is what the resulting Table object looks like:
Table('model',DynamicMetaData(),
Column('investigator_isid',OracleString(length=8),nullable=False),
Column('active',OracleNumeric(precision=1,length=0),nullable=False,default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0x0193E850)),
Column('model_id',Numeric(precision=6,length=0),primary_key=True,nullable=False,
default=Sequence('model_seq',start=None,increment=None,optional=False)),
Column('model_acronym',OracleString(length=32),nullable=False),
Column('model_name',OracleString(length=256),nullable=False),
Column('therapeutic_area',OracleString(length=32)),
schema=None)
On Mar 27, 12:12 pm, Michael Bayer [EMAIL PROTECTED] wrote:
this is most likely a typing error and id need to see the types of
columns being used. in particular if you have any String columns
without a size, they are now interpreted as CLOBs which might be
where its tripping up.
On Mar 27, 2007, at 10:35 AM, shday wrote:
Here is the surrounding code:
def model_list(therapeutic_area='All'):
if therapeutic_area == 'All':
s = model_table.select(order_by=[model_table.c.model_acronym])
elif therapeutic_area == 'Other':
s =
model_table.select(~(model_table.c.therapeutic_area.in_
('Respiratory','Diabetes',
'Inflammation','CVD')),
order_by=[model_table.c.model_acronym])
else:
s =
model_table.select
(model_table.c.therapeutic_area==therapeutic_area,order_by=
[model_table.c.model_acronym])
rs = s.execute()
return [(str(row['model_id']),(row['model_acronym'] + -
+row['model_name'])[:80]) for row in rs.fetchall()]
On Mar 27, 10:29 am, shday [EMAIL PROTECTED] wrote:
I upgraded to 0.3.6 from 0.3.5 and one of my querys stopped working:
s =
model_table.select(~(model_table.c.therapeutic_area.in_
('Respiratory','Diab etes',
'Inflammation','CVD')),
order_by=
[model_table.c.model_acronym])
All my other query still work fine. Although this is the only one
using in_() and ~.
The funny thing is that the created sql appears to be exactly the
same
as before. Here is the error:
2007-03-26 15:11:20,851 INFO sqlalchemy.engine.base.Engine.0x..d0
SELECT model.i
nvestigator_isid, model.active, model.model_id, model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,851 sqlalchemy.engine.base.Engine.0x..d0 INFO
SELECT model.i
nvestigator_isid, model.active, model.model_id, model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
ROLLBACK
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
ROLLBACK
Traceback (most recent call last):
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\start-sr
t.py, line 23, in ?
from srt.controllers import Root
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\srt\cont
rollers.py, line 9, in ?
model_list = [('Respiratory',[('','Please select a model')]
+dbmodel.model_li
st('Respiratory')),
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\srt\mode
l.py, line 158, in model_list
rs = s.execute()
File c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg
\sqlalchemy\sql.
py, line 776, in execute
return self.compile(engine=self.engine,
[sqlalchemy] Re: query stopped working in 0.3.6
the probable change that revealed this error is that oracle in 0.3.6
will by default apply cursor.setinputsizes() to all queries, which is
necessary for CLOB/BLOB. So encoding/NLS_LANG stuff probably became
more significant once cx_oracle has less need to guess about bind
parameters.
this option can be disabled by sending auto_setinputsizes=False to
create_engine().
On Mar 27, 2007, at 2:48 PM, shday wrote:
Okay, I changed the NLS_LANG setting on the oracle client to match
that of the server, and now it works. I found out about it here:
http://www.theserverside.com/discussions/thread.tss?thread_id=14634
On Mar 27, 1:27 pm, shday [EMAIL PROTECTED] wrote:
Hi,
The table is reflected, with one column overridden, here:
model_table = Table('model',metadata,
Column('model_id',Numeric(precision=6,length=0),Sequence
('model_seq'),
primary_key=True, nullable=False),
autoload=True)
Here is what the resulting Table object looks like:
Table('model',DynamicMetaData(),
Column('investigator_isid',OracleString(length=8),nullable=False),
Column('active',OracleNumeric
(precision=1,length=0),nullable=False,default= PassiveDefault
(sqlalchemy.sql._TextClause
object at 0x0193E850)),
Column('model_id',Numeric
(precision=6,length=0),primary_key=True,nullable=F alse,
default=Sequence
('model_seq',start=None,increment=None,optional=False)),
Column('model_acronym',OracleString(length=32),nullable=False),
Column('model_name',OracleString(length=256),nullable=False),
Column('therapeutic_area',OracleString(length=32)),
schema=None)
On Mar 27, 12:12 pm, Michael Bayer [EMAIL PROTECTED] wrote:
this is most likely a typing error and id need to see the types of
columns being used. in particular if you have any String columns
without a size, they are now interpreted as CLOBs which might be
where its tripping up.
On Mar 27, 2007, at 10:35 AM, shday wrote:
Here is the surrounding code:
def model_list(therapeutic_area='All'):
if therapeutic_area == 'All':
s = model_table.select(order_by=
[model_table.c.model_acronym])
elif therapeutic_area == 'Other':
s =
model_table.select(~(model_table.c.therapeutic_area.in_
('Respiratory','Diabetes',
'Inflammation','CVD')),
order_by=
[model_table.c.model_acronym])
else:
s =
model_table.select
(model_table.c.therapeutic_area==therapeutic_area,order_by=
[model_table.c.model_acronym])
rs = s.execute()
return [(str(row['model_id']),(row['model_acronym'] + -
+row['model_name'])[:80]) for row in rs.fetchall()]
On Mar 27, 10:29 am, shday [EMAIL PROTECTED] wrote:
I upgraded to 0.3.6 from 0.3.5 and one of my querys stopped
working:
s =
model_table.select(~(model_table.c.therapeutic_area.in_
('Respiratory','Diab etes',
'Inflammation','CVD')),
order_by=
[model_table.c.model_acronym])
All my other query still work fine. Although this is the only one
using in_() and ~.
The funny thing is that the created sql appears to be exactly the
same
as before. Here is the error:
2007-03-26 15:11:20,851 INFO sqlalchemy.engine.base.Engine.0x..d0
SELECT model.i
nvestigator_isid, model.active, model.model_id,
model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,851 sqlalchemy.engine.base.Engine.0x..d0 INFO
SELECT model.i
nvestigator_isid, model.active, model.model_id,
model.model_acronym,
model.model
_name, model.therapeutic_area
FROM model
WHERE model.therapeutic_area NOT IN
(:model_therapeutic_area, :model_therapeutic
_ar_1, :model_therapeutic_ar_2, :model_therapeutic_ar_3) ORDER BY
model.model_ac
ronym
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
{'model_therap
eutic_area': 'Respiratory', 'model_therapeutic_ar_3': 'CVD',
'model_therapeutic_
ar_2': 'Inflammation', 'model_therapeutic_ar_1': 'Diabetes'}
2007-03-26 15:11:20,861 INFO sqlalchemy.engine.base.Engine.0x..d0
ROLLBACK
2007-03-26 15:11:20,861 sqlalchemy.engine.base.Engine.0x..d0 INFO
ROLLBACK
Traceback (most recent call last):
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\start-sr
t.py, line 23, in ?
from srt.controllers import Root
File C:\Documents and Settings\Daystev\Desktop\srt-trunk\srt-
project
\srt\cont
rollers.py, line 9, in ?
model_list = [('Respiratory',[('','Please select a model')]
+dbmodel.model_li
st('Respiratory')),
File C:\Documents and
[sqlalchemy] Re: [ticket:336] Informix support and some enhancement for oracle and pgsql
ive added the patchfile to the ticket and its part of a long list of things i have to do. On Mar 21, 2007, at 11:56 PM, 张骏 wrote: so, youd just have people using postgres automatically and without any control issue a SAVEPOINT SP after every single statement execution ? what about the performance overhead ? what if someone wants to issue SAVEPOINT at some other step, or not at all ? yes，you are right. reject this patch please. i only want to make the pgsql's behavior as same as oracle. but i am not thinking over those questions. :) -- james.zhang [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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] UOW relation delete bug
Surprised no one has hit this one yet.
When issuing a series of deletes in a UOW, SA issues the bogus delete
statement
DELETE child where id = [1,2,3]
instead of using IN()
Test case attached. Seems to work in Sqlite even while issuing the bogus SQL
(which is probably why a unit test didn't pick it up), but MS-SQL doesn't
like it; didn't check PG or others.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
from sqlalchemy import *
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
#metadata = BoundMetaData('mssql://d:[EMAIL PROTECTED]/drvtest')
metadata = BoundMetaData('sqlite:///:memory:')
entity = Table('entity', metadata,
Column('id', INT, primary_key=True, nullable=False),
Column('typ', VARCHAR(12)),
Column('lname', VARCHAR(128))
)
entityattr = Table('entityattr', metadata,
Column('id', INT, primary_key=True, nullable=False),
Column('ident', INT, ForeignKey('entity.id'), nullable=False),
Column('typ', VARCHAR(12), nullable=False),
Column('val', VARCHAR(128))
)
metadata.create_all()
class O(object):
def __init__(self,**kw):
for k,v in kw.items():
setattr(self,k,v)
class Ent(O): pass
class Entattr(O): pass
mapper(Ent, entity, properties = {'props':relation(Entattr, cascade=all, delete-orphan)})
mapper(Entattr, entityattr)
S = create_session()
S.save(Ent(typ='A',lname='A',
props = [Entattr(typ='A1', val='1'),
Entattr(typ='A2', val='2'),
Entattr(typ='A3', val='3'),
Entattr(typ='A4', val='4'),
Entattr(typ='A5', val='5'),
Entattr(typ='A6', val='6')
]))
S.flush()
S.clear()
e = S.query(Ent).options(eagerload('props')).select()[0]
# remove some attributes
e.props = [p for p in e.props if 0 == int(p.val) % 2]
# put some back
e.props.append(Entattr(typ='A6', val='6'))
e.props.append(Entattr(typ='A7', val='7'))
e.props.append(Entattr(typ='A8', val='8'))
S.flush()# -- Delete issued here
S.clear()
e = S.query(Ent).options(eagerload('props')).select()[0]
assert 6 == len(e.props)
metadata.drop_all()
