Discuss usage/design issues for SQLAlchemy, a Python SQL toolkit

Grant McKenzie | 23 May 2013 16:08

0.8.0 engine.base.Connection._safe_close_cursor

Hi,

this method references self.connection._logger.

Is self.connection._logger guaranteed to exist?

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

headers

Chris Withers | 23 May 2013 13:49

feel dirty, is there a better way?

Hi All,

I have a mixin defined like this:

def add_exclude_constraint(mapper, class_):
     table = class_.__table__
     elements = [('period', '&&')]
     for col in table.primary_key.columns:
         if col.name!='period':
             elements.append((col, '='))
     table.append_constraint(ExcludeConstraint(*elements))

class Temporal(object):

      <at> declared_attr
     def __table_args__(cls):
         listen(cls, 'instrument_class', add_exclude_constraint)
         return (
         CheckConstraint("period != 'empty'::tsrange"),
         )

     period = Column(DateTimeRange(), nullable=False, primary_key=True)

That listen call is the source of the dirty feeling...

What's the "right" way to do this?

Chris

-- 
Simplistix - Content Management, Batch Processing & Python Consulting
             - http://www.simplistix.co.uk

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

headers

Burak Arslan | 23 May 2013 13:20

implementing implicit scalar collections

Hi,

I've just implemented support for scalar collections for Spyne. (In 
Spyne terms that's sql serialization of an array of primitives). Seems 
to be working fine so far.

The question is: Is the association proxy the only (read/write) way of 
doing this? It requires the child table to be mapped, which requires the 
child table to have a primary key, which is sometimes completely 
useless. I also have to create another implicit attribute so that the 
association proxy can fetch the value off of it.

Here's the relevant bit:

https://github.com/plq/spyne/blob/master/spyne/util/sqlalchemy.py#L563

Here's its test:

https://github.com/plq/spyne/blob/master/spyne/test/test_sqlalchemy.py#L917

Setting both columns as primary keys breaks the test:

https://gist.github.com/plq/5630698#file-spyne-patch

column_property is read-only, so I can't use it.

Any suggestions?

Another question, instead of deleting, it seems to be updating foreign 
keys to null and re-inserting data. Why not just delete the old values?

Best regards,
Burak

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

headers

jo | 23 May 2013 09:42

distinct on

|Hi all,

I wondered if it is possible to execute a partial distinct in sqlalchemy.
The following query works in oracle and postgresql:

select distinct col1, 
first_value(col2) over (partition by col1 order by col2 asc)
from tmp;

How can I do such query in sqlalchemy?
Thanks for any help.
j

|

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

headers

Anthony Kong | 23 May 2013 07:29

how to use bind_expression to build an express like 'convert(bit, <at> value)?

Hi, all,

We are upgrading our application to use SA0.8.0. For reason outside our control, some XP workstations are using old sybase ODBC driver and we cannot upgrade them as of yet.

The sybase driver will cause this problem in the following situation:

1) we have a table that have a field of type 'bit' to hold boolean value (i.e. 0 for false and 1 for true)

2) we need to select subset data out of this table based on this flag

When we run something like

    session.query(SubProduct).filter(Product.isTradeable==True)

we get this error message:

DatabaseError: (DatabaseError) Msg 257, Level 16, State 1, Line 1
Implicit conversion from datatype 'CHAR' to 'BIT' is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 1, Line 1
Implicit conversion from datatype 'CHAR' to 'BIT' is not allowed. Use the CONVERT function to run this query.
'SELECT SUBPRODUCT ... WHERE Product.id_tradeable = <at> id_tradeable_1' {' <at> id_tradeable_1': '1'}

I am trying to put in place a workaround: If I can convert the sql statement to something like so

'SELECT SUBPRODUCT ... WHERE Product.id_tradeable = convert(bit, <at> id_tradeable_1)' {' <at> id_tradeable_1': '1'}

I believe I can avoid the sybase SQL exception regardless of client driver version

So I implemented this class

import sqlalchemy.types as satypes
from sqlalchemy import func
from sqlalchemy.sql.expression import type_coerce

