gmane.comp.python.sqlalchemy.user

Creating an index if it doesn't exist

Mike Bissell — 2013-05-24T00:06:19

How might I convince SQLAlchemy 0.7.9 to create a newly added index on a table in the event that the index doesn't already exist? This new index is created as a member of __table_args__; it is not instantiated with index=True. I have many such index objects to create.

Calling create_all() doesn't appear to issue CREATEs for a table if it already exists. I think that's because the optional checkfirst parameter defaults to True.

Setting checkfirst=False breaks because trying to create an already existing DB object is an error, and most of my DB already exists:

    ModelBase.metadata.create_all(checkfirst=False)
    ProgrammingError: (ProgrammingError) relation "organism" already exists
    '\nCREATE TABLE organism (\n\tid SERIAL NOT NULL, \n\tname VARCHAR NOT NULL, \n\tPRIMARY KEY (id)\n)\n\n' {}


I did check stackoverflow, and their unsatisfactory advice was simply to cut and paste the generated DDL:

    http://stackoverflow.com/questions/14419299/adding-indexes-to-sqlalchemy-models-after-table-creation

Can SQLAlchemy execute multiple select statements in a single round trip?

Sean Lynch — 2013-05-23T18:10:38

Does SQLAlchemy have any builtin support to execute multiple SELECT 
statements in a single round trip to the database, similar to NHibernate's 
.future() call (http://ayende.com/blog/3979/nhibernate-futures) or 
ActiveRecord::Futures (https://github.com/leoasis/activerecord-futures).

I came across the SQLAlchemy-Future project 
(http://lunant.github.io/SQLAlchemy-Future/), but it appears to just spawn 
a new thread for each query so it doesn't block the normal flow, instead of 
batching multiple queries together into a single trip.

I've seen examples in SQLAlchemy on how to do this for INSERTs, but not for 
SELECT queries.
http://docs.sqlalchemy.org/ru/latest/core/tutorial.html#executing-multiple-statements

If not, it would be nice to call .future() or .promise() on instead of a 
` sqlalchemy.orm.query.Query` instance instead of .all() to batch multiple 
queries and have them executed in a single round trip.  The way NHibernate 
works is it will execute all the queries called with .future() when an 
atte

0.8.0 engine.base.Connection._safe_close_cursor

Grant McKenzie — 2013-05-23T14:08:38

Hi,

this method references self.connection._logger.

Is self.connection._logger guaranteed to exist?

feel dirty, is there a better way?

Chris Withers — 2013-05-23T11:49:10

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

implementing implicit scalar collections

Burak Arslan — 2013-05-23T11:20:46

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?

B

distinct on

jo — 2013-05-23T07:42:32

|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


|

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

Anthony Kong — 2013-05-23T05:29:59

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:

sqlalchemy does not enforce foreign key constraint

Shyam Purkayastha — 2013-05-23T05:16:39


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,us

"virtual-like" entities, concrete table inheritance

Julien Cigar — 2013-05-22T11:14:35

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 und

bug in dogpile advanced example?

avdd — 2013-05-22T10:37:40

"two through twelve" actually shows 25 .. 40

SA 0.8.0: Invalid argument(s) 'label_length'

Anthony Kong — 2013-05-22T03:04:44

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

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

Etienne Rouxel — 2013-05-21T15:31:10

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 = sessionmake

Working with stored procedures

Charlie Clark — 2013-05-19T12:53:49

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 cal

finding table/column name of unique constraint failure

lars van gemerden — 2013-05-19T08:39:46

Hi all,

I generate webpages in which end-users can input data that is stored in a 
database via sqla.The datamodel is not pre-defined but created by (other) 
users/designers.   

My question is how can i best let the end-user know which field contains 
the error if the end-user violates a unique constraint; e.g. if the user 
creates an account by filling in a username/password, the username must be 
unique so the user must get an error ("username already exists" oss) if he 
tries to fill in an existing username.

If i commit the data, the exception thrown is somewhat cryptic (is the 
message created by sqla or the database?). 

Some related questions:

- are there arguments in the exception i could use?
- should i just query the table for the existence of the unique constrained 
value (i seem to remember that is impossible in the "validate" solution, so 
how else)?

I might be able to come up with some sort of solution, but i am looking for 
a good/the best (structurally sound, not too slow, not overly comp

eager load polymorphic relation

me — 2013-05-15T22:51:13

using python 2.7, sqla 0.7.9, postgresql 9.1

i'm trying to eager load a relationship to a polymorphic target (the target
uses joined table inheritance).

here is example:

    http://pastebin.com/xSiHS4QW

and this is output:

    SELECT
    ...
    FROM container
    LEFT OUTER JOIN root AS root_1 ON root_1.id = container.root_id

no type1, not type2?

looks like with_polymorphic mappig arg controls that:

    http://pastebin.com/FfmJSZWc

and this is output:

    SELECT ...
    FROM container
    LEFT OUTER JOIN (
            SELECT ...
            FROM root
            LEFT OUTER JOIN type1 ON root.id = type1.id
            LEFT OUTER JOIN type2 ON root.id = type2.id
        ) AS anon_1 ON anon_1.root_id = container.root_id

but i thought i'd get:

    SELECT ...
    FROM container
    LEFT OUTER JOIN root AS root_1 ON root_1.id = container.root_id
    LEFT OUTER JOIN type1 ON root.id = type1.id
    LEFT OUTER JOIN type2 ON root.id = type2.id

this is not problem for small sets but for bulk ops either la

Announcing new model code generation tool: sqlacodegen

Alex Grönholm — 2013-05-18T00:28:05

After a while of trying to fix sqlautocode, I ended up writing a new tool 
instead.

Copypasta from the README:
---------------------------------------------

This is a tool that reads the structure of an existing database and 
generates the appropriate SQLAlchemy model code, using the declarative 
style if possible.
Features 
   
   - Supports SQLAlchemy 0.6.x - 0.8.x
   - Produces declarative code that almost looks like it was hand written
   - Produces PEP 8 <http://www.python.org/dev/peps/pep-0008/> compliant 
   code
   - Accurately determines relationships, including many-to-many, one-to-one
   - Excellent test coverage

---------------------------------------------
It should work at least as well as sqlautocode. There is some room for 
future improvement, of course:

   - Autodetection of joined-table inheritance
   - Code generation for separate tables/classes/mappers instead of 
   declarative
   - Proper code generation for deferred/immediate foreign key constraints
   - Sorting out problems with C

Fun with Macros: PINQ to SQLAlchemy

Haoyi Li — 2013-05-17T13:37:55

Hey All, 

I asked a few questions here a while ago to get help on a project, and 
thought it might be interesting to share the result of that project.

PINQ to SQLAlchemy <https://github.com/lihaoyi/macropy#pinq-to-sqlalchemy> is 
a shameless clone of .NET's LINQ to SQL, using macros to perform the 
lifting of code into a SQL query. It lets you write code like this:

# Countries with a land area greater than 10 million square kilometersprint query%((x.name, x.surface_area) for x in db.country if x.surface_area > 10000000)# [(u'Antarctica', Decimal('13120000.0000000000')), (u'Russian Federation', Decimal('17075400.0000000000'))]

Where the whole thing gets translated into a SQLAlchemy expression language 
"select" query and shipped over to the database. It basically relies 
entirely on SQLAlchemy's expression language, using macros to perform a 
very shallow translation from generator-comprehensions into the expression 
language's "select" and "where" clauses, as well as nicely binding the 
tables (e.g. db.c

passing additional values to association_proxy's 'creator' method

Gerald Thibault — 2013-05-17T00:59:03

I have a User model, and a Group model, and a m2m relation between them.

I also have a proxy on the user that routes directly to the Group 
instances. The creator function, however, only takes one param (group), so 
when I pass in a group, I have no way to look it up to see if the (user_id, 
group_id) combo is in use already, because i only have the group. It seems 
like I am probably missing something very simple, but I can't figure out 
what it is. The code below can be run to show the issue I am having.

The last line, user.groups = [group] , throws an error because 'self' isn't 
defined. self clearly doesn't work here, what would the workaround be to 
get the User instance's "id" value into that creator function? The group I 
am passing in is the same as the group currently assigned, so rather than 
purging the collection and adding a UserGroup with only a group_id (the 
user_id gets populated later via the relationship, but at that point it is 
too late, because it's already decided the new object is n

SQLServer + Datetimes returning odd results?

John Anderson — 2013-05-16T19:37:56

I have a SQLServer DB with a table that has a column as datetime and its
default value is `getutcdate()` (on the server).

We are using stored procedures and are running DBSession.execute('sproc')
and to do a select on the table and the rows that are returned have weird
datetime values:

datetime.datetime(2013, 16, 136, 29, 231, 4, 3888128)

It seems be be completely missing its month.  In the db the value is:

2013-05-16 19:29:29.487

Any ideas what might cause this?

It is currently using SQLAlchemy 0.7.6

Relationship configuration that isn't functioning as I expect

Bobby Impollonia — 2013-05-16T19:02:41

I am trying to create a relationship to work with a legacy schema and am 
having trouble configuring it to behave as I want. As a disclaimer, I 
understand why the schema here is not the schema one would use if starting 
from scratch.

Anyway, I have simplied the situation down to the following example:
I have users and have two different types of objects that users can be 
related to. For this example, those are street addresses and email 
addresses. Rather than having a secondary table for each relationship, 
there is a single secondary table. It has a "foreign key" column that will 
either function as a foreign key to the street addresses table or the email 
addresses table. There is a discriminator column that indicates which of 
those tables is being referenced. The secondary table has its own primary 
key and has an associated mapped class. The problem I am trying to solve is 
setting up a relationship between the secondary table and the address 
tables.

This is what I have ended up with:

class Affil

how are foreign relations stored ?

Jonathan Vanasco — 2013-05-16T17:37:35

I've been using a utility method 'columns_as_dict' to help store my data in 
a cache.  It works well.

A problem I've encountered... i need to access the related data that i've 
eagerloaded.

My current function looks like this

    def columns_as_dict(self):
        as_dict = dict(
               (col.name, getattr(self, col.name)) 
               for col in sqlalchemy_orm.class_mapper(self.__class__).mapped_table.c
        )
        return *as_dict*
*
*
any idea how i can access the foreign relations ?  i'm specifically interested in ones that are already eagerloaded, but am also interested in accessing all of the relations in general.