Re: [sqlalchemy] SQLAlchemy 0.6beta1: PG dialect: DropTable: (ProgrammingError) syntax error at or near CONSTRAINTS

2010-03-11 Thread Manlio Perillo 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Gerry Reno ha scritto:
 Been doing great with SQLAlchemy 0.6beta1 for the past week after a
 couple snags migrating from 5.5.
 
 Today I hit a small problem with postgresql on DropTables:
 
 running this command:
 engine.execute(DropTable(table, cascade=True))
 
 produced this error:
 sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or
 near CONSTRAINTS
 LINE 2: DROP TABLE users CASCADE CONSTRAINTS
  ^
  '\nDROP TABLE users CASCADE CONSTRAINTS' {}
 
 The problem seems to be with the keyword 'CONSTRAINTS' for the
 postgresql dialect.  The drop succeeds without it.
 

I have reported the same problem a few days ago.

Support for cascading has been removed very recently, since it used
Oracle only syntax.

 [...]


Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkuYt9kACgkQscQJ24LbaUTTFACgiYx4yWm7mT3ulZvYdt9wWLgf
gQEAn3IwidE3nba7o4hc2jW7iGoTbiPU
=Yne8
-END PGP SIGNATURE-

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Oracle DATE column reflection

2010-03-11 Thread Grimsqueaker 
When I use autoload=True to reflect an Oracle table with a DATE
column containing a full date and time, the resulting python object is
a datetime.date, not datetime.datetime. Before updating to SA 0.6 I
used to get a datetime.datetime as expected.

If I override the reflected column with the DATETIME type from the
oracle dialect, I get what I expect.

Is there a way to configure the behaviour of the table reflection so
that I will get the type that I expect without having to explicitly
override column definitions?

Thanks

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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: Oracle DATE column reflection

2010-03-11 Thread Grimsqueaker 
I forgot to mention this old post which seems to mention this exact
problem, maybe it is related.
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg06619.html

On Mar 11, 4:04 pm, Grimsqueaker grimsqueake...@gmail.com wrote:
 When I use autoload=True to reflect an Oracle table with a DATE
 column containing a full date and time, the resulting python object is
 a datetime.date, not datetime.datetime. Before updating to SA 0.6 I
 used to get a datetime.datetime as expected.

 If I override the reflected column with the DATETIME type from the
 oracle dialect, I get what I expect.

 Is there a way to configure the behaviour of the table reflection so
 that I will get the type that I expect without having to explicitly
 override column definitions?

 Thanks

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] SQLAlchemy 0.6beta1: PG dialect: DropTable: (ProgrammingError) syntax error at or near CONSTRAINTS

2010-03-11 Thread Michael Bayer 
Manlio Perillo wrote:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1

 Gerry Reno ha scritto:
 Been doing great with SQLAlchemy 0.6beta1 for the past week after a
 couple snags migrating from 5.5.

 Today I hit a small problem with postgresql on DropTables:

 running this command:
 engine.execute(DropTable(table, cascade=True))

 produced this error:
 sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or
 near CONSTRAINTS
 LINE 2: DROP TABLE users CASCADE CONSTRAINTS
  ^
  '\nDROP TABLE users CASCADE CONSTRAINTS' {}

 The problem seems to be with the keyword 'CONSTRAINTS' for the
 postgresql dialect.  The drop succeeds without it.


 I have reported the same problem a few days ago.

 Support for cascading has been removed very recently, since it used
 Oracle only syntax.

to clarify, the cascade flag has been removed entirely until it can be
implemented completely.  to add to that, if you want the CASCADE syntax,
you can enhance DropTable with your own construct for now as per the
guidelines in http://www.sqlalchemy.org/docs/reference/ext/compiler.html .


 [...]


 Manlio
 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.4.10 (GNU/Linux)
 Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

 iEYEARECAAYFAkuYt9kACgkQscQJ24LbaUTTFACgiYx4yWm7mT3ulZvYdt9wWLgf
 gQEAn3IwidE3nba7o4hc2jW7iGoTbiPU
 =Yne8
 -END PGP SIGNATURE-

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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] Oracle DATE column reflection

