[sqlalchemy] Re: SA and IBM DB2

[Christian Klinger]

Hi Michael,

thanks for input. If i find some time i will start...

Christian


On Jul 6, 2011, at 11:19 AM, Christian Klinger wrote:


Hi Michael,

i am intrested in writing a dialect for DB2. Is there any howto which covers 
what is needed to start. Do you think we should write an extension, or should 
this dialect in sqlalchemy itself?


first off, HOORAY, secondly, this would be a dialect within SQLAlchemy itself 
under sqlalchemy.dialects.

Here are the two files we would need:

sqlalchemy/dialects/db2/base.py
sqlalchemy/dialects/db2/ibm_db.py

So in base.py, the base dialect classes, things that deal with the kind of SQL that DB2 
deals with.Preferably no details that are specific to the DBAPI.   In ibm_db.py is where things 
that are specific to IBMs DBAPI are present.At some later point, if for example pyodbc could 
also connect to DB2, we'd add a pyodbc.py file there.

Then to do what's in base.py, ibm_db.py, you need to emulate what's in all the 
other dialects.  Some smaller ones to look at are firebird, sybase.  More 
involved are mssql, postgresql, oracle.   The MySQL dialect is good too but 
that one is particularly complicated due to a lot of difficulties MySQL 
presents.

When I write a new dialect from scratch, the first thing I do is just to get it 
to run at all, which usually means a script like this:

e = create_engine('db2:ibm_db://scott:tiger@localhost/test')
c = e.connect()
print c.execute('SELECT 1').fetchall()

That's pretty much hello world.   You might try to work with a few variants of 
hello world just to get things going.

Then, you can start moving onto the actual tests.  This is also an incremental 
process, and I usually start with test/sql/test_query.py which tests basic 
round trips.The last section of README.unittests has several paragraphs on 
how to test new dialects and includes an overview of which tests to start with.







Thanks in advance
Christian



On Jun 29, 2011, at 6:43 AM, Luca Lesinigo wrote:


Hello there. I'd like to use SQLalchemy with an existing db2 database
(I can already access it with plain SQL using pyODBC from a python-2.6/
win32 system).

Googling around, I found http://code.google.com/p/ibm-db and it seems
to have an updated DB-API driver for python-2.6/win32, but the latest
SA adapter is for sqlalchemy-0.4.

Is there any way to access DB2 from sqlalchemy-0.6 or -0.7?
If that helps, I'm gonna use it in read-only (ie, no INSERT, UPDATE,
DELETE queries will be issued nor would they be accepted by the db)


A project I'd like to take on at some point, or to get someone else to do it, 
would be to write a modernized SQLAlchemy 0.7 dialect for DB2, where we would 
use DB2's DBAPI, but not their SQLAlchemy dialect which is out of date and they 
appear to not be doing much with.   I'd write a new dialect rather than 
porting/looking at the one IBM wrote just so there's no potential licensing 
issues.  The new DB2 dialect would live with all the other dialects under the 
SQLAlchemy project itself.

I understand DB2 has a free express edition so it would be a matter of 
getting that going and working out the dialect. Dialects aren't too hard to write so 
we do get them contributed, but for the moment we don't have a DB2 story for modern 
SQLAlchemy versions.






thanks, Luca

--
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.






--
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.






--
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: pymssql and decimal support

[Emmanuel Cazenave]

All right, I'm going to try pyodbc + freetds.

Thank you very much for your response.

On 6 juil, 19:39, Michael Bayer mike...@zzzcomputing.com wrote:
 On Jul 6, 2011, at 12:57 PM, emmanuelCAZENAVEwrote:

  Hello,

  I'm facing problems with mssql+pymssql: it seems that pymssql has poor 
  decimal support.
  As a result there are some rounding differences between the values stored 
  in the database, and the values I get when querying through mssql+pymssql. 
  And I absolutely need the exacts values stored in the DB.

  I'm thinking of a workaround: is it possible to declare every Decimal 
  column as String in  my alchemy's column definitions and manually cast the 
  results in decimal (or maybe at a lower level: in the column_property for 
  example ?). The goal would be to 'get rid' of conversion to float made by 
  pymssql on decimal columns

  PS: I can't change the database structure so I can't change the column data 
  type at the sqlserver level and I'm in a 'read only'  context, I don't need 
  to write in the database.

 Yeah this is known limitations of current pymssql and is mentioned in the 
 docs:http://www.sqlalchemy.org/docs/dialects/mssql.html#limitations.    My 
 understanding is that yet another pymssql version is in the works so you 
 might want to contact them.   It's likely that it is coercing from floating 
 point which introduces lossful conversion.   SQLAlchemy can only work with 
 what the DBAPI returns which is not a string here.

 FWIW pyodbc with MSSQL + FreeTDS produces accurate decimals in both 
 directions.    It's fully unix/OSX compatible.

