[sqlalchemy] Re: Multiple encodings in my database
Am Freitag, 27. Juni 2008 01:20 schrieb Michael Bayer:
> first of all, the stack trace suggests you have not set the "encoding"
> parameter on create_engine() as it's still using UTF-8.
>
> If you mean that a single database column may have different encodings
> in different rows, you want to do your own encoding/decoding with
> "encoding errors" set to something liberal like "ignore". You also
> need to use your own custom type, as below:
>
> from sqlalchemy import types
> class MyEncodedType(types.TypeDecorator):
> impl = String
>
> def process_bind_param(self, value, dialect):
> assert isinstance(value, unicode)
> return value.encode('latin-1')
>
> def process_result_value(self, value, dialect):
> return value.decode('latin-1', 'ignore')
>
> then use MyEncodedType() as the type for all your columns which
> contain random encoding. No convert_unicode setting should be used
> on your engine as this type replaces that usage.
Perfect, that works, thanks!
Best Regards,
Hermann
--
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
--~--~-~--~~~---~--~~
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: Divide columns with possible zeroDivisionError
Hello,
Thanks to both of you.
1) Case combined with null() works fine to order one column by
replacing zero with NULL. -- See Query1 in the attached snippet
Does nullif exist with SA ?
2) When dividing 2 columns, where zeros of the divisor are replaced by
NULL, the order doesn't work. See Query2
I tried to put floats, import future division, ... without success.
May be I am doing something wrong ? Any ideas ?
I am using sqlite (will also investigate sqlite docs)
Thanks in advance
Dominique
#! /usr/bin/env python
# -*- coding: utf-8 -*-
#from __future__ import division
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.sql import *
metadata = MetaData()
engine = create_engine('sqlite:///:memory:', encoding = 'utf8',
echo=True)
mytable = Table('mytable', metadata,
Column('id', Integer, primary_key=True),
Column('colA', Float),
Column('colB', Float),
Column('colC', Float)
)
class Mytable(object):
def __init__(self, colA, colB, colC):
self.colA = colA
self.colB = colB
self.colC = colC
def __repr__(self):
return "" % (self.colA, self.colB,
self.colC)
metadata.create_all(engine)
mapper(Mytable, mytable)
e0=Mytable(0, 0.0, 0.0)
e1=Mytable(1, 1.0, 0.0)
e2=Mytable(2, 2.0, 0.0)
e3=Mytable(3, 0.0, 10.0)#0
e4=Mytable(4, 1.0, 10.0)#0.1
e5=Mytable(5, 2.0, 10.0)#0.2
e6=Mytable(6, 2.0, 4.0)#0.5
e7=Mytable(7, 3.0, 4.0)#0.75
e8=Mytable(8, 3.0, 8.0)#0.375
e9=Mytable(9, 4.0, 8.0)#0.5
e10=Mytable(10, 5.0, 8.0)#0.625
Session = sessionmaker(bind=engine, autoflush=True,
transactional=True)
session = Session()
for i in [e0,e1,e2,e3,e4,e5,e6,e7,e8,e9,e10]:
session.save(i)
session.commit()
# order should be: 0,1,2,6,7,8,9,10,3,4,5
mycase = case([(Mytable.colC==0,null())],else_=Mytable.colC)
Query1 = session.query(Mytable).order_by(asc(mycase))
print Query1
Query1 = Query1.all()
print Query1
for qq in Query1 :
print qq.colA
print 50*'*'
# order should be: 0,1,2,3,4,5,8,6and9,10,7
mycase2 = (Mytable.colB /
case([(Mytable.colC==0,null())],else_=Mytable.colC))
Query2 = session.query(Mytable).order_by(asc(mycase2))
print Query2
Query2 =Query2.all()
print Query2
for pp in Query2 :
print pp.colA
session.clear()
session.close()
--~--~-~--~~~---~--~~
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: sharding id_chooser query_chooser
My understanding of this query_chooser is that it's used when you want to execute orm's sql rather than raw sql. I don't quite understand what is visit_binary function do from attribute_shard.py example. What does it mean binary.operator, binary.left, binary.right.clause and query._criterion? The sharding design behind our application is that we have a master lookup table and shards. What shard to execute sql is based on querying master lookup table. taken from sqlalchemy attribute_shard.py example: def query_chooser(query): ids = [] # here we will traverse through the query's criterion, searching # for SQL constructs. we'll grab continent names as we find them # and convert to shard ids class FindContinent(sql.ClauseVisitor): def visit_binary(self, binary): if binary.left is weather_locations.c.continent: if binary.operator == operators.eq: ids.append(shard_lookup[binary.right.value]) elif binary.operator == operators.in_op: for bind in binary.right.clauses: ids.append(shard_lookup[bind.value]) FindContinent().traverse(query._criterion) if len(ids) == 0: return ['north_america', 'asia', 'europe', 'south_america'] else: return ids thank you. --~--~-~--~~~---~--~~ 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: Multiple encodings in my database
first of all, the stack trace suggests you have not set the "encoding"
parameter on create_engine() as it's still using UTF-8.
If you mean that a single database column may have different encodings
in different rows, you want to do your own encoding/decoding with
"encoding errors" set to something liberal like "ignore". You also
need to use your own custom type, as below:
from sqlalchemy import types
class MyEncodedType(types.TypeDecorator):
impl = String
def process_bind_param(self, value, dialect):
assert isinstance(value, unicode)
return value.encode('latin-1')
def process_result_value(self, value, dialect):
return value.decode('latin-1', 'ignore')
then use MyEncodedType() as the type for all your columns which
contain random encoding. No convert_unicode setting should be used
on your engine as this type replaces that usage.
On Jun 26, 2008, at 6:55 PM, Hermann Himmelbauer wrote:
>
> Hi,
> I'm trying to access a database via SA, which contains varchars with
> different, arbitrary encodings. Most of them are ascii or ISO-8859-2
> encoded,
> however, many are windows-1252 encoded and there are also some other
> weird
> ones.
>
> In my engine setup, I set the encoding to latin1 and set
> convert_unicode to
> True, as I my application requires the database values in unicode
> format.
>
> If SA now tries to retrieve such a key, the following traceback
> occurs:
>
> --
> File "/home/dusty/prog/python_modules/sqlalchemy/engine/base.py",
> line 1605,
> in _get_col
>return processor(row[index])
> File "/home/dusty/prog/python_modules/sqlalchemy/databases/
> maxdb.py", line
> 112, in process
>return value.decode(dialect.encoding)
>
> File "/local/home/dusty/python/Python-2.4.4/lib/python2.4/encodings/
> utf_8.py",
> line 16, in decode
>return codecs.utf_8_decode(input, errors, True)
> UnicodeDecodeError: 'utf8' codec can't decode bytes in position 3-6:
> invalid
> data
> -
>
> What can I do? It's not so important that all characters are correctly
> displayed, but it's vital that such improper encodings do not crash my
> application. Perhaps, there's some "universal" encoding that is able
> to deal
> with such problems?
>
> Best Regards,
> Hermann
>
> --
> [EMAIL PROTECTED]
> GPG key ID: 299893C7 (on keyservers)
> FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
>
> >
--~--~-~--~~~---~--~~
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] Multiple encodings in my database
Hi, I'm trying to access a database via SA, which contains varchars with different, arbitrary encodings. Most of them are ascii or ISO-8859-2 encoded, however, many are windows-1252 encoded and there are also some other weird ones. In my engine setup, I set the encoding to latin1 and set convert_unicode to True, as I my application requires the database values in unicode format. If SA now tries to retrieve such a key, the following traceback occurs: -- File "/home/dusty/prog/python_modules/sqlalchemy/engine/base.py", line 1605, in _get_col return processor(row[index]) File "/home/dusty/prog/python_modules/sqlalchemy/databases/maxdb.py", line 112, in process return value.decode(dialect.encoding) File "/local/home/dusty/python/Python-2.4.4/lib/python2.4/encodings/utf_8.py", line 16, in decode return codecs.utf_8_decode(input, errors, True) UnicodeDecodeError: 'utf8' codec can't decode bytes in position 3-6: invalid data - What can I do? It's not so important that all characters are correctly displayed, but it's vital that such improper encodings do not crash my application. Perhaps, there's some "universal" encoding that is able to deal with such problems? Best Regards, Hermann -- [EMAIL PROTECTED] GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7 --~--~-~--~~~---~--~~ 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: backrefs
On Jun 26, 2008, at 1:22 PM, [EMAIL PROTECTED] wrote: > sort of, > x: relation( Foo, primaryjoin=, secondaryjoin=,remote_side=,whatever, > backref= halfbackref(name=abc,post_update=True)) > and let it construct the backref from relation.primary/secondaryjoin > etc, putting name and whatever extra args are there. it already re-uses primaryjoin and secondaryjoin in 0.5 with the regular backref="foo", reversing primary/secondary if both are present. the rest of the args dont really propagate. >> >> remote_side by definition cannot be the same on both sides of a >> relation. do you mean the backref should "figure it out" based on >> the forwards-facing explicit remote_side ? > yes, if there is explicit forward remote_side, the backward should be > guessable. if that needs extra info like pjoins etc, so be it. > heh, by definition... right now i'm giving same thing to both sides > and it seems to work. but not really sure, i havent tested heavily > the backref-using stuff. "usually guessable" is not really enough here. its definitely not "always" guessable. --~--~-~--~~~---~--~~ 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: backrefs
On Thursday 26 June 2008 17:34:00 Michael Bayer wrote: > On Jun 26, 2008, at 7:22 AM, [EMAIL PROTECTED] wrote: > > hi > > just an idea: is it possible to have half-baked > > backref-declarations? > > > > i want to use the SA's way of inventing backrefs from a name, and > > just provide some extra arguments to that invention. > > > > instead now i have a full backref(...) having more or less all of > > the relation(...) arguments, but with additional logic on > > picking/swapping of primaryjoin/secondaryjoin - something that SA > > does internaly in the PropertyLoader constructor. > > this sounds like you mean: > > > x: relation(Foo, backref="somebackref", backref_primaryjoin=xx, > backref_remote_site=x) > > ? sort of, x: relation( Foo, primaryjoin=, secondaryjoin=,remote_side=,whatever, backref= halfbackref(name=abc,post_update=True)) and let it construct the backref from relation.primary/secondaryjoin etc, putting name and whatever extra args are there. so arguments of halfbackref are considered "extra" to those implied from the relation itself. > > btw remote_side is not propagated by PropertyLoader constructor - > > but IMO is needed. > > remote_side by definition cannot be the same on both sides of a > relation. do you mean the backref should "figure it out" based on > the forwards-facing explicit remote_side ? yes, if there is explicit forward remote_side, the backward should be guessable. if that needs extra info like pjoins etc, so be it. heh, by definition... right now i'm giving same thing to both sides and it seems to work. but not really sure, i havent tested heavily the backref-using stuff. > Actually I can't see > how that would work, if SA can't figure out the forwards facing one > I don't think it would be that great for it to try guessing the > backwards facing one. --~--~-~--~~~---~--~~ 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: Beginner: query.join not cooperating
On Jun 26, 8:29 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Jun 26, 2008, at 11:12 AM, bukzor wrote: > > > > > Sorry for being a pest, but I've looked at the documentation and > > really can't figure this out. If a mapped class is a node of our > > graph, where do I find the edges, and how do I get to the next node. > > the mapper has a method called "iterate_properties" which returns all > MapperProperty objects it contains. Each PropertyLoader subclass > represents a relation() to another mapper. > > so you can walk among relations as follows: > > recursive = set() > def walk (cls): > print "cls:", cls > if cls in recursive: > return > recursive.add(cls) > > mapper = class_mapper(cls) > for prop in mapper.iterate_properties: > if isinstance(prop, PropertyLoader): > print "key", prop.key > walk(prop.mapper.class_) > > > > > Alternatively, should I do this at the table/sql level rather than the > > class/orm level? > > it depends on what information you're interested in. the use case > here seems to be joining among configured relation()s so the ORM level > would be better. > > > How did you yourself learn this? Is there some other reference I'm > > overlooking? > > heres the docs for every API mentioned above: > > http://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqla...http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_mapper.html#docstrin...http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_interfaces.html#docs...http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_properties.html#docs... > > The same documentation can be had by using "pydoc ", i.e. > pydoc sqlalchemy.orm.mapperlib > > I also think you should consider carefully if you truly need > automatic, implicit joining across arbitrarily long paths. Its a > feature we explicitly removed for its non-pythonicness and > unpredictable behavior. Thanks so much for the help! I need it because the interface I'm exposing lets (advanced) users select filters against arbitrary fields in the database. From these filters I need to construct a sql query. The "easy" way is just to always join every table in the database, but this is infeasible because the size of the database would make this query very slow. So, I need to figure out some sort of smart auto-join method. I'll only define one path between each table, so the result will be deterministic. I'm open to suggestions if you see a better way. I'll let you know how it goes... --Buck --~--~-~--~~~---~--~~ 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: Beginner: query.join not cooperating
On Jun 26, 2008, at 11:12 AM, bukzor wrote: > > Sorry for being a pest, but I've looked at the documentation and > really can't figure this out. If a mapped class is a node of our > graph, where do I find the edges, and how do I get to the next node. the mapper has a method called "iterate_properties" which returns all MapperProperty objects it contains. Each PropertyLoader subclass represents a relation() to another mapper. so you can walk among relations as follows: recursive = set() def walk (cls): print "cls:", cls if cls in recursive: return recursive.add(cls) mapper = class_mapper(cls) for prop in mapper.iterate_properties: if isinstance(prop, PropertyLoader): print "key", prop.key walk(prop.mapper.class_) > > Alternatively, should I do this at the table/sql level rather than the > class/orm level? it depends on what information you're interested in. the use case here seems to be joining among configured relation()s so the ORM level would be better. > How did you yourself learn this? Is there some other reference I'm > overlooking? heres the docs for every API mentioned above: http://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqlalchemy.orm_modfunc_class_mapper http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_mapper.html#docstrings_sqlalchemy.orm.mapper_Mapper http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_interfaces.html#docstrings_sqlalchemy.orm.interfaces_MapperProperty http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_properties.html#docstrings_sqlalchemy.orm.properties_PropertyLoader The same documentation can be had by using "pydoc ", i.e. pydoc sqlalchemy.orm.mapperlib I also think you should consider carefully if you truly need automatic, implicit joining across arbitrarily long paths. Its a feature we explicitly removed for its non-pythonicness and unpredictable behavior. --~--~-~--~~~---~--~~ 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: Beginner: query.join not cooperating
On Jun 25, 10:50 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Jun 25, 2008, at 1:24 PM, bukzor wrote: > > > > > Thanks for that versioning overview. > > > Sorry for changing the topic (Should I make a separate post?), but is > > there a way to make the joins more automatic? > > > I'd like to just specify some filter against table A and another > > against table B and have the system join them, even if the join needs > > to go through C or D. Of course the results would be undefined if > > there was more than one path between A and B, but this is not the case > > in my database and I'm sure a good subset of most databases. Will I > > need to roll this myself? Would people appreciate it if I added this > > functionality to the mapper class? If so, what code would you suggest > > editing? I just need some representation of the database as a graph. > > While you're of course free to create your own Query subclass which > implements a graph traversal of relations to achieve this effect, this > actual functionality was long ago removed (in the form of the old > "join_by()" method), since it amounts to guessing; issues were > apparent almost immedately after its introduction and it was soon > deprecated. It requires an expensive graph traversal each time it's > used, and leads to applications that silently, randomly fail as soon > as a new foreign key path between the two target tables is added. It > fits perfectly the kind of behavior that's targeted by "explicit is > better than implicit". Sorry for being a pest, but I've looked at the documentation and really can't figure this out. If a mapped class is a node of our graph, where do I find the edges, and how do I get to the next node. Alternatively, should I do this at the table/sql level rather than the class/orm level? How did you yourself learn this? Is there some other reference I'm overlooking? --Buck --~--~-~--~~~---~--~~ 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: backrefs
On Jun 26, 2008, at 7:22 AM, [EMAIL PROTECTED] wrote: > > hi > just an idea: is it possible to have half-baked backref-declarations? > > i want to use the SA's way of inventing backrefs from a name, and just > provide some extra arguments to that invention. > > instead now i have a full backref(...) having more or less all of the > relation(...) arguments, but with additional logic on > picking/swapping of primaryjoin/secondaryjoin - something that SA > does internaly in the PropertyLoader constructor. this sounds like you mean: x: relation(Foo, backref="somebackref", backref_primaryjoin=xx, backref_remote_site=x) ? > btw remote_side is not propagated by PropertyLoader constructor - but > IMO is needed. remote_side by definition cannot be the same on both sides of a relation. do you mean the backref should "figure it out" based on the forwards-facing explicit remote_side ? Actually I can't see how that would work, if SA can't figure out the forwards facing one I don't think it would be that great for it to try guessing the backwards facing one. --~--~-~--~~~---~--~~ 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] backrefs
hi just an idea: is it possible to have half-baked backref-declarations? i want to use the SA's way of inventing backrefs from a name, and just provide some extra arguments to that invention. instead now i have a full backref(...) having more or less all of the relation(...) arguments, but with additional logic on picking/swapping of primaryjoin/secondaryjoin - something that SA does internaly in the PropertyLoader constructor. alternatively, i can patch the backref later, letting SA invent it properly and then fix some things - like post_update and remote_side. how bad is that? btw remote_side is not propagated by PropertyLoader constructor - but IMO is needed. ciao svilen --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