class SybaseBit( satypes.TypeDecorator):
    impl = satypes.Boolean

    def bind_expression(self, bindvalue):
        # bindvalue = type_coerce(bindvalue, satypes.Integer)
        return func.convert(bit, bindvalue)

It failed because 'bit' is not defined there. If I put in a string 'bit', the generated sql is very close to what I want.

So my question is:

What is the proper way to implement this?

Cheers, Tony

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

headers

Shyam Purkayastha | 23 May 2013 07:16

sqlalchemy does not enforce foreign key constraint

I am trying to play with the sql alchemy ORM based db definition with an inmemory sqlite db. I have defined my tables as follows

class Customer(Base): __tablename__ = 'customer' id = Column(Integer, primary_key=True) name = Column(String(80)) auth = relationship("CustomerAuth", backref='customer') class CustomerAuth(Base): __tablename__ = 'authentication' id = Column(Integer, ForeignKey('customer.id')) username = Column(String(80), primary_key=True) passwd = Column(String(80))

Now I am creating the session

Session = sessionmaker(bind=sqla.engine) session = Session()

And then I try to create two row objects for Customer with id 1 and 2

cst1 = sqla.Customer(id=1,name='shyam') cst2 = sqla.Customer(id=2,name='ram')

And I create three row objects for CustomerAuth which reference to id 1, 2 and 3 of Customer

auth1 = sqla.CustomerAuth(id=1,username='shyamu',passwd='wam') auth2 = sqla.CustomerAuth(id=2,username='ramu',passwd='dam') auth3 = sqla.CustomerAuth(id=3,username='lamu',passwd='sam')

As you can see I have created a CustomerAuth row with id = 3 , which is a foreign key referring the Customer.id. But since Customer table has no entry with id=3 , this should fail

session.add(cst1) session.add(cst2) session.add(auth1) session.add(auth2) session.flush()

This operation should fail but it goes through successfully.

session.add(auth3) session.flush()

I want to know what am I not doing which bypasses the foreign key enforcement

Thanks in advance

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

headers

Julien Cigar | 22 May 2013 13:14

"virtual-like" entities, concrete table inheritance

Hello,

I'm currently implementing a RBAC-like model for a webapp with the 
"usual suspects": users, roles, permissions, etc where a "Role" has one 
or more "Permissions", and an "User" can be in 1 or more "Role".

I would like to some virtual-like "Role" that are automatically 
attribued in some situations. For example "AnonymousUser" is the user is 
not logged, "AuthenticatedUser" is the user is authenticated, "Owner" if 
the user "owns" an object, etc. Those roles should always exist and 
should not be part of the unit-of-work process, in fact I don't want to 
store them in the database.
I wondered if there is an elegant way to do that kind of stuff 
transparently with SQLAlchemy?

Another question: I have different kind of "Permission" (some of them 
are linked to a module, some to a content_type, some are "core" 
permissions, etc) and I planned to use one separate table per 
"permission type" with concrete table inheritance. I only used joined 
load inheritance (which work very well) and from what I understood from 
the documentation concrete table inheritance are somewhat discouraged?

Thank you 
Julien

-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

headers

avdd | 22 May 2013 12:37

bug in dogpile advanced example?

"two through twelve" actually shows 25 .. 40

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

headers

Anthony Kong | 22 May 2013 05:04

SA 0.8.0: Invalid argument(s) 'label_length'

Hi, all,

I have recently upgraded to use SA 0.8.0.

Now when I run this statement,

db = create_engine("sybase://", creator=self.buildConnection, echo=echo, label_length=30)

it throws a TypeError exception:

TypeError: Invalid argument(s) 'label_length' sent to create_engine(), using configuration SybaseDialect_pyodbc/QueuePool/Engine. Please check that the keyword arguments are appropriate for this combination of components.
Locals:

What is the root cause of it?

If I remove 'label_length=30', it will work fine. How can I set this value in 0.8.0? Do I still need to?

Cheers, Tony

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

headers

Etienne Rouxel | 21 May 2013 17:31

Are consecutive query.get calls supposed to send a single SELECT query?

Hello

In my program, I was trying to guess why so many SQL queries were sent while some could have been avoided with the help of the identity map.

