[sqlalchemy] How can I perform one query with a sort on unicode field with sqlalchemy and sqlite backend. I need to use collations features ?
Hi, I use sqlalchemy with sqlite backend. I've one table with unicodes fields. When I launch one query with order statement on unicode field, I've bad sort result. I've one issue with all no ASCII characters. I've see some information about sqlite collation ( http://www.sqlite.org/capi3ref.html#sqlite3_create_collation ) and pysqlite collation ( http://initd.org/pub/software/pysqlite/doc/usage-guide.html#creating-and- using-collations ). I found also locale.strcoll(string1, string2) ( http://docs.python.org/library/locale.html?highlight=locale#module- locale) to compare two unicode string. Now, I wonder how can I perform a unicode sort with sqlalchemy and sqlite backend ? Thanks for your help, Stephane --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Mapper with declarative
Hello everyone, I have a concrete question about using declarative mapper in my project. There are three tables: user, services and objects Class Services(Base): user_id = Column(user_id, Integer, ForeignKey (user_table.user_id)) .. Class Objects(Base): user_id = Column(user_id, Integer, ForeignKey (user_table.user_id)) ... Class User(Base): ... service = relation(Objects, backref = user) object = relation(Services, backref = user) When i there appeared some errors. I wonder whether declarative mapper supports two relation in a single class. Thanks! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Mapper with declarative
Hello everyone, I have a concrete question about using declarative mapper in my project. There are three tables: user, services and objects Class Services(Base): user_id = Column(user_id, Integer, ForeignKey (user_table.user_id)) .. Class Objects(Base): user_id = Column(user_id, Integer, ForeignKey (user_table.user_id)) ... Class User(Base): ... service = relation(Objects, backref = user) object = relation(Services, backref = user) When i there appeared some errors. I wonder whether declarative mapper supports two relation in a single class. Thanks! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Mapper with declarative
Hello everyone, I have a concrete question about the declarative mapper in my project. There are three tables: user, services and objects Class Services(Base): mote_id = Column(mote_id, Integer, ForeignKey (mote_table.mote_id)) Class Objects(Base): mote_id = Column(mote_id, Integer, ForeignKey (mote_table.mote_id)) Class User(Base): r Yet, in User class i have to which appear to be a error since mapper with declarative always unique. How can i implement the relation between user and the two tables though? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Mapper with declarative
Hello everyone, I have a concrete question about using declarative mapper in my project. There are three tables: user, services and objects Class Services(Base): user_id = Column(user_id, Integer, ForeignKey (user_table.user_id)) .. Class Objects(Base): user_id = Column(user_id, Integer, ForeignKey (user_table.user_id)) ... Class User(Base): ... service = relation(Objects, backref = user) object = relation(Services, backref = user) When i there appeared some errors. I wonder whether declarative mapper supports two relation in a single class. Thanks! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How can I perform one query with a sort on unicode field with sqlalchemy and sqlite backend. I need to use collations features ?
you're free to access the raw connection and use the pysqlite create_collation method. If you'd like to assemble this on all connections, look into the PoolListener API described in http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/interfaces.html?highlight=poollistener#sqlalchemy.interfaces.PoolListener and implement the connect method. KLEIN Stéphane wrote: Hi, I use sqlalchemy with sqlite backend. I've one table with unicodes fields. When I launch one query with order statement on unicode field, I've bad sort result. I've one issue with all no ASCII characters. I've see some information about sqlite collation ( http://www.sqlite.org/capi3ref.html#sqlite3_create_collation ) and pysqlite collation ( http://initd.org/pub/software/pysqlite/doc/usage-guide.html#creating-and- using-collations ). I found also locale.strcoll(string1, string2) ( http://docs.python.org/library/locale.html?highlight=locale#module- locale) to compare two unicode string. Now, I wonder how can I perform a unicode sort with sqlalchemy and sqlite backend ? Thanks for your help, Stephane --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapper with declarative
Scripper wrote: When i there appeared some errors. I wonder whether declarative mapper supports two relation in a single class. Thanks! a single class can have as many relation()s or other properties as you'd like. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: eagerload and count() doesn't use join
sector119 wrote:
meta.Session.query(model.Report).join((model.User,
model.Report.reporter_id==model.User.id)).filter(model.User.office_id==62).count()
18:08:29,568 INFO [sqlalchemy.engine.base.Engine.0x...888c] SELECT
count(1) AS count_1
FROM system.reports JOIN system.users ON system.reports.reporter_id =
system.users.id
WHERE system.users.office_id = %(office_id_1)s
18:08:29,569 INFO [sqlalchemy.engine.base.Engine.0x...888c]
{'office_id_1': 62}
14L
correct. you are joining from Report to User and getting a count of
Report rows.
meta.Session.query(model.Report).options(eagerload('reporter')).filter(model.User.office_id==62).count()
18:09:08,488 INFO [sqlalchemy.engine.base.Engine.0x...888c] SELECT
count(1) AS count_1
FROM system.users, system.reports
WHERE system.users.office_id = %(office_id_1)s
18:09:08,489 INFO [sqlalchemy.engine.base.Engine.0x...888c]
{'office_id_1': 62}
42L
here, your query is incorrect. you are not specifying a join between
Report and User, and you are getting a cartesian product, hence 42
results. the usage of eagerload does not change those results. See
http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOIN
.
len(meta.Session.query(model.Report).options(eagerload('reporter')).filter(model.User.office_id==62).all())
18:09:22,053 INFO [sqlalchemy.engine.base.Engine.0x...888c] SELECT
...
FROM system.users, system.reports LEFT OUTER JOIN system.users AS
users_1 ON users_1.id = system.reports.reporter_id
WHERE system.users.office_id = %(office_id_1)s
18:09:22,054 INFO [sqlalchemy.engine.base.Engine.0x...888c]
{'office_id_1': 62}
14
Again, you are not specifying a join between Report and User, so you are
still getting a cartesian product here. But SQLAlchemy filters redundant
instances with the same primary key into a unique list so the end result
is 14. If you queried instead for model.Report.id you'd see 42 again.
Again, the eagerload() has no effect on the results.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapper with declarative
On Tue, 2009-04-07 at 06:28 -0700, Scripper wrote: Hello everyone, I have a concrete question about using declarative mapper in my project. When i there appeared some errors. I wonder whether declarative mapper supports two relation in a single class. Thanks! Well, so far, that's an abstract question. The answer (as Michael told you) is yes. But what errors are you getting, specifically (full traceback would be helpful)? Cheers, Cliff --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How can I perform one query with a sort on unicode field with sqlalchemy and sqlite backend. I need to use collations features ?
On 7 avr, 16:31, Michael Bayer mike...@zzzcomputing.com wrote:
you're free to access the raw connection and use the pysqlite
create_collation method. If you'd like to assemble this on all
connections, look into the PoolListener API described
inhttp://www.sqlalchemy.org/docs/05/reference/sqlalchemy/interfaces.htm...
and implement the connect method.
Ok, I've appended my collation function with dbapi_con.create_collation
(my_collate, my_collate) but I don't know how can I use my collation
in query statement.
::
import locale
from sqlalchemy.interfaces import PoolListener
from sqlalchemy import Table, MetaData
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import mapper
locale.setlocale(locale.LC_ALL,fr_FR.UTF-8)
def my_collate(string1, string2):
return locale.strcoll(string1, string2)
class MyListener(PoolListener):
def connect(self, dbapi_con, con_record):
dbapi_con.create_collation(my_collate, my_collate)
engine = create_engine('sqlite:///development.db', echo=True,
listeners = [MyListener()])
metadata = MetaData()
metadata.bind = engine
clients = Table('client', metadata, autoload = True)
Session = sessionmaker(bind=engine)
session = Session()
class Client(object):
pass
mapper(Client, clients)
result = session.query(Client).order_by(brand_name)
How can I add collation this query ?
Regards,
Stephane
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] 0.5.3 and mssql
Hello,
Is this still proper connection string for mssql where I specify the
tds version and a connection driver name?
e =
sqlalchemy.create_engine(mssql://user:p...@server:1433/db_name?driver=TDSodbc_options='TDS_Version=8.0')
What is the syntax for driver, and how can I pass TDS_Version=8.0 to
the end of connection string?
Thanks,
Lucas
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py,
line 198, in __init__
self.connection = self.__connect()
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py,
line 261, in __connect
connection = self.__pool._creator()
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/strategies.py,
line 80, in connect
raise exc.DBAPIError.instance(None, None, e)
sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001]
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0)
(SQLDriverConnectW)') None None
--
How to create python package?
http://lucasmanual.com/mywiki/PythonPaste
DataHub - create a package that gets, parses, loads, visualizes data
http://lucasmanual.com/mywiki/DataHub
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: select A join B where B is null
Thanks that ended up working. so a query like this would do it: session.query(JobInfo).filter(~JobInfo.Job.has() -- David Gardner Pipeline Tools Programmer, Sid the Science Kid Jim Henson Creature Shop dgard...@creatureshop.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How can I perform one query with a sort on unicode field with sqlalchemy and sqlite backend. I need to use collations features ?
KLEIN Stéphane wrote: On 7 avr, 16:31, Michael Bayer mike...@zzzcomputing.com wrote: you're free to access the raw connection and use the pysqlite create_collation method. If you'd like to assemble this on all connections, look into the PoolListener API described inhttp://www.sqlalchemy.org/docs/05/reference/sqlalchemy/interfaces.htm... and implement the connect method. Ok, I've appended my collation function with dbapi_con.create_collation (my_collate, my_collate) but I don't know how can I use my collation in query statement. we have an expression operator collate: from sqlalchemy import collate query.order_by(collate(mycolumn, 'somecollation')) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How can I perform one query with a sort on unicode field with sqlalchemy and sqlite backend. I need to use collations features ?
On 7 avr, 22:16, Michael Bayer mike...@zzzcomputing.com wrote: KLEIN Stéphane wrote: On 7 avr, 16:31, Michael Bayer mike...@zzzcomputing.com wrote: you're free to access the raw connection and use the pysqlite create_collation method. If you'd like to assemble this on all connections, look into the PoolListener API described inhttp://www.sqlalchemy.org/docs/05/reference/sqlalchemy/interfaces.htm... and implement the connect method. Ok, I've appended my collation function with dbapi_con.create_collation (my_collate, my_collate) but I don't know how can I use my collation in query statement. we have an expression operator collate: from sqlalchemy import collate query.order_by(collate(mycolumn, 'somecollation')) Thanks, it's working ! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How can I perform one query with a sort on unicode field with sqlalchemy and sqlite backend. I need to use collations features ?
On 7 avr, 22:16, Michael Bayer mike...@zzzcomputing.com wrote: KLEIN Stéphane wrote: On 7 avr, 16:31, Michael Bayer mike...@zzzcomputing.com wrote: you're free to access the raw connection and use the pysqlite create_collation method. If you'd like to assemble this on all connections, look into the PoolListener API described inhttp://www.sqlalchemy.org/docs/05/reference/sqlalchemy/interfaces.htm... and implement the connect method. Ok, I've appended my collation function with dbapi_con.create_collation (my_collate, my_collate) but I don't know how can I use my collation in query statement. we have an expression operator collate: from sqlalchemy import collate query.order_by(collate(mycolumn, 'somecollation')) Is there a solution to define this collate at the level of mapper or table definition ? I would like define this parameter globally to my application. Thanks for your help, Stephane --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Calling Oracle Stored Procedures Having Out Cursor Parameter
Hey all,
I have to call some pre-existing Oracle stored procedures that have
cursor out parameters. I've had no luck doing this short of using
raw cursors. Is there a better way to do this?
Here is a simple stored procedure modelled after what I have to call:
create or replace package TEST_PKG as
type refcur is ref cursor;
procedure simple_read(p_param out refcur);
end TEST_PKG;
/
create or replace package body TEST_PKG as
procedure simple_read(p_param out refcur) as
begin
open p_param for
select name from some_table;
end simple_read;
end TEST_PKG;
/
Here is the only code I was able to find that worked:
import sqlalchemy
engine = sqlalchemy.create_engine('oracle://user:pwd@db')
oracle_conn = engine.raw_connection()
in_cursor = oracle_conn.connection.cursor()
out_cursor = oracle_conn.connection.cursor()
in_cursor.execute('begin TEST_PKG.simple_read(:x); end;', x =
out_cursor)
results = out_cursor.fetchall()
print repr(results)
Any suggestions in how I can better do this with sqlalchemy?
Thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] When to create new sessions?
Hey Everyone, I'm new to SQLAlchemy (used to using Django's ORM but need to switch) and there's just one thing I'm struggling with, which is when am I supposed to create Sessions? I am of course creating scoped sessions. I feel like a real dunce for not being able to get my head around it. Do I create one per-request and pass it around? That just doesn't feel quite right to me. Or can I create them at module-level when I need them? Also, is it okay to call commit() more than once on the same session? On a per-function basis even (seems like an awful lot of boilerplate code in each function though… surely not?!) Sorry if I'm rambling, I just don't get when I'm supposed to instantiate a new session =) I know this is probably something really simple that most people get instantly. Kind 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How can I perform one query with a sort on unicode field with sqlalchemy and sqlite backend. I need to use collations features ?
On Apr 7, 2009, at 5:09 PM, KLEIN Stéphane wrote: Is there a solution to define this collate at the level of mapper or table definition ? I would like define this parameter globally to my application. there's an old trac ticket to somehow implement full collation ability but I think because its highly variable across implementations and usually not available it hasn't materialized into any such feature. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Calling Oracle Stored Procedures Having Out Cursor Parameter
On Apr 7, 2009, at 4:11 PM, cbaron wrote:
Hey all,
I have to call some pre-existing Oracle stored procedures that have
cursor out parameters. I've had no luck doing this short of using
raw cursors. Is there a better way to do this?
we have an outparam construct which makes use of cx_oracle's built in
API for this:
result = testing.db.execute(text(begin
foo(:x_in, :x_out, :y_out, :z_out); end;,
bindparams=[bindparam('x_in', Numeric), outparam('x_out', Numeric),
outparam('y_out',
Numeric), outparam('z_out', String)]), x_in=5)
assert result.out_parameters == {'x_out':10, 'y_out':75, 'z_out':None}
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: When to create new sessions?
On Apr 7, 2009, at 6:07 PM, rintin...@googlemail.com wrote: Hey Everyone, I'm new to SQLAlchemy (used to using Django's ORM but need to switch) and there's just one thing I'm struggling with, which is when am I supposed to create Sessions? I am of course creating scoped sessions. I feel like a real dunce for not being able to get my head around it. Do I create one per-request and pass it around? That just doesn't feel quite right to me. Or can I create them at module-level when I need them? per-request is the most natural approach. The point of the scopedsession is that you can use it as a global object, there's no need to pass it around. It automatically routes operations to a thread-local session. I'm sure django does something similar. the chapter on sessions includes a discussion on integrating scopedsession within a web application, you should check it out. Also, is it okay to call commit() more than once on the same session? absolutely. On a per-function basis even (seems like an awful lot of boilerplate code in each function though… surely not?!) depending on what you're doing , this may or may not be appropriate. boilerplate can be cut down using a decorator, such as: @commits def do_some_stuff(...): the decorator: def commits(fn): def go(*args, **kw): try: return fn(*args, **kw) Session.commit() except: Session.rollback() raise return go Session is a scopedsession which can be declared at the module level. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