2010-03-11 Thread Michael Bayer 
Grimsqueaker wrote:
 When I use autoload=True to reflect an Oracle table with a DATE
 column containing a full date and time, the resulting python object is
 a datetime.date, not datetime.datetime. Before updating to SA 0.6 I
 used to get a datetime.datetime as expected.

 If I override the reflected column with the DATETIME type from the
 oracle dialect, I get what I expect.

 Is there a way to configure the behaviour of the table reflection so
 that I will get the type that I expect without having to explicitly
 override column definitions?

As I'm sure you're aware, Oracle has no DATETIME type - it only has DATE,
which stores a date + time, as well as TIMESTAMP.   So when we reflect
this, we get back DATE.  The generic types.Date, when used with the
cx_oracle backend, as expected chops off the time portion coming back in
result sets, and until now so did the DATE type, which is a subclass.  In
0.5 we apparently had the DATE type reflect as DateTime to work around
this, which is not an option here since types in 0.6 reflect exactly as
stated, in this case DATE.  So the solution is to make DATE act
identically as it does on Oracle, in that it returns a datetime in all
cases (note this is not the behavior for types.Date, however), so that is
available on the latest default tip rb66411b37ba4.



 Thanks

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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: Unsigned attributes brake autoload

2010-03-11 Thread MattQc 
Here is the create statement of the table causing problem:

--

CREATE TABLE `AdminClass` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) unsigned DEFAULT NULL,
  `en_US` varchar(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--

Using sqlalchemy version 0.5.8 I don't get this error.

Thanks,
Mathieu

On Mar 10, 6:40 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 MattQc wrote:
  Hi there,
  Using sqlalchemy version 0.6beta1, I am trying to load a mysql table
  which has a field unsigned int.
  Here is the error I got:

 we would have to see your schema since we have working test coverage for
 reflection of INTEGER with unsigned.



    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/schema.py, line 205, in __new__
      table._init(name, metadata, *args, **kw)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/schema.py, line 258, in _init
      reflecttable(self, include_columns=include_columns)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/base.py, line 1512, in reflecttable
      self.dialect.reflecttable(conn, table, include_columns)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/default.py, line 160, in reflecttable
      return insp.reflecttable(table, include_columns)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/reflection.py, line 263, in reflecttable
      tbl_opts = self.get_table_options(table_name, schema,
  **table.kwargs)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/reflection.py, line 126, in
  get_table_options
      **kw)
    File string, line 1, in lambda
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/reflection.py, line 40, in cache
      ret = fn(self, con, *args, **kw)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/dialects/mysql/base.py, line 1769, in
  get_table_options
      parsed_state = self._parsed_state_or_create(connection,
  table_name, schema, **kw)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/dialects/mysql/base.py, line 1868, in
  _parsed_state_or_create
      info_cache=kw.get('info_cache', None)
    File string, line 1, in lambda
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/reflection.py, line 40, in cache
      ret = fn(self, con, *args, **kw)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/dialects/mysql/base.py, line 1899, in
  _setup_parser
      return parser.parse(sql, charset)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/dialects/mysql/base.py, line 2044, in parse
      self._parse_column(line, state)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/dialects/mysql/base.py, line 2209, in
  _parse_column
      type_instance = col_type(*type_args, **type_kw)
  TypeError: __init__() got an unexpected keyword argument 'unsigned'

  Thanks
  Mathieu

  --
  You received this message because you are subscribed to the Google Groups
  sqlalchemy group.
  To post to this group, send email to sqlalch...@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 sqlalch...@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] Re: Unsigned attributes brake autoload

2010-03-11 Thread Michael Bayer 
MattQc wrote:
 Here is the create statement of the table causing problem:

 --

 CREATE TABLE `AdminClass` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `active` tinyint(1) unsigned DEFAULT NULL,
   `en_US` varchar(256) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

 --

 Using sqlalchemy version 0.5.8 I don't get this error.