So, I reduced my program to what is below and wrote 3 times the same query.get call and 3 SQL queries were sent to the database server.

Why does SQLAlchemy send 3 times the SQL while it could have done it only 1 time for the first call and use the identity map for the 2 last calls?

from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

_descriptiontype_table = Table('descriptiontype', Base.metadata,

Column('id', Integer, primary_key=True),

Column('refno', Integer),

Column('sortindex', Integer),

Column('designation', String),

schema='botany'

)

class Descriptiontype(Base):

__table__ = _descriptiontype_table

if __name__ == '__main__':

engine = create_engine('postgresql://user <at> localhost:5432/mydatabase')

Session = sessionmaker(bind=engine)

session = Session()

session.query(Descriptiontype).get(-2147483648)

session.query(Descriptiontype).get(-2147483648)

session.query(Descriptiontype).get(-2147483648)

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

headers

Charlie Clark | 19 May 2013 14:53

Working with stored procedures

Hi,

I have created a stored procedure on a MySQL database to create a "pivot table". To reduce network traffic the procedure generates some SQL and then executes a statement. It has no return value. I have had trouble calling the procedure from sqlachemy using the mysq-connector driver. From the searches I've done it seems that there is no standard or easy way of doing this because of the different, if any, ways .callproc() is implemented. I'm therefore looking for the best or most convenient way of integrating the procedure in SQLAlchemy code.

The procedure is:

CREATE PROCEDURE `http`.`cdn_pivot` ()

BEGIN SET <at> <at> group_concat_max_len = 32000;

SET <at> sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT( ' sum(IF(cdn = ''', cdn, ''', sites,NULL)) "' ,cdn, '"' ) )

INTO <at> sql

FROM cdn_trend;

SET <at> stmt = CONCAT('SELECT labelDate, ', <at> sql, ' from cdn_trend group by labelDate'); prepare stmt from <at> stmt; execute stmt; deallocate prepare stmt;

SET <at> <at> group_concat_max_len = 1024;

END

And this *has* to be called and read like this:

c = con.cursor()

c.callproc("cdn_pivot")

for r in c.stored_results():

print r.fetchall()

So .execute() cannot be used. I can think of two solutions: do the first part of the procedure in Python and simply execute the generated statement. This adds a network call but also increases the testability of the code. Secondly, access the cursor directly with something likte:

c = session.connection.cursor()

c.callproc()

for r in c.stored_results():

pass

print r.fetchall()

Is this a reasonable summary of the situation?

Charlie

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

208	May 2013
309	April 2013
206	March 2013
208	February 2013
323	January 2013
220	December 2012
248	November 2012
189	October 2012
228	September 2012
248	August 2012
173	July 2012
179	June 2012
252	May 2012
267	April 2012
302	March 2012
319	February 2012
225	January 2012
351	December 2011
231	November 2011
224	October 2011
272	September 2011
319	August 2011
302	July 2011
343	June 2011
219	May 2011
300	April 2011
288	March 2011
406	February 2011
383	January 2011
320	December 2010
326	November 2010
366	October 2010
429	September 2010
407	August 2010
387	July 2010
394	June 2010
426	May 2010
444	April 2010
449	March 2010
496	February 2010
345	January 2010
311	December 2009
306	November 2009
464	October 2009
420	September 2009
472	August 2009
329	July 2009
338	June 2009
444	May 2009
445	April 2009
459	March 2009
442	February 2009
546	January 2009
471	December 2008
459	November 2008
439	October 2008
599	September 2008
542	August 2008
533	July 2008
562	June 2008
604	May 2008
494	April 2008
394	March 2008
439	February 2008
520	January 2008
488	December 2007
509	November 2007
417	October 2007
408	September 2007
466	August 2007
590	July 2007
576	June 2007
402	May 2007
621	April 2007
649	March 2007
585	February 2007
544	January 2007
462	December 2006
372	November 2006
387	October 2006
341	September 2006
224	August 2006
455	July 2006
442	June 2006
422	May 2006
396	April 2006
487	March 2006
351	February 2006
246	January 2006
95	December 2005
48	November 2005

Mon	Tue	Wed	Thu	Fri	Sat	Sun

		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31