-- 
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] type safety using sqlite

[Ben Sizer]

I have a Column(Integer) called object_id. I assign to it a string or
unicode value, eg. object_id = unot an integer. To my surprise, this
doesn't raise any kind of exception when the row is committed. I can
then expunge the session and request that row back, getting a Unicode
object for that column.

I understand that sqlite is very weakly typed and that you can do this
sort of thing easily. But I thought that SQLAlchemy would apply some
logic in the middle to ensure that an Integer column only takes
something integral. I would understand if I'd passed 30 or some
other string that could be coerced to an integer, but this doesn't fit
that constraint.

So, 2 questions:

a) Is this expected behaviour?
b) How can I catch this, ideally at the SQLAlchemy level, so that I
can't accidentally store a string as an integer?

--
Ben Sizer

-- 
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.

Re: [sqlalchemy] type safety using sqlite

[Michael Bayer]

On Jul 7, 2011, at 12:43 PM, Ben Sizer wrote:

 I have a Column(Integer) called object_id. I assign to it a string or
 unicode value, eg. object_id = unot an integer. To my surprise, this
 doesn't raise any kind of exception when the row is committed. I can
 then expunge the session and request that row back, getting a Unicode
 object for that column.
 
 I understand that sqlite is very weakly typed and that you can do this
 sort of thing easily. But I thought that SQLAlchemy would apply some
 logic in the middle to ensure that an Integer column only takes
 something integral. I would understand if I'd passed 30 or some
 other string that could be coerced to an integer, but this doesn't fit
 that constraint.
 
 So, 2 questions:
 
 a) Is this expected behaviour?
 b) How can I catch this, ideally at the SQLAlchemy level, so that I
 can't accidentally store a string as an integer?


The types do as little as possible, with the exception of the assert_unicode 
feature of String, as well as SQLite's date type since we have to coerce to a 
string (and in the latter case we used to get a lot of requests to let strings 
pass through).

This is a performance-critical point and SQLAlchemy seeks to do as little as 
possible with type coercion, we defer to the DBAPI to determine in most cases 
if a value is not acceptable.

For validation of incoming values, the options are TypeDecorator at the Core 
level and @validates at the ORM level:

http://www.sqlalchemy.org/docs/core/types.html#augmenting-existing-types
http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators

A recipe to apply validators to all occurrences of a type:

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ValidateAllOccurrencesOfType


-- 
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: type safety using sqlite

[Ben Sizer]

Thanks very much Michael, that should be more than enough information
for me to find a solution.

--
Ben Sizer

-- 
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] Explicit main table

[Bryan]

I'm having trouble telling an orm query which table is the main
table when I
only use a single column from the main table and it is wrapped up in
an SQL
function.  It's almost like SqlAlchemy can't see that I am using a
column from
that table because it is inside of a function::

  # -- Schema ---
  #
  # Labor
  # =
  # id
  # hours
  # createdBy (user ref)
  # editedBy (user ref)
  #
  #
  # User
  # 
  # id
  # username
  # -


  # -- Code ---
  CREATED_BY = aliased(User, name='createdBy')
  EDITED_BY = aliased(User, name='editedBy')

  q = query(CREATED_BY.username, func.sum(Labor.hours))
  q = q.join((CREATED_BY, Labor.createdBy==CREATED_BY.id))
  q.all()


This is producing a query like this::

  SELECT
user_1.username, sum(labor.st)
  FROM
user AS user_1
INNER JOIN user AS user_1 ON labor.createdBy = user_1.id

Which gives me a OperationalError 1066, Not unique table/alias:
'user_1'.

I would expect this::

  SELECT
createdBy.username, sum(labor.st)
  FROM
labor
INNER JOIN user AS createdBy ON labor.createdBy = createdBy.id

As soon as I add a column from the Labor table to the query, and it is
not in a
function, the query works.  For example, this works::

  q = query(CREATED_BY.username, Labor.id, func.sum(Labor.hours))

Mysql 5
SqlAlchemy 0.5.2

-- 
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: type safety using sqlite

[Ben Sizer]

Ok, this seems to do the trick for my use case, but I'd be curious to
see if there's a better way or if there are things that should be
fixed here.


from types import IntType, LongType

from sqlalchemy.exc import ArgumentError
from sqlalchemy import event
from sqlalchemy.orm import mapper

def _check_integral_type(target, value, oldvalue, initiator):
 if not isinstance(value, IntType) and not isinstance(value,
LongType):
 raise ArgumentError(value is not numeric)
 return value