its fine in the latest tip.   please be aware that beta releases change
quickly and its best to always test the latest.


 Thanks,
 Mathieu

 On Mar 10, 6:40 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 MattQc wrote:
  Hi there,
  Using sqlalchemy version 0.6beta1, I am trying to load a mysql table
  which has a field unsigned int.
  Here is the error I got:

 we would have to see your schema since we have working test coverage for
 reflection of INTEGER with unsigned.



    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/schema.py, line 205, in __new__
      table._init(name, metadata, *args, **kw)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/schema.py, line 258, in _init
      reflecttable(self, include_columns=include_columns)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/base.py, line 1512, in reflecttable
      self.dialect.reflecttable(conn, table, include_columns)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/default.py, line 160, in reflecttable
      return insp.reflecttable(table, include_columns)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/reflection.py, line 263, in reflecttable
      tbl_opts = self.get_table_options(table_name, schema,
  **table.kwargs)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/reflection.py, line 126, in
  get_table_options
      **kw)
    File string, line 1, in lambda
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/reflection.py, line 40, in cache
      ret = fn(self, con, *args, **kw)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/dialects/mysql/base.py, line 1769, in
  get_table_options
      parsed_state = self._parsed_state_or_create(connection,
  table_name, schema, **kw)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/dialects/mysql/base.py, line 1868, in
  _parsed_state_or_create
      info_cache=kw.get('info_cache', None)
    File string, line 1, in lambda
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/engine/reflection.py, line 40, in cache
      ret = fn(self, con, *args, **kw)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/dialects/mysql/base.py, line 1899, in
  _setup_parser
      return parser.parse(sql, charset)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/dialects/mysql/base.py, line 2044, in parse
      self._parse_column(line, state)
    File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6beta1-
  py2.6.egg/sqlalchemy/dialects/mysql/base.py, line 2209, in
  _parse_column
      type_instance = col_type(*type_args, **type_kw)
  TypeError: __init__() got an unexpected keyword argument 'unsigned'

  Thanks
  Mathieu

  --
  You received this message because you are subscribed to the Google
 Groups
  sqlalchemy group.
  To post to this group, send email to sqlalch...@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 sqlalch...@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 sqlalch...@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] Single table inheritance subclass relations

2010-03-11 Thread Conor 
I'm having trouble with many-to-one relationships to subclasses that use
single table inheritance. I have tried this in 0.5.8 and 0.6beta1.

Here is my test case:

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

employee = sa.Table(employee, Base.metadata,
sa.Column(id, sa.Integer, primary_key=True),
sa.Column(type, sa.Integer, nullable=False))

class Employee(Base):
__table__ = employee
__mapper_args__ = {polymorphic_identity: 0,
   polymorphic_on: employee.c.type}

class Engineer(Employee):
__mapper_args__ = {polymorphic_identity: 1}

class Other(Base):
__tablename__ = other

# Fields
id = sa.Column(sa.Integer, primary_key=True)
engineer_id = sa.Column(sa.Integer,
sa.ForeignKey(employee.id))

# Relations
engineer = orm.relation(Engineer)

print Other.engineer.has()

I get this exception:

AttributeError: 'ClauseList' object has no attribute 'proxy_set'

This appears to be the same issue described at
http://groups.google.com/group/sqlalchemy/browse_thread/thread/7fcb0c0c6e1c809f.
Is this a bug? Currently, I am working around the issue by changing
Other.engineer to refer to Employee and using
Other.engineer.of_type(Engineer).has(), but this is less than ideal.

NOTE: In my real use case, I have compound foreign keys and check
constraints on Other to ensure that its engineer_id only points to
Engineer objects. However that was not necessary to demonstrate the
problem, so it has been omitted here.

Thanks,
-Conor

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Single table inheritance subclass relations

2010-03-11 Thread Michael Bayer 
Conor wrote:
 I'm having trouble with many-to-one relationships to subclasses that use
 single table inheritance. I have tried this in 0.5.8 and 0.6beta1.


