[sqlalchemy] Re: Problems with join query, can't figure out what's wrong. Noob alert!
Hi,
Try to replace
.join(Prosjekt,Sak.prosjektid) with the .join(Prosjekt,Sak.prosjekt)
in your first query and write back,
Alex
On 23 сент, 12:38, olavgg [EMAIL PROTECTED] wrote:
I've recently started using SQLAlchemy and is a newb. I have good SQL
knowledge though. My first project with SQLAlchemy is a big project
where I integrate SQLAlchemy to a Plone application. So far it has
been working great for queries from a single table however queries
with joins in two or more tables is way more difficult.
I've tried to join two tables with two different queries without
success
The query:
objects = db.session.query(Sak).join(Prosjekt,
Sak.prosjektid).filter(Prosjekt.kundeid==1532).all()
returns this error:
InvalidRequestError: Mapper 'Mapper|Sak|sak' has no property 'class
'Products.TikkTakk.model.Prosjekt.Prosjekt''
The query:
objects = db.session.query(Sak).from_statement(SELECT s.saksnr,
s.tittel FROM sak s INNER JOIN prosjekt p ON s.prosjektid =
p.prosjektid WHERE p.kundeid = 1532).all()
returns this error:
NoSuchColumnError: Could not locate column in row for column
'sak.prosjektid'
I have some trouble figuring out what I'm doing wrong. Especially the
NoSuchColumnError is frustrating as the column prosjektid exists in
the table sak, it's not a typo either ;)
The mapping looks like this:
mappers['prosjekt'] = mapper(Prosjekt, tables['prosjekt'],
properties = {
'sak': relation(Sak)
})
mappers['sak'] = mapper(Sak, tables['sak'],
properties = {
'prosjekt': relation(Prosjekt),
})
And the models like this:
class Prosjekt(Entity):
Prosjekt entity map
prosjektid = Field(mysql.MSInteger, primary_key=True)
p_prosjektid = ManyToOne('Prosjekt')
sak = OneToMany('Sak')
using_options(shortnames=True)
def __init__(self, data):
Objects are initialized using form data
def __repr__(self):
return 'Prosjekt (%d)' % (self.prosjektid)
class Sak(Entity):
Sak entity map
prosjektid = ManyToOne('Prosjekt', primary_key=True)
saksnr = Field(mysql.MSInteger, primary_key=True)
using_options(shortnames=True)
def __init__(self, data):
Objects are initialized using form data
def __repr__(self):
return 'Sak (%d)' % (
self.prosjektid,
self.saksnr,
)
--~--~-~--~~~---~--~~
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: Relations - How?
Hello,
Actually, you can join on any condition, not only on the relation
condition,
In this case your query needs to evolve from:
.outerjoin((ATranslation,Book.translations))\
to:
.outerjoin((ATranslation,
and_(ATranslation.book_id == Book.isbn,
ATranslation.language_id == 'en')))\
what will give you
LEFT OUTER JOIN translation AS translation_1
ON book.isbn = translation_1.book_id
AND
translation_1.language_id = %s
what is probably what you want, you can also refer to
http://www.sqlalchemy.org/docs/05/sqlexpression.html#sql_joins
to get more info on this.
Alex
On 24 сент, 03:50, g00fy [EMAIL PROTECTED] wrote:
One more thing,
If i have just book and translations
1 Book has 3 translations
this sql will create me 3 rows (due to the join)
isbn translation_code translation_text
1en The Book
1ru Книжка
1pl Ksiazka
so if i have 10 books i will have 30 rows.
this is NxM,
what if I just want to join by :
LEFT OUTER JOIN translation AS translation_1
ON (book.isbn = translation_1.book_id ,
translation_1.code=en)
so i will only get the required information ?
Limiting the results will also be a problem becouse simple LIMIT will
not be enough.
(consider having 30 multiple relations, it will generate huge ammount
of data M*n*x*...)
On 23 Wrz, 16:37, g00fy [EMAIL PROTECTED] wrote:
Thnx a lot Alex! I already love Pylons and SQLAlchemy!
On 23 Wrz, 12:16, Alex K [EMAIL PROTECTED] wrote:
Hello, here is the answer:
# -*- coding: utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, Unicode,
MetaData, ForeignKey
from sqlalchemy.orm import sessionmaker, mapper,
dynamic_loader,backref, relation, composite, comparable_property,
contains_eager,aliased
from sqlalchemy.sql import text, and_
from orm.object.factory import make_papped_class
from sqlalchemy.orm import PropComparator
from common import Application
app = Application()
session = app.session
metadata = app.metadata
Book :
isbn - integer
translations - many translations
prices - many prices
Translation :
book - FK to book
laguage - FK to Language (oneTOone)
title - string
Language :
code - string
name - string
Currency :
code - string
name - string
Price :
currency - FK
book -FK
brutto - int
netto - int
book_table = Table('book', metadata,
Column('isbn', Integer, primary_key=True)
)
language_table = Table('language', metadata,
Column('code', String(5), primary_key=True),
Column('name', Unicode(20)),
)
currency_table = Table('currency', metadata,
Column('code', String(5), primary_key=True),
Column('name', Unicode(20)),
)
translation_table = Table('translation', metadata,
Column('book_id', Integer,
ForeignKey(book_table.c.isbn)),
Column('language_id', String(5),
ForeignKey(language_table.c.code)),
Column('title', Unicode(512)),
)
price_table = Table('price', metadata,
Column('currency_id', String(5),
ForeignKey(currency_table.c.code)),
Column('book_id', Integer,
ForeignKey(book_table.c.isbn)),
Column('brutto', Integer),
Column('netto', Integer)
)
metadata.create_all()
#create objects
class Book(object):
def __init__(self,isbn):
self.isbn = isbn
class Language(object):
def __init__(self,code,name):
self.code = code
self.name = name
class Currency(object):
def __init__(self,code,name):
self.code = code
self.name = name
class Translation(object):
def __init__(self,book_id,language_id,title):
self.book_id = book_id
self.language_id = language_id
self.title = title
class Price(object):
def __init__(self,currency_id,book_id,brutto,netto):
self.currency_id = currency_id
self.book_id = book_id
self.brutto = brutto
self.netto = netto
mapper(Book,book_table)
mapper(Language,language_table)
mapper(Currency,currency_table)
mapper(Translation,translation_table,properties = {
'book': relation(Book,
lazy = False, #1 note lazy here, it means that
we
# will use lazy loading (more
details in the docs
backref = backref('translations',lazy = False)),
'language': relation(Language,
uselist = False,#2 note
[sqlalchemy] Re: Problems with join query, can't figure out what's wrong. Noob alert!
Thank you for your answer, I did as you said but still returns the same error: InvalidRequestError: Mapper 'Mapper|Sak|sak' has no property 'class 'Products.TikkTakk.model.Prosjekt.Prosjekt'' --~--~-~--~~~---~--~~ 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: sorting by members
thanx alot
it works
On Sep 21, 2:05 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Sep 20, 2008, at 7:59 AM, m.talaee wrote:
hi all
i need to sort users by number of theirfriends.
there is many to many relation table between user table and itself.
order_by don't support ordering (or i couldn't find).
please help me
for a mapping like:
mapper(User, users, properties={
'friends':relation(User, secondary=user_friends, primaryjoin=,
secondaryjoin=...)
})
you can say:
subq =
select
([func
.count
(user_friends
.c.friend_id).label('friendcount')]).group_by(user_friends.c.user_id)
sess.query(User).select_from(users.join(subq,
users.c.user_id==subq.c.user_id)).order_by(subq.c.friendcount)
--~--~-~--~~~---~--~~
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] cascade defaults
hi i have cascade= option of relation switched automaticaly between all and other values, and nothing. so i used cascade=None as nothing, hoping it will get the default behaviour. the result was some query got 6x slower (actualy thats why i went hunting it)... nope, it happens that source-wise, exactly cascade=False triggers the default behavior (whatever that is) and nothing else. Not really intuituve, well, fine, i'll fix me code, but the question still remains: why cascade=None or cascade='' or whatever empty thing makes queries (sqlite) sooo slow. i've compared the sql of the query - it's same. something in the schema went wrong? or result-processing? or what? is it worth chasing or there is a known reason? and a related suggestion: why not use symbols e.g. some singleton called DefaultValue, instead of any hard-to-guess default values (be them False, None, '', whatever)? the actual default values are mostly set up later, so the if something is DefaultValue: something = actual-default-value is there anyway. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Limit to 500 records after particular date.
Morning Guys, I hope this'll be a fairly simple question. I have a query which looks something like this: the_objects = session.query(myobject.myobject).filter(myobject.created :lastrecord).params(lastrecord=time.strftime(%Y-%m-%d %H:%M:%S, from_date)).all() This grabs all the records after a particular date and time. What I WANT it to achieve though is to grab the first 1000 records that happen after a period in time. How would you suggest that I handle my limits and order_by's on this? It's important that no records get missed out you see so I need to be totally sure that the 1000 records returned are the first 1000 records in chronological order after 'from_date'. I'm using SQLAclchemy 0.5 and MySQL 5 I appreciate any advice you can offer. Cheers, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Limit to 500 records after particular date.
first u get your query sorted in proper order (by timestamp or even dbid, or hierarchicaly via subselect + groupby + order, whatever) then yourquery[:1000] On Wednesday 24 September 2008 14:11:24 Heston James - Cold Beans wrote: Morning Guys, I hope this'll be a fairly simple question. I have a query which looks something like this: the_objects = session.query(myobject.myobject).filter(myobject.created :lastrecord).params(lastrecord=time.strftime(%Y-%m-%d %H:%M:%S, from_date)).all() This grabs all the records after a particular date and time. What I WANT it to achieve though is to grab the first 1000 records that happen after a period in time. How would you suggest that I handle my limits and order_by's on this? It's important that no records get missed out you see so I need to be totally sure that the 1000 records returned are the first 1000 records in chronological order after 'from_date'. I'm using SQLAclchemy 0.5 and MySQL 5 I appreciate any advice you can offer. Cheers, Heston --~--~-~--~~~---~--~~ 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: Problems with join query, can't figure out what's wrong. Noob alert!
K, seems that I've found the root cause:
When you are making the join from query, the syntax is:
app.session.query(Sak).join(Sak.prosjektid).filter(
where Sak.prosjektid is the relation property of the Sak class
or you can specify the target in the tuples form:
app.session.query(Sak).join((Prosjekt,Sak.prosjekt)), where
(Prosjekt,Sak.prosjekt) - is the tuple with what you join (first) and
how you join (second value)
when you are using commas inside this join, it thinks of the multiple
join statements, so it interprets your
.join(Prosjekt,Sak.prosjektid) as 2 joins requirements and fails
anyway, here is the complete example that works,
tables = {}
tables['prosjekt'] = Table('prosjekt', metadata,
Column('prosjektid', Integer, primary_key=True),
Column('p_prosjektid', Integer,
ForeignKey('prosjekt.prosjektid')),
Column('kundeid', Integer),
)
tables['sak'] = Table('sak', metadata,
Column('saksnr', Integer, primary_key = True),
Column('prosjektid', Integer,
ForeignKey('prosjekt.prosjektid')),
)
metadata.create_all()
mappers = {}
class Prosjekt(object):pass
class Sak(object):pass
mappers['prosjekt'] = mapper(Prosjekt, tables['prosjekt'],
properties = {
'sak': relation(Sak),
'p_prosjekt':
relation(Prosjekt,remote_side=[tables['prosjekt'].c.prosjektid])
})
mappers['sak'] = mapper(Sak, tables['sak'],
properties = {
'prosjekt': relation(Prosjekt),
})
objects =
app.session.query(Sak).join(Sak.prosjekt).filter(Prosjekt.kundeid==1532).all()
#or
objects =
app.session.query(Sak).join((Prosjekt,Sak.prosjekt)).filter(Prosjekt.kundeid==1532).all()
On 24 сент, 12:45, olavgg [EMAIL PROTECTED] wrote:
Thank you for your answer,
I did as you said but still returns the same error:
InvalidRequestError: Mapper 'Mapper|Sak|sak' has no property 'class
'Products.TikkTakk.model.Prosjekt.Prosjekt''
--~--~-~--~~~---~--~~
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: Problems with join query, can't figure out what's wrong. Noob alert!
Thank you again for a good answer. I still can't figure this out
though, Im getting the same error message all the time when I
use .join()
Could it be an issue with Elixir which I'm using? Or with Plone/Five?
What about just using a from_statement?
On Sep 24, 3:16 pm, Alex K [EMAIL PROTECTED] wrote:
K, seems that I've found the root cause:
When you are making the join from query, the syntax is:
app.session.query(Sak).join(Sak.prosjektid).filter(
where Sak.prosjektid is the relation property of the Sak class
or you can specify the target in the tuples form:
app.session.query(Sak).join((Prosjekt,Sak.prosjekt)), where
(Prosjekt,Sak.prosjekt) - is the tuple with what you join (first) and
how you join (second value)
when you are using commas inside this join, it thinks of the multiple
join statements, so it interprets your
.join(Prosjekt,Sak.prosjektid) as 2 joins requirements and fails
anyway, here is the complete example that works,
tables = {}
tables['prosjekt'] = Table('prosjekt', metadata,
Column('prosjektid', Integer, primary_key=True),
Column('p_prosjektid', Integer,
ForeignKey('prosjekt.prosjektid')),
Column('kundeid', Integer),
)
tables['sak'] = Table('sak', metadata,
Column('saksnr', Integer, primary_key = True),
Column('prosjektid', Integer,
ForeignKey('prosjekt.prosjektid')),
)
metadata.create_all()
mappers = {}
class Prosjekt(object):pass
class Sak(object):pass
mappers['prosjekt'] = mapper(Prosjekt, tables['prosjekt'],
properties = {
'sak': relation(Sak),
'p_prosjekt':
relation(Prosjekt,remote_side=[tables['prosjekt'].c.prosjektid])
})
mappers['sak'] = mapper(Sak, tables['sak'],
properties = {
'prosjekt': relation(Prosjekt),
})
objects =
app.session.query(Sak).join(Sak.prosjekt).filter(Prosjekt.kundeid==1532).all()
#or
objects =
app.session.query(Sak).join((Prosjekt,Sak.prosjekt)).filter(Prosjekt.kundeid==1532).all()
On 24 сент, 12:45, olavgg [EMAIL PROTECTED] wrote:
Thank you for your answer,
I did as you said but still returns the same error:
InvalidRequestError: Mapper 'Mapper|Sak|sak' has no property 'class
'Products.TikkTakk.model.Prosjekt.Prosjekt''
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Limit to 500 records after particular date.
Hi,
Thanks for the response, that gave me a good foot in the door to this.
I've now appened my existing query with.
.order_by('myobject.created')[:1000]
Which appears to give the desired result set, however, when looking
through the SQL debug output it seems that the limit their is applied
using python and not SQL, in as much as SQL returns ALL the records
and then the app splits it up. This doesnt feel particularly
performant too me and I would ideal prefer the limit to be imposed at
a SQL level, is this possible?
Thanks for your advice so far,
Heston
On Sep 24, 12:06 pm, [EMAIL PROTECTED] wrote:
first u get your query sorted in proper order (by timestamp or even
dbid, or hierarchicaly via subselect + groupby + order, whatever)
then yourquery[:1000]
On Wednesday 24 September 2008 14:11:24 Heston James - Cold Beans
wrote:
Morning Guys,
I hope this'll be a fairly simple question. I have a query which
looks something like this:
the_objects =
session.query(myobject.myobject).filter(myobject.created
:lastrecord).params(lastrecord=time.strftime(%Y-%m-%d %H:%M:%S,
from_date)).all()
This grabs all the records after a particular date and time. What I
WANT it to achieve though is to grab the first 1000 records that
happen after a period in time. How would you suggest that I handle
my limits and order_by's on this? It's important that no records
get missed out you see so I need to be totally sure that the 1000
records returned are the first 1000 records in chronological order
after 'from_date'.
I'm using SQLAclchemy 0.5 and MySQL 5
I appreciate any advice you can offer.
Cheers,
Heston- Hide quoted text -
- Show quoted text -
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Limit to 500 records after particular date.
Heston,
Heston James wrote:
Hi,
Thanks for the response, that gave me a good foot in the door to this.
I've now appened my existing query with.
.order_by('myobject.created')[:1000]
Just tried this on a simple example:
query = query.limit(500)
Quote from doc for 0.5rc1:
*def limit(/self/, /limit/)*
Apply a LIMIT to the query and return the newly resulting Query.
*def offset(/self/, /offset/)*
Apply an OFFSET to the query and return the newly resulting Query.
*def slice(/self/, /start/, /stop/)*
apply LIMIT/OFFSET to the Query based on a range and return the newly
resulting Query.
Werner
--~--~-~--~~~---~--~~
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: Fwd: Support for ordered lists of child items
Emmett Lazich wrote: Thank you Jason. orderinglist looks like what I am after! Is your orderinglist plugin fully functional in 0.4.7p1? Yep. Before I attempt it, pls advise if there any technical reason preventing the integration of orderinglist into the basic_tree.py (adjacency list) example? See http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/adjacencytree/basic_tree.py That example uses a dict-based collection, so it's not a drop in. But I don't see any obstacle to using the orderinglist on adjacency lists in general. jason kirtland wrote: Yep, orderinglist handles that case. Michael Bayer wrote: forwarded from pvt email orderinglist ? Begin forwarded message: *From: *Emmett [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *Date: *September 22, 2008 9:51:31 AM EDT *To: *Michael Bayer [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *Subject: **Re: Support for ordered lists of child items* Hello Michael, 18 months later, would your answer to Aaron still be the same? I have a problem fitting what Aaron described. ie. save+restore of child order after re-ordering in the Python side. Re-ordering child list elements would obviously be ideal, but I could cope with updating an extra integer node attribute instead. I'm completely new to SA and at this stage skimming documentation and looking at the tree examples. Found this thread, so wondering if some newer SA magic can solve this, or if a custom collection class or something else is the best solution. I also looked at the ElementTree examples, but they don't appear to guarantee child order either - correct? Thanks in advance. I looked at your activity in this group. Amazing! On Apr 10 2007, 4:02 am, Michael Bayer [EMAIL PROTECTED] wrote: we dont have the capability to automatically update ordering columns when the elements of a list are moved around. if you move the elements around, you need to execute some step that will update the index columns (or create a custom collection class that does this for you). On Apr 9, 2007, at 12:42 PM, Aaron Digulla wrote: Hello, I'm looking for a feature but couldn't find it in the docs. I have atreelike structure where the user can specify theorderof thechildrenof a node. In DB lingo, I have a parentId and an index column. When I loadchildren, they should be ordered by the index. This seems to be supported. Can SA also update the index column when I movechildrenin the list around? Like: # ... parent has threechildrenA, B C item = parent.children[0] del parent.children[0] parent.children.insert (1, item) # now, parent has threechildrenB, A, C Regards, --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Limit to 500 records after particular date.
Werner,
Thank you for your response, very kind of you. This looks to be more
what I'm looking for, after a quick test it seems that it is now
applying the limit at the SQL level which is definitly a good thing.
Where abouts in the documentation did you find that? Look here:
http://www.sqlalchemy.org/docs/05/ormtutorial.html is seems to
reccomend the same method as reccomended by the first repsonder, is
that a fault in the docs perhaps?
The next challegne I've noticed with using Limit() is that it doesnt
appear to return an array of objects, but instead, a query object, so
when I try and perform an evaluation on it like so:
if len(the_objects):
I get an error which states:
TypeError: object of type 'Query' has no len()
Why is this? Does using Limit() mean that we're returning query
objects instead of the array of objects I was getting before?
Many thanks,
Heston
On Sep 24, 4:27 pm, Werner F. Bruhin [EMAIL PROTECTED] wrote:
Heston,
Heston James wrote:
Hi,
Thanks for the response, that gave me a good foot in the door to this.
I've now appened my existing query with.
.order_by('myobject.created')[:1000]
Just tried this on a simple example:
query = query.limit(500)
Quote from doc for 0.5rc1:
*def limit(/self/, /limit/)*
Apply a LIMIT to the query and return the newly resulting Query.
*def offset(/self/, /offset/)*
Apply an OFFSET to the query and return the newly resulting Query.
*def slice(/self/, /start/, /stop/)*
apply LIMIT/OFFSET to the Query based on a range and return the newly
resulting Query.
Werner
--~--~-~--~~~---~--~~
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: Cyclic references and Nullable
Thank you - i think it is a solution SA's Constraint types have support for generating a deferrable key at CREATE TABLE table: http://www.sqlalchemy.org/docs/05/sqlalchemy_schema.html#docstrings_s... I believe you can either define the cyclic constraints as initially deferred for this behavior, or issue a SET ALL CONSTRAINTS DEFERRED during the transaction to loosen up any deferrable keys. SQL also allows naming specific constraints for the second form, check your db manual for it's take on it. --~--~-~--~~~---~--~~ 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: Performance problem related to saving newly created objects.
Awesome! Thank you very much indeed for the follow-up. After turning of expire_on_commit, saving a newly created object takes less than 1/3 of a second even with 100K objects in memory. It's always nice to find the fast = True switch. :-) On Sep 23, 6:00 pm, Michael Bayer [EMAIL PROTECTED] wrote: I misread your ticket and the resolution has been corrected. The commit() operation expires all objects present in the session as described inhttp://www.sqlalchemy.org/docs/05/session.html#unitofwork_using_commi... . Turn off expire_on_commit to disable the expiration operation, which normally would loop through all 100K objects and set an expiration flag for all of their attributes. Keep in mind that if any concurrent transactions take place subsequent to the end of your transaction, with expire_on_commit=False you may be operating with stale data. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Limit to 500 records after particular date.
Heston, Heston James wrote: Werner, Thank you for your response, very kind of you. This looks to be more what I'm looking for, after a quick test it seems that it is now applying the limit at the SQL level which is definitly a good thing. Where abouts in the documentation did you find that? Look here: http://www.sqlalchemy.org/docs/05/ormtutorial.html is seems to reccomend the same method as reccomended by the first repsonder, is that a fault in the docs perhaps? It is from the api doc, see here: http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_query.html When I search for things on the doc page I mostly click first on the link in the top right One page, unless I know which section contains what I am looking for. The next challegne I've noticed with using Limit() is that it doesnt appear to return an array of objects, but instead, a query object, so when I try and perform an evaluation on it like so: if len(the_objects): I get an error which states: TypeError: object of type 'Query' has no len() Why is this? Does using Limit() mean that we're returning query objects instead of the array of objects I was getting before? Yes (now take my answer with a grain of salt, I am by no means an SA expert, nor even an advanced user). In the 0.5 version you can do things like: query = session.query(db.Mytable) query = query.order_by() query = query.limit(3) ... etc but then you need to tell it to actually get the data, with .first(), .one(), .all() or iterate over it, e.g. for aninstance in query: print aninstance Hope this helps, and if I said something wrong I hope someone corrects me. Werner --~--~-~--~~~---~--~~ 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] Is the string 'version' a reserved word or not usable in relations in SA?
There is a case in my application where setting the attribute 'version' which is the name of a relationship to a look up table. When I avoid setting this attribute, saving the record takes 0.15s, however when I set the value of this relationship, saving the record takes upwards of 30 seconds. Furthermore I am seeing a CircularDependencyError, but only when I set the value of this attribute: sqlalchemy.exc.CircularDependencyError: Circular dependency detected [(sqlalchemy.orm.identity.IdentityManagedState object at 0x37ff070, sqlalchemy.orm.identity.IdentityManagedState object at 0x1808110), (sqlalchemy.orm.identity.IdentityManagedState object at 0x1808110, sqlalchemy.orm.identity.IdentityManagedState object at 0x37ff070)][] Is this error indicative of a problem with the relationship I've defined? --~--~-~--~~~---~--~~ 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: Is the string 'version' a reserved word or not usable in relations in SA?
Replying to my own post: It appears that I've resolved this problem by removing the reciprocal relationship (backref) from this particular relation. Though it is a bit of a head-scratcher as to why the removal of the backref resolves the circular dependency problem given that this table has many other relationships defined to other lookup tables that are modeled identically and do not exhibit any circular dependency problems. On Sep 24, 10:49 am, Randallabra [EMAIL PROTECTED] wrote: There is a case in my application where setting the attribute 'version' which is the name of a relationship to a look up table. When I avoid setting this attribute, saving the record takes 0.15s, however when I set the value of this relationship, saving the record takes upwards of 30 seconds. Furthermore I am seeing a CircularDependencyError, but only when I set the value of this attribute: sqlalchemy.exc.CircularDependencyError: Circular dependency detected [(sqlalchemy.orm.identity.IdentityManagedState object at 0x37ff070, sqlalchemy.orm.identity.IdentityManagedState object at 0x1808110), (sqlalchemy.orm.identity.IdentityManagedState object at 0x1808110, sqlalchemy.orm.identity.IdentityManagedState object at 0x37ff070)][] Is this error indicative of a problem with the relationship I've defined? --~--~-~--~~~---~--~~ 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] column renaming and adding
I have a setup like this:
class Entity(object): pass
class Persoon(Entity): pass
entity_tabel = Table('contact', metadata,
Column('contactid', Integer, primary_key=True),
Column('voornaam', String(75)),
Column('naam', String(75)),
Column('organisatietypeid', Integer))
entity_mapper = mapper(self.Entity, entity_tabel,
polymorphic_on=entity_tabel.c.organisatietypeid,
polymorphic_identity=None)
persoon_mapper = mapper(self.Persoon,
inherits=entity_mapper,
polymorphic_identity=None,
properties={
'achternaam':entity_tabel.c.naam})
Now, I want to add a 'Persoon':
p = Persoon()
p.voornaam = 'firstname'
p.achternaam = 'surname'
mySession.add(p)
mySession.commit()
But this leaves my 'naam' field to 'NULL'. Only if I do:
p = Persoon()
p.voornaam = 'firstname'
p.naam = 'surname'
mySession.add(p)
mySession.commit()
Things work as they should.
How is this possible? I thought that by saying:
properties={'achternaam':entity_tabel.c.naam}
you actually rename the column, but it seems it creates another
attribute which would lead to unexpected results in my case.
Anybody more info about this?
--~--~-~--~~~---~--~~
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: Problems with join query, can't figure out what's wrong. Noob alert!
I've never used Elixir/Plone, I'll try you example with Elixir tomorow
and will let you know.
On 24 сент, 18:27, olavgg [EMAIL PROTECTED] wrote:
Thank you again for a good answer. I still can't figure this out
though, Im getting the same error message all the time when I
use .join()
Could it be an issue with Elixir which I'm using? Or with Plone/Five?
What about just using a from_statement?
On Sep 24, 3:16 pm, Alex K [EMAIL PROTECTED] wrote:
K, seems that I've found the root cause:
When you are making the join from query, the syntax is:
app.session.query(Sak).join(Sak.prosjektid).filter(
where Sak.prosjektid is the relation property of the Sak class
or you can specify the target in the tuples form:
app.session.query(Sak).join((Prosjekt,Sak.prosjekt)), where
(Prosjekt,Sak.prosjekt) - is the tuple with what you join (first) and
how you join (second value)
when you are using commas inside this join, it thinks of the multiple
join statements, so it interprets your
.join(Prosjekt,Sak.prosjektid) as 2 joins requirements and fails
anyway, here is the complete example that works,
tables = {}
tables['prosjekt'] = Table('prosjekt', metadata,
Column('prosjektid', Integer, primary_key=True),
Column('p_prosjektid', Integer,
ForeignKey('prosjekt.prosjektid')),
Column('kundeid', Integer),
)
tables['sak'] = Table('sak', metadata,
Column('saksnr', Integer, primary_key = True),
Column('prosjektid', Integer,
ForeignKey('prosjekt.prosjektid')),
)
metadata.create_all()
mappers = {}
class Prosjekt(object):pass
class Sak(object):pass
mappers['prosjekt'] = mapper(Prosjekt, tables['prosjekt'],
properties = {
'sak': relation(Sak),
'p_prosjekt':
relation(Prosjekt,remote_side=[tables['prosjekt'].c.prosjektid])
})
mappers['sak'] = mapper(Sak, tables['sak'],
properties = {
'prosjekt': relation(Prosjekt),
})
objects =
app.session.query(Sak).join(Sak.prosjekt).filter(Prosjekt.kundeid==1532
).all()
#or
objects =
app.session.query(Sak).join((Prosjekt,Sak.prosjekt)).filter(Prosjekt.kundei
d==1532).all()
On 24 сент, 12:45, olavgg [EMAIL PROTECTED] wrote:
Thank you for your answer,
I did as you said but still returns the same error:
InvalidRequestError: Mapper 'Mapper|Sak|sak' has no property 'class
'Products.TikkTakk.model.Prosjekt.Prosjekt''
--~--~-~--~~~---~--~~
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] Newbie many-to-many using Declarative question
Hi all,
I'm trying to set up a many-to-many system using the Declarative syntax
against an existing MySQL set of tables. We're using a 'link' table
between two other tables we want to relate many-to-many. Here's the
simplified layout of those tables:
mysql desc press_routing_press;
+--+--+--+-+-+--
-+
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-+--
-+
| press_routing_id | int(10) unsigned | NO | MUL | |
|
| press_id | int(10) unsigned | NO | MUL | |
|
| type | enum('new','rework') | YES | | NULL|
|
+--+--+--+-+-+--
-+
mysql desc press;
+-+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| id | int(10) unsigned | NO | PRI | | |
| code| varchar(15) | YES | | NULL| |
| name| varchar(25) | YES | | NULL| |
+-+--+--+-+-+---+
mysql desc press_routing;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| id | int(10) unsigned | NO | PRI | | |
| code | varchar(20) | NO | | | |
| press| int(10) unsigned | NO | | | |
+--+--+--+-+-+---+
And here's the Python SqlAlchemy code I've put together trying to
describe this:
class PressRoutingPress(Base):
'''This class defines the many-to-many join table between press
and press_routing.
'''
__tablename__ = press_routing_press
__table_args__ = {'autoload' : True}
press_id = Column(Integer, ForeignKey('press.id'), primary_key=True)
press_routing_id = Column(Integer, ForeignKey('press_routing.id'),
primary_key=True)
class PressRouting(Base):
'''This class defines the press_routing table information.
'''
__tablename__ = press_routing
__table_args__ = {'autoload' : True}
class Press(Base):
'''This class defines the press table information.
'''
__tablename__ = press
__table_args__ = {'autoload' : True}
# many to many Press-PressRouting
press_routing = relation('PressRouting',
secondary=PressRoutingPress,
primaryjoin=id==PressRoutingPress.press_id,
foreign_keys=[PressRoutingPress.press_id],
secondaryjoin=PressRouting.id==PressRoutingPress.press_routing_id,
foreign_keys=[PressRoutingPress.press_routing_id],
uselist=False)
#backref=backref('press'))
#viewonly=True)
This all works till I try to instantiate an instance of a Press()
object, then I get the following exception:
sqlalchemy.exc.ArgumentError: Could not determine relation direction for
primaryjoin condition 'press_routing_press.press_id = %s', on relation
Press.press_routing. Are the columns in 'foreign_keys' present within
the given join condition ?
I've tinkered around with various things in the relation() defined in
the Press class, but that just seems to generate other exceptions. From
what I've read the above code looks closest to something that should
work based on what I've seen others posting.
Any help and/or guidance would be appreciated, thanks!
Doug
--~--~-~--~~~---~--~~
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: cascade defaults
On Sep 24, 2008, at 6:23 AM, [EMAIL PROTECTED] wrote: hi i have cascade= option of relation switched automaticaly between all and other values, and nothing. so i used cascade=None as nothing, hoping it will get the default behaviour. the result was some query got 6x slower (actualy thats why i went hunting it)... nope, it happens that source-wise, exactly cascade=False triggers the default behavior (whatever that is) and nothing else. Not really intuituve, well, fine, i'll fix me code, but the question still remains: why cascade=None or cascade='' or whatever empty thing makes queries (sqlite) sooo slow. i've compared the sql of the query - it's same. something in the schema went wrong? or result-processing? or what? is it worth chasing or there is a known reason? wild guess, something to do with objects that normally would have been cascaded into the session were not, and are being loaded instead. but otherwise no clue, you'd have to provide specifics. and a related suggestion: why not use symbols e.g. some singleton called DefaultValue, instead of any hard-to-guess default values (be them False, None, '', whatever)? the actual default values are mostly set up later, so the if something is DefaultValue: something = actual-default-value is there anyway. i think the foo=False as a default might have been some habit I picked up when looking at the source code to SQLObject (or maybe SQLObject2). I didn't think we had too much public API with the default=False thing going on but I havent taken a poll.Usually these defaults should just be documented. A symbol like DefaultValue might be nice except I don't see how that's any more obvious for someone who isn't reading docstrings. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Limit to 500 records after particular date.
On Sep 24, 2008, at 11:02 AM, Heston James wrote:
Hi,
Thanks for the response, that gave me a good foot in the door to this.
I've now appened my existing query with.
.order_by('myobject.created')[:1000]
Which appears to give the desired result set, however, when looking
through the SQL debug output it seems that the limit their is applied
using python and not SQL,
issue the slice as [0:1000] and you'll get the LIMIT/OFFSET. Ticket
#1177 will address the [:1000] usage (and 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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Cyclic references and Nullable
On Sep 24, 2008, at 12:11 PM, jason kirtland wrote: SA's Constraint types have support for generating a deferrable key at CREATE TABLE table: http://www.sqlalchemy.org/docs/05/sqlalchemy_schema.html#docstrings_sqlalchemy.schema_Constraint I believe you can either define the cyclic constraints as initially deferred for this behavior, or issue a SET ALL CONSTRAINTS DEFERRED during the transaction to loosen up any deferrable keys. SQL also allows naming specific constraints for the second form, check your db manual for it's take on it. shame on me for not RTFM ! --~--~-~--~~~---~--~~ 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: Is the string 'version' a reserved word or not usable in relations in SA?
On Sep 24, 2008, at 2:16 PM, Randallabra wrote: Replying to my own post: It appears that I've resolved this problem by removing the reciprocal relationship (backref) from this particular relation. Though it is a bit of a head-scratcher as to why the removal of the backref resolves the circular dependency problem given that this table has many other relationships defined to other lookup tables that are modeled identically and do not exhibit any circular dependency problems. this usually indicates that the backref is not set up properly, but typically with a self-referential relationship (i.e., a mapper that has a relation pointing back to itself). This sounds like its not a self-referential relationship since you're saying its a lookup table. Its possible some other element of the relation() chain is misconfigured (such that a o2m thinks its m2o, or vice versa) which would cause this issue to happen (and removing any part of the chain could resolve it). We can diagnose the issue very quickly if you can provide a short test case illustrating your mappings. --~--~-~--~~~---~--~~ 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: Newbie many-to-many using Declarative question
On Sep 24, 2008, at 5:19 PM, Doug Farrell wrote:
class PressRoutingPress(Base):
'''This class defines the many-to-many join table between press
and press_routing.
'''
__tablename__ = press_routing_press
__table_args__ = {'autoload' : True}
press_id = Column(Integer, ForeignKey('press.id'),
primary_key=True)
press_routing_id = Column(Integer, ForeignKey('press_routing.id'),
primary_key=True)
class PressRouting(Base):
'''This class defines the press_routing table information.
'''
__tablename__ = press_routing
__table_args__ = {'autoload' : True}
class Press(Base):
'''This class defines the press table information.
'''
__tablename__ = press
__table_args__ = {'autoload' : True}
# many to many Press-PressRouting
press_routing = relation('PressRouting',
secondary=PressRoutingPress,
primaryjoin=id==PressRoutingPress.press_id,
foreign_keys=[PressRoutingPress.press_id],
secondaryjoin=PressRouting.id==PressRoutingPress.press_routing_id,
foreign_keys=[PressRoutingPress.press_routing_id],
uselist=False)
#backref=backref('press'))
#viewonly=True)
This all works till I try to instantiate an instance of a Press()
object, then I get the following exception:
when you use the secondary argument on relation(), that should be a
plain Table object and should not be mapped (i.e. there should be no
separate class for it):
press_routing_press = Table(press_routing_press, Base.metadata,
Column(press_id, Integer, ForeignKey('press.id'), primary_key=True),
Column(press_routing_id, Integer,
ForeignKey('press_routing.id'),primary_key=True)
)
class Press(Base):
...
press_routing = relation(PressRouting,
secondary=press_routing_press)
no other arguments to relation() are needed.
If you do want PressRoutingPress to be mapped, you use the association
object pattern, which means you aren't using the secondary keyword.
The non-declarative version is here:
http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relation_patterns_association
--~--~-~--~~~---~--~~
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] Unicode Results from SQL functions
I am trying to construct a select query in mysql (version
5.0.51a-3ubuntu5.3-log) using SQL functions. Once I set the convert_unicode
flag = True on my engine some function results are returned as type str and
some results are returned as type unicode (I want, and expected, all
unicode). Although this problem can be resolved with an explict cast I
suspect there might be a deeper problem.
It seems that when an obvious string function is used (i.e. CONCAT in my
case) then unicode is correctly returned. However when the return type is
dependent on the results of the query (i.e. IFNULL, could return any type
dependent on the arguments) then a str is returned.
Am I just missing something or is this a problem with mysql or is there some
other problem? Sample code and output is included below.
Thanks in advance,
Shawn
=== Sample Output ==
Executing command:
/usr/lib/python2.5/site-packages/aPyIdea-0.1.0a1-py2.5.egg/apyidea/pause.py
/usr/bin/python /home/schurch/Projects/MTZAuto/mtzauto/model/test.py
Simple query, Unicode OK: [(1L, u'a', u'b'), (2L, u'c', u'd')]
IFNULL, No Unicode: [('a', 'b'), ('c', 'd')]
CAST to Unicode, Unicode OK: [(u'a', u'b'), (u'c', u'd')]
CAST to String, Unicode OK: [(u'a', u'b'), (u'c', u'd')]
CONCAT(results), Unicode OK: [(u'a', u'b'), (u'c', u'd')]
Press enter to close window
= test.py ==
from elixir import *
from sqlalchemy import select, func, or_, create_engine, union, cast
class Test(Entity):
field1 = Field(Unicode(50))
field2 = Field(Unicode(50))
if __name__ == __main__:
setup_all()
uri = mysql://test:[EMAIL PROTECTED]/test
engine = create_engine(uri, encoding = latin1, convert_unicode = True)
engine.execute('SET collation_connection = latin1_general_ci')
metadata.bind = engine
metadata.bind.echo = False
drop_all()
create_all()
Test(field1 = ua, field2 = ub)
Test(field1 = uc, field2 = ud)
session.commit()
t = Test.table
q = select([Test.table])
# Simple query
print Simple query, Unicode OK:, list(session.execute(q))
# Query with IFNULL functions that return strings
field2 = func.ifnull(t.c.field2, uUnknown)
field1 = func.ifnull(t.c.field1, field2)
q = select([field1, field2])
print \nIFNULL, No Unicode:, list(session.execute(q))
# Results of IFNULL explicity CAST to Unicode
field2 = cast(func.ifnull(t.c.field2, uUnknown), Unicode)
field1 = cast(func.ifnull(t.c.field1, field2), Unicode)
q = select([field1, field2])
print \nCAST to Unicode, Unicode OK:, list(session.execute(q))
# Results of IFNULL explicity CAST to String
field2 = cast(func.ifnull(t.c.field2, uUnknown), String)
field1 = cast(func.ifnull(t.c.field1, field2), String)
q = select([field1, field2])
print \nCAST to String, Unicode OK:, list(session.execute(q))
# Results of IFNULL used in CONCAT
field2 = func.concat(func.ifnull(t.c.field2, uUnknown))
field1 = func.concat(func.ifnull(t.c.field1, field2))
q = select([field1, field2])
print \nCONCAT(results), Unicode OK:, list(session.execute(q))
--~--~-~--~~~---~--~~
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: Unicode Results from SQL functions
On Sep 24, 2008, at 10:13 PM, Shawn Church wrote: I am trying to construct a select query in mysql (version 5.0.51a-3ubuntu5.3-log) using SQL functions. Once I set the convert_unicode flag = True on my engine some function results are returned as type str and some results are returned as type unicode (I want, and expected, all unicode). Although this problem can be resolved with an explict cast I suspect there might be a deeper problem. It seems that when an obvious string function is used (i.e. CONCAT in my case) then unicode is correctly returned. However when the return type is dependent on the results of the query (i.e. IFNULL, could return any type dependent on the arguments) then a str is returned. Am I just missing something or is this a problem with mysql or is there some other problem? Sample code and output is included below. the func.XXX() construct can draw upon a module of known functions such as CONCAT, in which case the return type of the function is known (sqlalchemy terms these generic functions). However, if the function name given is not a known function, then the return type is not known to be a string (which is necessary for convert_unicode to take effect).To coerce an arbitrary func to apply String processing to the result, use the type_ parameter: func.foo(arg1, arg2, ..., type_=Unicode) we can of course add more functions to the list of known functions such as ifnull() (it would be best if ifnull() is a SQL standard function, I'm not sure if it is). --~--~-~--~~~---~--~~ 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: Unicode Results from SQL functions
On Wed, Sep 24, 2008 at 7:37 PM, Michael Bayer [EMAIL PROTECTED]wrote: we can of course add more functions to the list of known functions such as ifnull() (it would be best if ifnull() is a SQL standard function, I'm not sure if it is). Not sure this will work for IFNULL since it's type depends upon the runtime arguments. I missed the func type_ argument when I read the documentation. That is a good solution for the general case of specifiying the type when it cannot be determined from the function or the function arguments. In fact I'm going to use it any time the type is not obvious. For what it is worth the following patch modifies ResultProxy to convert strings to unicode if convert_unicode == True. It 'fixes' my example and test/testall.py still passes. [EMAIL PROTECTED]:~/Projects/sqlalchemy$ svn diff Index: lib/sqlalchemy/engine/base.py === --- lib/sqlalchemy/engine/base.py(revision 5123) +++ lib/sqlalchemy/engine/base.py(working copy) @@ -1630,7 +1630,10 @@ if processor: return processor(row[index]) else: -return row[index] +result = row[index] +if isinstance(result, str) and self.context.dialect.convert_unicode: +result = result.decode(self.context.dialect.encoding) +return result def _fetchone_impl(self): return self.cursor.fetchone() [EMAIL PROTECTED]:~/Projects/sqlalchemy$ Thanks, Shawn --~--~-~--~~~---~--~~ 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: Unicode Results from SQL functions
Shawn Church wrote: On Wed, Sep 24, 2008 at 7:37 PM, Michael Bayer [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: we can of course add more functions to the list of known functions such as ifnull() (it would be best if ifnull() is a SQL standard function, I'm not sure if it is). Not sure this will work for IFNULL since it's type depends upon the runtime arguments. I missed the func type_ argument when I read the documentation. That is a good solution for the general case of specifiying the type when it cannot be determined from the function or the function arguments. In fact I'm going to use it any time the type is not obvious. For what it is worth the following patch modifies ResultProxy to convert strings to unicode if convert_unicode == True. It 'fixes' my example and test/testall.py still passes. Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