@event.listens_for(mapper, mapper_configured)
def _setup_int_listeners(mapper, class_):
for prop in mapper.iterate_properties:
if hasattr(prop, 'columns'):
if isinstance(prop.columns[0].type, Integer):
event.listen(getattr(class_, prop.key), set,
_check_integral_type, retval=True)

-- 
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] Numeric value error on blank field with sqlite

[Fabrizio Pollastri]

When I read a record where a field declared as numeric is a blank
string, sqlalchemy gives the error ValueError: could not convert
string to float:. Instead, I wish to get something like NaN
or None. I am using sqlite.

TIA,
Fabrizio

-- 
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.

Re: [sqlalchemy] Explicit main table

[Michael Bayer]

On Jul 7, 2011, at 2:16 PM, Bryan wrote:

 I'm having trouble telling an orm query which table is the main
 table when I
 only use a single column from the main table and it is wrapped up in
 an SQL
 function.  It's almost like SqlAlchemy can't see that I am using a
 column from
 that table because it is inside of a function::
 
  # -- Schema ---
  #
  # Labor
  # =
  # id
  # hours
  # createdBy (user ref)
  # editedBy (user ref)
  #
  #
  # User
  # 
  # id
  # username
  # -
 
 
  # -- Code ---
  CREATED_BY = aliased(User, name='createdBy')
  EDITED_BY = aliased(User, name='editedBy')
 
  q = query(CREATED_BY.username, func.sum(Labor.hours))
  q = q.join((CREATED_BY, Labor.createdBy==CREATED_BY.id))
  q.all()
 
 
 This is producing a query like this::
 
  SELECT
user_1.username, sum(labor.st)
  FROM
user AS user_1
INNER JOIN user AS user_1 ON labor.createdBy = user_1.id
 
 Which gives me a OperationalError 1066, Not unique table/alias:
 'user_1'.
 
 I would expect this::
 
  SELECT
createdBy.username, sum(labor.st)
  FROM
labor
INNER JOIN user AS createdBy ON labor.createdBy = createdBy.id
 
 As soon as I add a column from the Labor table to the query, and it is
 not in a
 function, the query works.  For example, this works::
 
  q = query(CREATED_BY.username, Labor.id, func.sum(Labor.hours))
 
 Mysql 5
 SqlAlchemy 0.5.2

if you could upgrade to 0.6 or 0.7, you would say 
query(created_by).select_from(Labor).join(created_by, onclause)

else if stuck with 0.5 you need to use

from sqlalchemy.orm import join

query(created_by).select_from(join(Labor, created_by, 
onclause).join(whatever else needs to be joined))

i.e. the whole JOIN needs to be in select_from

-- 
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] hybrid, relationships and inheritance.

[James Studdart]

Hi all,
 I've got a question regarding hybrid properties and how to use them 
with single table inheritance.


I've got a class hierarchy like this (in semi-pseudo code):

class MyBase(object):
# This has the tablename declared attr, id as primary key, generic 
table args etc.


class Person(MyBase, Base):
children = relationship('Children')

class SpecialPerson(Person):
partner = relationship('Person')

Okay, so what I want is for SpecialPerson to return both it's own plus 
it's partners children. But, if I add to list of children of a special 
person, it only adds to it's local children list. Does that make sense?


This is what I've got now, I'm stabbing around in the dark a little bit, 
so I'm hoping for some guidance in the correct  way to do this with SQL 
Alchemy.


class Person(Mybase, Base):
_children = relationship('Children')

@hybrid_property
def children(self):
return self._children

class SpecialPerson(Person):
partner = relationship('Person')

@hybrid_property
def children(self):
return self._children + self.parter._children

@children.setter
def children(self, value):
self._children = value

Thank you for your time.

Cheers,
 James.




--
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: Explicit main table

[Bryan]

Thanks, that worked.