that's what I get for not trying to answer every single email, a 6 month
old bug which I've missed.  This is very small and I've created and closed
ticket #1731 for this in r5402dd9d21e8 .




 Here is my test case:

 import sqlalchemy as sa
 from sqlalchemy import orm
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()

 employee = sa.Table(employee, Base.metadata,
 sa.Column(id, sa.Integer, primary_key=True),
 sa.Column(type, sa.Integer, nullable=False))

 class Employee(Base):
 __table__ = employee
 __mapper_args__ = {polymorphic_identity: 0,
polymorphic_on: employee.c.type}

 class Engineer(Employee):
 __mapper_args__ = {polymorphic_identity: 1}

 class Other(Base):
 __tablename__ = other

 # Fields
 id = sa.Column(sa.Integer, primary_key=True)
 engineer_id = sa.Column(sa.Integer,
 sa.ForeignKey(employee.id))

 # Relations
 engineer = orm.relation(Engineer)

 print Other.engineer.has()

 I get this exception:

 AttributeError: 'ClauseList' object has no attribute 'proxy_set'

 This appears to be the same issue described at
 http://groups.google.com/group/sqlalchemy/browse_thread/thread/7fcb0c0c6e1c809f.
 Is this a bug? Currently, I am working around the issue by changing
 Other.engineer to refer to Employee and using
 Other.engineer.of_type(Engineer).has(), but this is less than ideal.

 NOTE: In my real use case, I have compound foreign keys and check
 constraints on Other to ensure that its engineer_id only points to
 Engineer objects. However that was not necessary to demonstrate the
 problem, so it has been omitted here.

 Thanks,
 -Conor

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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] distinct on two fields with a count

2010-03-11 Thread Moshe C. 
How do I write an expression (in sqlalchemy 0.4.6) that will generate
the following expression:

select count(distinct  field1, field2)   from tableA;

I know how to do it for one field and I know how to do it with no
count using distinct=True, but not this combination.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] connection pooling question, is it possible

2010-03-11 Thread Krishnakant Mane 

hello all,
I am working on a free software for accounting and rural banking in India.
We use Pylons for web application.
Now, my question is in reference to the recent threads on this mailing 
list regarding sqlalchemy connections.
I heard that after a certain amount of connections, the library does 
have some problems managing them.
I have looked at some emails but I would like if some one demystifies my 
understanding or misunderstanding.
My application is based on MVC Architecture and the core logic is coded 
as XMLRPC based server side APIs.
We create a connection for every new client which connects to the rpc 
server and maintain all the connections in a list.
now I forsee a situation where more than 500 connections might be alive 
at one time in the list.
I understand that sqlalchemy has some limitations on the number of 
connections (engines ) and their respective session objects which can be 
kept alive at the same time?

if this is true, can i create some kind of a connection pool for the server?
This way connections can be recycled and used for a lot of clients and 
new connections will only be created when needed.
I want to avoide this situation, so I really want to know if there is 
some kind of upper limit on the number of engines that can be active at 
one time.


Happy hacking.
Krishnakant.



--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Multiple relationships in table to other kinds of tables

2010-03-11 Thread Noel James 

Hello,

I have a question about using multiple polymorphic tables with different 
parents which relate to the the same parent table but other polymorphic 
child.


I have two tables staff (class Staff) and contract (class Contract).
The Staff table has an identity manager (class Manager) and the contract 
table has an identity peon (class Peon).
I have a third table called payments. Payments has two identities 
identity StatusReport and BillableHours.


I want to have StausReport have a relationship to Peon 
ForeignKey('contract.id') and the BillableHours have a relationship to 
Manager ForeignKey('staff.id')


The relationships look like this:
staff.id = payments.user_id
contract.id = payments.user_id
payments.user_id = staff.id
payments.user_id = contract.id

I am using SQLAlchemy v0.5.8 on Python v2.6

Here is an *example* of one of the tests i have tried. I  hope it gives 
a better idea of what I am tring to do (and is not just confusing).
I get failures about specifying foreign_keys or Could not locate any 
equated locally mapped column pairs...

--

from   sqlalchemy import MetaData, orm, schema
from   sqlalchemy.types  import Integer, String
from   sqlalchemy.schema import Column, Sequence, ForeignKey
import sqlalchemy as sa
from   sqlalchemy.orm import sessionmaker, relation
from   sqlalchemy.ext.declarative import declarative_base

engine   = sa.create_engine( 'sqlite://' )
metadata = MetaData(   )
Base = declarative_base( metadata = metadata )
Session  = sessionmaker(  )
Session.configure( bind = engine )

def create( ):
Base.metadata.create_all( engine )

class Staff( Base ):
__tablename__ = 'staff'
id  = Column( Integer, Sequence( 'user_seq' ),
  nullable=False, primary_key=True )
name = Column( String, nullable=False )
type = Column( String, nullable=False )
__mapper_args__ = {'polymorphic_on': type }