On Jul 7, 11:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
 On Jul 7, 2011, at 2:16 PM, Bryan wrote:



  I'm having trouble telling an orm query which table is the main
  table when I
  only use a single column from the main table and it is wrapped up in
  an SQL
  function.  It's almost like SqlAlchemy can't see that I am using a
  column from
  that table because it is inside of a function::

   # -- Schema ---
   #
   # Labor
   # =
   # id
   # hours
   # createdBy (user ref)
   # editedBy (user ref)
   #
   #
   # User
   # 
   # id
   # username
   # -

   # -- Code ---
   CREATED_BY = aliased(User, name='createdBy')
   EDITED_BY = aliased(User, name='editedBy')

   q = query(CREATED_BY.username, func.sum(Labor.hours))
   q = q.join((CREATED_BY, Labor.createdBy==CREATED_BY.id))
   q.all()

  This is producing a query like this::

   SELECT
     user_1.username, sum(labor.st)
   FROM
     user AS user_1
     INNER JOIN user AS user_1 ON labor.createdBy = user_1.id

  Which gives me a OperationalError 1066, Not unique table/alias:
  'user_1'.

  I would expect this::

   SELECT
     createdBy.username, sum(labor.st)
   FROM
     labor
     INNER JOIN user AS createdBy ON labor.createdBy = createdBy.id

  As soon as I add a column from the Labor table to the query, and it is
  not in a
  function, the query works.  For example, this works::

   q = query(CREATED_BY.username, Labor.id, func.sum(Labor.hours))

  Mysql 5
  SqlAlchemy 0.5.2

 if you could upgrade to 0.6 or 0.7, you would say 
 query(created_by).select_from(Labor).join(created_by, onclause)

 else if stuck with 0.5 you need to use

 from sqlalchemy.orm import join

 query(created_by).select_from(join(Labor, created_by, 
 onclause).join(whatever else needs to be joined))

 i.e. the whole JOIN needs to be in select_from

-- 
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] Problem with eagerload and lazy='joined'

[Ben Chess]

I've hit a problem where eagerload() fails to load in a relation of a
relation when lazy='joined' is involved.  It's easiest just to show
the test.  It fails in 0.7.1, and an equivalent test also fails in
0.6.8.

http://pastebin.com/ruq6SM1z

Basically, A has relations to B, C, and D.  C's relationship to A is a
lazy='joined'.

First load A, eagerloading 'd_row'
Then reference A.c_row, causing it to load C.

Then, separately, load C, eagerloading 'a_row.b_row'.
At this point, I expunge_all() and demonstrate that b_row was not
attached to C.a_row.

This does not occur if C's relationship to A is lazy='select'.
Weirdly, this also does not occur if the initial load of A does not
eagerload 'd_row'.   I'm not sure why that should affect anything.

-- 
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.

Re: [sqlalchemy] Problem with eagerload and lazy='joined'

[Michael Bayer]

On Jul 7, 2011, at 7:04 PM, Ben Chess wrote:

 I've hit a problem where eagerload() fails to load in a relation of a
 relation when lazy='joined' is involved.  It's easiest just to show
 the test.  It fails in 0.7.1, and an equivalent test also fails in
 0.6.8.
 
 http://pastebin.com/ruq6SM1z
 
 Basically, A has relations to B, C, and D.  C's relationship to A is a
 lazy='joined'.
 
 First load A, eagerloading 'd_row'
 Then reference A.c_row, causing it to load C.
 
 Then, separately, load C, eagerloading 'a_row.b_row'.
 At this point, I expunge_all() and demonstrate that b_row was not
 attached to C.a_row.
 
 This does not occur if C's relationship to A is lazy='select'.
 Weirdly, this also does not occur if the initial load of A does not
 eagerload 'd_row'.   I'm not sure why that should affect anything.

This will make it pass:

assert 'a_row' in a_obj.c_rows[0].__dict__
session.expire_all()

c_obj = 
session.query(C).options(eagerload_all('a_row.b_row')).filter_by(id=1).one()
session.expunge_all()

assert c_obj.a_row.b_row

note after load #1, c_obj is already in the Session, and c_obj.a_row is already 
populated (looking in __dict__ is always the way to see if something is already 
loaded).This is because of the lazy=False on C.a_row.

Then what happens in the load, and it occurs on line 2587 of mapper.py in the 
current tip, we get the C object already in the identity map during the second 
load.   We say, OK C do you have any attributes that aren't populated which we 
can pull from this row ?   C says, nope.   C.a_row is already there.   This 
process currently doesn't descend further into the objects attached to C.a_row 
so the rest of the columns are thrown away.

It was actually somewhat of an innovation around 0.5 or so when I actually got 
the thing to populate unloaded attributes on objects that were otherwise 
loaded and might even have pending changes, which was a big step forward at 
that time, I didn't take on trying to figure out if eagers could keep on going 
into the graph and find deeper attributes that aren't loaded.   

If you have an opinion on this, let me know, right now I feel like its in an OK 
place considering the tradeoff of digging way down into a graph which may be 
unnecessary for those rows that were already loaded, many-to-ones are usually 
not an issue since they pull from the identity map.  If the issue is you're 
going for detached behavior, I generally don't recommend relying heavily on 
object graphs that are fully traversable in the detached state unless you're 
doing some kind of offline caching.   Of course, if there were a patch to that 
area of code that successfully kept the traversal going deeper into already 
loaded nodes based on the current eagers present, I'm open to evaluating it, 
though it doesn't seem like a quick tweak at the moment.

Nice test though, if you're interested in helping with tests/patches we're 
always looking for help.





 
 -- 
 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.
 

-- 
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.