class Manager( Staff ):
__mapper_args__ = {'polymorphic_identity': 'manager'}
Billables   = relation( 'BillableHours' )


class Contract( Base  ):
__tablename__ = 'contract'
id  = Column( Integer, Sequence( 'contract_seq' ),
  nullable=False, primary_key=True )
type = Column( String, nullable=False )
__mapper_args__ = {'polymorphic_on': type }

class Peon( Contract ):
__mapper_args__ = {'polymorphic_identity': 'peon'}
StatusReports   = relation( 'StatusReport' )


class Payments( Base ):
__tablename__ = 'payments'
id  = Column( Integer, Sequence( 'payments_seq' ),
  nullable=False, primary_key=True )
type = Column( String,  nullable=False )
__mapper_args__ = {'polymorphic_on': type }

class StatusReport( Payments ):
__mapper_args__ = {'polymorphic_identity': 'status'}
user = Column( Integer, ForeignKey('contract.id'),  nullable=False )
job  = Column( String, nullable=False, default=offshore )
hrs  = Column( Integer, nullable=False, default=0 )
Peons = relation( 'Peon' )

class BillableHours( Payments ):
__mapper_args__ = {'polymorphic_identity': 'billable'}
user = Column( Integer, ForeignKey('staff.id'),  nullable=False )
job  = Column( String, nullable=False  )
hrs  = Column( Integer, nullable=False, default=8 )
Managers = relation( 'Manager' )
--

Thanks.

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] connection pooling question, is it possible

2010-03-11 Thread Michael Bayer 
Krishnakant Mane wrote:
 hello all,
 I am working on a free software for accounting and rural banking in India.
 We use Pylons for web application.
 Now, my question is in reference to the recent threads on this mailing
 list regarding sqlalchemy connections.
 I heard that after a certain amount of connections, the library does
 have some problems managing them.
 I have looked at some emails but I would like if some one demystifies my
 understanding or misunderstanding.
 My application is based on MVC Architecture and the core logic is coded
 as XMLRPC based server side APIs.
 We create a connection for every new client which connects to the rpc
 server and maintain all the connections in a list.
 now I forsee a situation where more than 500 connections might be alive
 at one time in the list.
 I understand that sqlalchemy has some limitations on the number of
 connections (engines ) and their respective session objects which can be
 kept alive at the same time?
 if this is true, can i create some kind of a connection pool for the
 server?
 This way connections can be recycled and used for a lot of clients and
 new connections will only be created when needed.
 I want to avoide this situation, so I really want to know if there is
 some kind of upper limit on the number of engines that can be active at
 one time.

The connection pool can be configured to allow any number of connections
you'd like using the options described at
http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html#creating-engines
, or if you are using a Pool directly see the docs at
http://www.sqlalchemy.org/docs/reference/sqlalchemy/pooling.html#sqlalchemy.pool.QueuePool
.  Keep in mind that 500 connections will require a very large amount of
memory on the client machine.   Hope this helps.




 Happy hacking.
 Krishnakant.



 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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] Multiple relationships in table to other kinds of tables

2010-03-11 Thread Michael Bayer 
Noel James wrote:
 Hello,

 I have a question about using multiple polymorphic tables with different
 parents which relate to the the same parent table but other polymorphic
 child.

 I have two tables staff (class Staff) and contract (class Contract).
 The Staff table has an identity manager (class Manager) and the contract
 table has an identity peon (class Peon).
 I have a third table called payments. Payments has two identities
 identity StatusReport and BillableHours.

 I want to have StausReport have a relationship to Peon
 ForeignKey('contract.id') and the BillableHours have a relationship to
 Manager ForeignKey('staff.id')

since you are using single table inheritance, you must have distinct
attribute names per class hierarchy.  Here you have placed a user column
twice, both on StatusReport and then on BillableHours - these conflict and
apparently declarative is not smart enough to see this particular
condition ahead of time (I've added ticket #1732 with a new patch that
just needs tests for this feature).  One column will need to be renamed
unless you'd like to break those into separate tables.

Additionally, you likely don't want two relations() on the
one-to-many/many-to-one side that aren't aware of each other - you'd want
to specify back_populates='Peons' on the Peon.StatusReports relation and
back_populates='StatusReports on the StatusReport.Peons relation.



 The relationships look like this:
 staff.id = payments.user_id
 contract.id = payments.user_id
 payments.user_id = staff.id
 payments.user_id = contract.id

 I am using SQLAlchemy v0.5.8 on Python v2.6

 Here is an *example* of one of the tests i have tried. I  hope it gives
 a better idea of what I am tring to do (and is not just confusing).
 I get failures about specifying foreign_keys or Could not locate any
 equated locally mapped column pairs...
 --

 from   sqlalchemy import MetaData, orm, schema
 from   sqlalchemy.types  import Integer, String
 from   sqlalchemy.schema import Column, Sequence, ForeignKey
 import sqlalchemy as sa
 from   sqlalchemy.orm import sessionmaker, relation
 from   sqlalchemy.ext.declarative import declarative_base

 engine   = sa.create_engine( 'sqlite://' )
 metadata = MetaData(   )
 Base = declarative_base( metadata = metadata )
 Session  = sessionmaker(  )
 Session.configure( bind = engine )

 def create( ):
  Base.metadata.create_all( engine )

 class Staff( Base ):
  __tablename__ = 'staff'
  id  = Column( Integer, Sequence( 'user_seq' ),
nullable=False, primary_key=True )
  name = Column( String, nullable=False )
  type = Column( String, nullable=False )
  __mapper_args__ = {'polymorphic_on': type }

 class Manager( Staff ):
  __mapper_args__ = {'polymorphic_identity': 'manager'}
  Billables   = relation( 'BillableHours' )


 class Contract( Base  ):
  __tablename__ = 'contract'
  id  = Column( Integer, Sequence( 'contract_seq' ),
nullable=False, primary_key=True )
  type = Column( String, nullable=False )
  __mapper_args__ = {'polymorphic_on': type }

 class Peon( Contract ):
  __mapper_args__ = {'polymorphic_identity': 'peon'}
  StatusReports   = relation( 'StatusReport' )


 class Payments( Base ):
  __tablename__ = 'payments'
  id  = Column( Integer, Sequence( 'payments_seq' ),
nullable=False, primary_key=True )
  type = Column( String,  nullable=False )
  __mapper_args__ = {'polymorphic_on': type }

 class StatusReport( Payments ):
  __mapper_args__ = {'polymorphic_identity': 'status'}
  user = Column( Integer, ForeignKey('contract.id'),  nullable=False )
  job  = Column( String, nullable=False, default=offshore )
  hrs  = Column( Integer, nullable=False, default=0 )
  Peons = relation( 'Peon' )

 class BillableHours( Payments ):
  __mapper_args__ = {'polymorphic_identity': 'billable'}
  user = Column( Integer, ForeignKey('staff.id'),  nullable=False )
  job  = Column( String, nullable=False  )
  hrs  = Column( Integer, nullable=False, default=8 )
  Managers = relation( 'Manager' )
 --

 Thanks.

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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] distinct on two fields with a count

2010-03-11 Thread Moshe Cohen 
Thanks.
The DB is MySQL.



On Fri, Mar 12, 2010 at 2:16 AM, Conor conor.edward.da...@gmail.com wrote:

 Moshe C. wrote:
  How do I write an expression (in sqlalchemy 0.4.6) that will generate
  the following expression:
 
  select count(distinct  field1, field2)   from tableA;
 
  I know how to do it for one field and I know how to do it with no
  count using distinct=True, but not this combination.
 

 What database are you using? I have not heard of a dialect that accepts
 multiple columns in a COUNT() function. This seems to work on 0.4.6, but
 it is sheer luck and very fragile:
  print select([func.count(tableA.c.field1.distinct(),
 tableA.c.field2)], from_obj=tableA)
 SELECT count(DISTINCT tableA.field1, tableA.field2) AS count_1
 FROM tableA

 The standard way is to use a subquery like so:
 select count(*) from (select distinct field1, field2 from tableA) anon

 which you can do via:
  print select([func.count(text(*))],
 from_obj=select([tableA.c.field1, tableA.c.field2], distinct=True))
 SELECT count(*) AS count_1
 FROM (SELECT DISTINCT tableA.field1 AS field1, tableA.field2 AS field2
 FROM tableA)

 Hope it helps,
 -Conor

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@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 sqlalch...@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.