[sqlalchemy] Re: Creating tables in correct order
Hi, that example code works for me in PostgreSQL, after adding unique=True on the name attribute of User, and reversing the order of the drop calls. I don't have a MySQL to try against. Did you get the exact same error from it when running against MySQL? As for your actual app: 1. the log output suggests that some of your tables already exist: the ROLLBACKs are presumably to clear the does-not-exist error condition. There's no ROLLBACK after DESCRIBE `games` and DESCRIBE `dependencies`, so those tables probably exist already, and maybe they don't match the schema of your current declarative setup. create_all does not modify existing tables, so creating a new table with a foreign key against them may fail with a mismatch. That might be what the errno 150 from MySQL means. 2. that decoupled configuration may be right, but not dead-obviously so :) ... to confirm, you could check that all the tables you expect the metadata to know about are really there, and have the proper foreign keys, just before calling create_all. Something like: import pprint pprint.pprint(dict( (tn, [ (c.name, c.foreign_keys) for c in t.c if c.foreign_keys ]) for tn, t in metadata.tables.items() )) Regards, - Gulli -- 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] Delete and Concrete Table Inheritance
Hi all,
I have some 'child' tables which inherit from the same 'master' table,
now I'd like to delete some entries from all tables. The 'where'-part
comes solely from the master table.
I have decided to use Concrete Table Inheritance since I don't need
the inheritance at points other than deleting, but I expect the tables
to have a lot of entries and at the moment I don't know how many child
tables I will have in the future. Also the delete process won't occur
often.
Each table has a compound primary key of user and room.
I use version 0.4.8 (and I can't change this)
I need a delete statement that deletes according to user_id and the
length of the room identifier.
Also master is only an abstract class, so I don't need to have a table
in my database (AFAIK).
master = Table('master', metadata,
Column('user_id', Integer, ForeignKey('user.user_id',
ondelete='CASCADE'), primary_key=True),
Column('room', Unicode(16), ForeignKey('room.id'),
primary_key=True)
)
child1 = Table('child1', metadata,
Column('user_id', Integer, ForeignKey('user.user_id',
ondelete='CASCADE'), primary_key=True),
Column('room', Unicode(16), ForeignKey('room.id'),
primary_key=True),
Column('child1_data', Unicode(16))
)
child2 = Table('child2', metadata,
Column('user_id', Integer, ForeignKey('user.user_id',
ondelete='CASCADE'), primary_key=True),
Column('room', Unicode(16), ForeignKey('room.id'),
primary_key=True),
Column('child2_data', Unicode(16))
)
join = polymorphic_union({
'master': master,
'child1': child1,
'child2': child2
}, 'type', 'pjoin')
master_mapper = mapper(Master, master, with_polymorphic=('*',
vote_join), \
polymorphic_on=vote_join.c.type, polymorphic_identity='vote_data')
child1_mapper = mapper(Child1, child1, inherits=master, \
concrete=True, polymorphic_identity='child1')
child2_mapper = mapper(Child2, child2, inherits=master, \
concrete=True, polymorphic_identity='child2')
Thanks for your time,
Mene
--
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] Avoiding float results using autoload=True with Oracle and sqlalchemy 0.6.5
sqlalchemy version 0.5.3 sqlalchemy.text() used to return float
results when the database type was numeric while sqlalchemy.select()
would return decimal results, which was the desired result. In order
to avoid this I used an output type handler to ensure that all NUMERIC
results from the cx_Oracle connection were always decimals.
With the upgrade to 0.6.5, sqlalchemy.text() returns decimals
regardless of whether the cx_Oracle output type handler was used or
not.
However, sqlalchemy.select() with autoload=True returns float values
when the Oracle column type is NUMBER with no precision defined
regardless of whether the cx_Oracle output type handler was used or
not.
Is it a bug that sqlalchemy.select() is returning float values (is
this behavior different in 0.6.6 different), and is there a way to get
sqlalchemy to consistently return decimal values for Oracle NUMERIC
columns while still using autoload=True.
Here is an example showing all the returned type combinations:
#!/usr/bin/env python
import decimal
import datetime
import cx_Oracle
import sqlalchemy as sa
# msg_seq_nbinteger
# lsale_id number
# dlayd_days_ct number(2)
# entrd_pr number(18,8)
query =
select msg_seq_nb, lsale_id, dlayd_days_ct, entrd_pr
from TRCOWN1.LSALE_MSTR
where trd_rpt_dt = '2 Nov 2010' and
dlayd_days_ct is not Null and
entrd_pr is not Null
connection_string = TRCREAD/xxx...@tdr3d
cx_oracle_connection = cx_Oracle.Connection(connection_string)
cursor = cx_Oracle.Cursor(cx_oracle_connection)
cursor.execute(query)
def print_results(results_title, results):
print results_title
for row in results:
print '\t' + '\n\t'.join(map(repr, zip(map(type, row), row)))
break
print
print_results('basic cx_Oracle result', cursor.fetchall())
cursor.close()
def float_as_decimal(cursor, name, defaultType, size, precision,
scale):
if defaultType == cx_Oracle.NUMBER:
return cursor.var(str, 100, cursor.arraysize,
outconverter=decimal.Decimal)
cx_oracle_connection.outputtypehandler = float_as_decimal
cursor = cx_Oracle.Cursor(cx_oracle_connection)
cursor.execute(query)
print_results('cx_Oracle result with type converter',
cursor.fetchall())
cursor.close()
engine = sa.create_engine('oracle://trcr...@tdr3d',
connect_args={'password': ' XX '})
metadata = sa.MetaData(engine)
sqlalchemy_connection = engine.connect()
def get_sa_text_rows():
selector = sa.text(query)
return sqlalchemy_connection.execute(selector)
def get_sa_select_rows():
trd_mstr = sa.Table('LSALE_MSTR', metadata, autoload=True,
schema='TRCOWN1')
selector = sa.select([trd_mstr.c.msg_seq_nb,
trd_mstr.c.lsale_id,
trd_mstr.c.dlayd_days_ct,
trd_mstr.c.entrd_pr],
whereclause=((trd_mstr.c.trd_rpt_dt ==
datetime.datetime(2010, 11, 2))
(trd_mstr.c.dlayd_days_ct !=
None)
(trd_mstr.c.entrd_pr != None)),
)
return sqlalchemy_connection.execute(selector)
print_results('basic sqlalchemy text result', get_sa_text_rows())
print_results('basic sqlalchemy select result', get_sa_select_rows())
sqlalchemy_connection.connection.connection.outputtypehandler =
float_as_decimal
print_results('sqlalchemy text result with type converter',
get_sa_text_rows())
print_results('sqlalchemy select result with type converter',
get_sa_select_rows())
sqlalchemy_connection.close()
#Output
#basic cx_Oracle result
#(type 'int', 16043)
#(type 'int', 85784770)
#(type 'int', 0)
#(type 'float', 100.274)
#
#cx_Oracle result with type converter
#(class 'decimal.Decimal', Decimal('16043'))
#(class 'decimal.Decimal', Decimal('85784770'))
#(class 'decimal.Decimal', Decimal('0'))
#(class 'decimal.Decimal', Decimal('100.274'))
#
#basic sqlalchemy text result
#(type 'int', 16043)
#(type 'int', 85784770)
#(type 'int', 0)
#(class 'decimal.Decimal', Decimal('100.274'))
#
#basic sqlalchemy select result
#(type 'int', 16043)
#(type 'float', 85784770.0)
#(type 'int', 0)
#(class 'decimal.Decimal', Decimal('100.274'))
#
#sqlalchemy text result with type converter
#(class 'decimal.Decimal', Decimal('16043'))
#(class 'decimal.Decimal', Decimal('85784770'))
#(class 'decimal.Decimal', Decimal('0'))
#(class 'decimal.Decimal', Decimal('100.274'))
#
#sqlalchemy select result with type converter
#(class 'decimal.Decimal', Decimal('16043'))
#(type 'float', 85784770.0)
#(class 'decimal.Decimal', Decimal('0'))
#(class 'decimal.Decimal', Decimal('100.274'))
--
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
[sqlalchemy] Database Views
Hi all,
This is a topic that has been discussed before, but I haven't been
able to successfully implement any of the proposed solutions in my own
code. I've created a few Views in my postgres database, and I'm
looking for a way to simply query them from sqlalchemy. I tried just
treating them as tables, but this failed when the mapper wasn't able
to find a primary key (makes sense).
The code I tried was just a naive attempt:
class SpectrumView(object):
pass
spectrum_view = Table('spectrum_view', metadata, autoload=True)
mapper(SpectrumView, spectrum_view)
So I read in some message that it might be possible to specify a
primary key to the mapper, like
mapper(SpectrumView, spectrum_view, primary_key=[..]), but the problem
is I'm not defining my tables in python - they are already created, so
I don't have any column objects to pass to primary_key.
Anyway, I'm just curious to see if anyone has had success with an
implementation of database views in sqlalchemy, and possibly examples
of those cases.
Thanks,
Adrian
--
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] Database Views
On Nov 8, 2010, at 1:16 PM, Adrian wrote:
Hi all,
This is a topic that has been discussed before, but I haven't been
able to successfully implement any of the proposed solutions in my own
code. I've created a few Views in my postgres database, and I'm
looking for a way to simply query them from sqlalchemy. I tried just
treating them as tables, but this failed when the mapper wasn't able
to find a primary key (makes sense).
The code I tried was just a naive attempt:
class SpectrumView(object):
pass
spectrum_view = Table('spectrum_view', metadata, autoload=True)
mapper(SpectrumView, spectrum_view)
So I read in some message that it might be possible to specify a
primary key to the mapper, like
mapper(SpectrumView, spectrum_view, primary_key=[..]), but the problem
is I'm not defining my tables in python - they are already created, so
I don't have any column objects to pass to primary_key.
Anyway, I'm just curious to see if anyone has had success with an
implementation of database views in sqlalchemy, and possibly examples
of those cases.
Views are reflectable, so you can reflect the view, then add the primary key to
the proper column in the metadata.
Cheers,
M
--
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] Avoiding float results using autoload=True with Oracle and sqlalchemy 0.6.5
On Nov 8, 2010, at 11:59 AM, ACB wrote:
sqlalchemy version 0.5.3 sqlalchemy.text() used to return float
results when the database type was numeric while sqlalchemy.select()
would return decimal results, which was the desired result. In order
to avoid this I used an output type handler to ensure that all NUMERIC
results from the cx_Oracle connection were always decimals.
We now use an outputtypehandler that intercepts all numeric values as strings
and coerces to Decimal or int, based on the presence of precision and
scale, or if p and s are ambiguous, we search the string for a decimal point
to determine decimal or int.
Your scheme of overriding this handler can be achieved more cleanly using a
PoolListener, described at
http://www.sqlalchemy.org/docs/core/interfaces.html#connection-pool-events .
The connection received should already have the handler that the SQLA dialect
applies, then yours would replace it.
As far as reflection, per http://ss64.com/ora/syntax-datatypes.html we consider
NUMERIC with no p or s to be float, so the Float type is used, which by
default ensures values are python floats, not Decimal. To override this type
you'd have to use the technique at
http://www.sqlalchemy.org/docs/core/schema.html#overriding-reflected-columns ,
and add the asdecimal=True flag to the Float type.
When using a text() construct, no information is present about the types used
unless you also specify a typemap parameter:
http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=text#sqlalchemy.sql.expression.text
, so you get back exactly what whatever outputtypehandler in use is generating.
It would be nice if cx_oracle could do the work of decimal/float/int for us,
though we have identified some situations, which typically involve subqueries,
where the p and s values are ambiguous, and we really don't know what type
should be used. So the Decimal/int phase allows the SQLAlchemy types later in
the chain to receive a lossless value which it can then make decisions on
regarding ultimate Python type.
With the upgrade to 0.6.5, sqlalchemy.text() returns decimals
regardless of whether the cx_Oracle output type handler was used or
not.
However, sqlalchemy.select() with autoload=True returns float values
when the Oracle column type is NUMBER with no precision defined
regardless of whether the cx_Oracle output type handler was used or
not.
Is it a bug that sqlalchemy.select() is returning float values (is
this behavior different in 0.6.6 different), and is there a way to get
sqlalchemy to consistently return decimal values for Oracle NUMERIC
columns while still using autoload=True.
Here is an example showing all the returned type combinations:
#!/usr/bin/env python
import decimal
import datetime
import cx_Oracle
import sqlalchemy as sa
# msg_seq_nbinteger
# lsale_id number
# dlayd_days_ct number(2)
# entrd_pr number(18,8)
query =
select msg_seq_nb, lsale_id, dlayd_days_ct, entrd_pr
from TRCOWN1.LSALE_MSTR
where trd_rpt_dt = '2 Nov 2010' and
dlayd_days_ct is not Null and
entrd_pr is not Null
connection_string = TRCREAD/xxx...@tdr3d
cx_oracle_connection = cx_Oracle.Connection(connection_string)
cursor = cx_Oracle.Cursor(cx_oracle_connection)
cursor.execute(query)
def print_results(results_title, results):
print results_title
for row in results:
print '\t' + '\n\t'.join(map(repr, zip(map(type, row), row)))
break
print
print_results('basic cx_Oracle result', cursor.fetchall())
cursor.close()
def float_as_decimal(cursor, name, defaultType, size, precision,
scale):
if defaultType == cx_Oracle.NUMBER:
return cursor.var(str, 100, cursor.arraysize,
outconverter=decimal.Decimal)
cx_oracle_connection.outputtypehandler = float_as_decimal
cursor = cx_Oracle.Cursor(cx_oracle_connection)
cursor.execute(query)
print_results('cx_Oracle result with type converter',
cursor.fetchall())
cursor.close()
engine = sa.create_engine('oracle://trcr...@tdr3d',
connect_args={'password': ' XX '})
metadata = sa.MetaData(engine)
sqlalchemy_connection = engine.connect()
def get_sa_text_rows():
selector = sa.text(query)
return sqlalchemy_connection.execute(selector)
def get_sa_select_rows():
trd_mstr = sa.Table('LSALE_MSTR', metadata, autoload=True,
schema='TRCOWN1')
selector = sa.select([trd_mstr.c.msg_seq_nb,
trd_mstr.c.lsale_id,
trd_mstr.c.dlayd_days_ct,
trd_mstr.c.entrd_pr],
whereclause=((trd_mstr.c.trd_rpt_dt ==
datetime.datetime(2010, 11, 2))
(trd_mstr.c.dlayd_days_ct !=
None)
(trd_mstr.c.entrd_pr != None)),
)
return sqlalchemy_connection.execute(selector)
print_results('basic
Re: [sqlalchemy] Database Views
On Nov 8, 2010, at 1:16 PM, Adrian wrote:
Hi all,
This is a topic that has been discussed before, but I haven't been
able to successfully implement any of the proposed solutions in my own
code. I've created a few Views in my postgres database, and I'm
looking for a way to simply query them from sqlalchemy. I tried just
treating them as tables, but this failed when the mapper wasn't able
to find a primary key (makes sense).
The code I tried was just a naive attempt:
class SpectrumView(object):
pass
spectrum_view = Table('spectrum_view', metadata, autoload=True)
mapper(SpectrumView, spectrum_view)
So I read in some message that it might be possible to specify a
primary key to the mapper, like
mapper(SpectrumView, spectrum_view, primary_key=[..]), but the problem
is I'm not defining my tables in python - they are already created, so
I don't have any column objects to pass to primary_key.
The column objects are always available from table.c regardless of whether
autoload was used or not:
mapper(SpectrumView, spectrum_view, primary_key=[spectrum_view.c.somecolumn,
spectrum_view.c.someothercolumn])
Anyway, I'm just curious to see if anyone has had success with an
implementation of database views in sqlalchemy, and possibly examples
of those cases.
Thanks,
Adrian
--
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] Delete and Concrete Table Inheritance
On Nov 8, 2010, at 11:02 AM, Mene wrote:
Hi all,
I have some 'child' tables which inherit from the same 'master' table,
now I'd like to delete some entries from all tables. The 'where'-part
comes solely from the master table.
I have decided to use Concrete Table Inheritance since I don't need
the inheritance at points other than deleting, but I expect the tables
to have a lot of entries and at the moment I don't know how many child
tables I will have in the future. Also the delete process won't occur
often.
Each table has a compound primary key of user and room.
I use version 0.4.8 (and I can't change this)
I need a delete statement that deletes according to user_id and the
length of the room identifier.
Also master is only an abstract class, so I don't need to have a table
in my database (AFAIK).
There's only two choices here, you can either load the objects with the ORM and
individually pass them to session.delete(), or you can emit DELETE statements
against the tables directly using SQL expressions or strings. The SQL
expression would be along the lines of
Session.execute(master.delete().where(...)).
master = Table('master', metadata,
Column('user_id', Integer, ForeignKey('user.user_id',
ondelete='CASCADE'), primary_key=True),
Column('room', Unicode(16), ForeignKey('room.id'),
primary_key=True)
)
child1 = Table('child1', metadata,
Column('user_id', Integer, ForeignKey('user.user_id',
ondelete='CASCADE'), primary_key=True),
Column('room', Unicode(16), ForeignKey('room.id'),
primary_key=True),
Column('child1_data', Unicode(16))
)
child2 = Table('child2', metadata,
Column('user_id', Integer, ForeignKey('user.user_id',
ondelete='CASCADE'), primary_key=True),
Column('room', Unicode(16), ForeignKey('room.id'),
primary_key=True),
Column('child2_data', Unicode(16))
)
join = polymorphic_union({
'master': master,
'child1': child1,
'child2': child2
}, 'type', 'pjoin')
master_mapper = mapper(Master, master, with_polymorphic=('*',
vote_join), \
polymorphic_on=vote_join.c.type, polymorphic_identity='vote_data')
child1_mapper = mapper(Child1, child1, inherits=master, \
concrete=True, polymorphic_identity='child1')
child2_mapper = mapper(Child2, child2, inherits=master, \
concrete=True, polymorphic_identity='child2')
Thanks for your time,
Mene
--
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] table updates + missing 'from'
On Nov 8, 2010, at 2:33 PM, Jon Nelson wrote: I'd like to translate a SQL statement: UPDATE foo SET colA = bar.colB FROM bar WHERE foo.colC = bar.colC; I get this far, but am not sure how to add the FROM: stmt = foo_table.update()\ .where( foo_table.c.colC == bar_table.c.colC )\ .values( foo_table.c.colA = bar_table.c.colA ) What do I need to do here? We don't have out of the box support for PG and MySQL's extensions to UPDATE, though there's someone working on some extensions and patches you can try over at http://www.sqlalchemy.org/trac/ticket/1944 . The SQL standard method of updating based on data from other tables is to use subqueries in the WHERE clause. An example of that: http://www.sqlalchemy.org/docs/core/tutorial.html#correlated-updates . In this case you might want to phrase it as WHERE colC IN (SELECT colC FROM b). -- Jon -- 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: Database Views
Thanks for the quick reply, this is exactly what I was looking for!
Thanks again,
Adrian
On Nov 8, 2:29 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Nov 8, 2010, at 1:16 PM, Adrian wrote:
Hi all,
This is a topic that has been discussed before, but I haven't been
able to successfully implement any of the proposed solutions in my own
code. I've created a few Views in my postgres database, and I'm
looking for a way to simply query them from sqlalchemy. I tried just
treating them as tables, but this failed when the mapper wasn't able
to find a primary key (makes sense).
The code I tried was just a naive attempt:
class SpectrumView(object):
pass
spectrum_view = Table('spectrum_view', metadata, autoload=True)
mapper(SpectrumView, spectrum_view)
So I read in some message that it might be possible to specify a
primary key to the mapper, like
mapper(SpectrumView, spectrum_view, primary_key=[..]), but the problem
is I'm not defining my tables in python - they are already created, so
I don't have any column objects to pass to primary_key.
The column objects are always available from table.c regardless of whether
autoload was used or not:
mapper(SpectrumView, spectrum_view, primary_key=[spectrum_view.c.somecolumn,
spectrum_view.c.someothercolumn])
Anyway, I'm just curious to see if anyone has had success with an
implementation of database views in sqlalchemy, and possibly examples
of those cases.
Thanks,
Adrian
--
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
athttp://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] sqla and firebird
Is firebird 2.5 working with sqla. ? -- 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] (Newbie) Using a custom collection extending from a dict(). Is that doable?
Hello everyone...
I'm trying to use a custom collection to connect (or relate) two
classes but I haven't been able to do it. Maybe I got the whole
concept of the custom collections wrong, but let me explain what I am
doing (and see if someone can give me a hint, or something)
I have a Parent class (which some of you will remember from other
questions) with a couple of children. One of the children fields
stores children whose type is VR and the other children with a CC
type.
I don't really need persistence for the collection used to store the
children, but I need it to be of an special class so it will have some
methods that I have implemented and that need to be there. That would
be the ZepConnector (and, for purposes of the example, it's method
foo() it's the one I need to use). As you can see in the following
lines, I randomly test its availability in the addChild1() method of
the Parent.
- Parent.py -
from megrok import rdb
from sqlalchemy import Column
from sqlalchemy import and_
from sqlalchemy.orm import relationship
from sqlalchemy.types import Integer
from sqlalchemy.types import String
from mylibraries.database.tests.Child import Child
from mylibraries.database.tests.Tables import testMetadata
from mylibraries.database.tests.ZepConnector import ZepConnector
class Parent(rdb.Model):
rdb.metadata(testMetadata)
rdb.tablename(parents_table)
rdb.tableargs(schema='test2', useexisting=False)
id = Column(id, Integer, primary_key=True, nullable=False,
unique=True)
_whateverField1 = Column(whatever_field1, String(16)) #Irrelevant
_whateverField2 = Column(whatever_field2, String(16)) #Irrelevant
child1 = relationship(
Child,
uselist=True,
primaryjoin=lambda: and_((Parent.id == Child.parent_id),
(Child.type
== VR)),
collection_class=ZepConnector(VR)
)
child2 = relationship(
Child,
uselist=True,
primaryjoin=lambda: and_((Parent.id == Child.parent_id),
(Child.type
== CC)),
collection_class=ZepConnector(CC)
)
def __init__(self):
print Parent __init__
self._whateverField1 = Whatever1
self._whateverField2 = Whatever2
self.child1 = ZepConnector(VR)
self.child2 = ZepConnector(CC)
def addChild1(self, child):
if isinstance(child, Child):
print(::addChild1 Testing .foo method: +
str(self.child1.foo()))
# The line above doesn't really makes much but testing
the
accessibility of the .foo() method.
# As I explain later, it doesn't work
self.child1.append(child)
def addChild2(self, child):
if isinstance(child, Child):
self.child2.append(child)
Please note that I'm using megrok. For those who are not familiar with
it, allow me to explain that it is just a tool that writes the mappers
itself and makes it a little bit programmer friendly.
I guess The mapping of the Parent() class in regular SqlAlchemy would
be something like:
mapper(Parent, parents_table, properties={
id = Column(id, Integer, primary_key=True, nullable=False,
unique=True)
_whateverField1 = Column(whatever_field1, String(16)) #Irrelevant
_whateverField2 = Column(whatever_field2, String(16)) #Irrelevant
child1 = relationship( # etc, etc, etc
})
#
but I'm 100%... erm... 90% certain that using that tool is not what
lead me to ask what I'm going to ask here (I mean: I don't think is
interfering with the Collections thing)
A child is a very simple class:
--- Child.py --
import random
from megrok import rdb
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy.types import Integer
from sqlalchemy.types import String
from mylibraries.database.tests.Tables import testMetadata
class Child(rdb.Model):
rdb.metadata(testMetadata)
rdb.tablename(children_table)
rdb.tableargs(schema='test2', useexisting=False)
parent_id = Column(parent_id, Integer,
ForeignKey(test2.parents_table.id), primary_key=True)
type = Column(type, String(2), nullable=True, primary_key=True)
hasher = Column(hasher, String(5))
def __init__(self):
self.type = None
self.hasher = self.generateHasher()
def setType(self, typeParameter):
if typeParameter in set([VR, CC]):
self.type = typeParameter
@staticmethod
def generateHasher():
retval = str()
for i in
[sqlalchemy] polymorphic_identity not recognized with non-primary mapper
When I create a non-primary mapper for my class to add an additional computed value in certain contexts when the query runs I get the error: AssertionError: No such polymorphic_identity 'M' is defined This works fine with the primary mapper as several classes are defined as having polymorphic identities. Is there a reason why this wouldn't work? It's entirely possible that I'm approaching this in the wrong way. One of the primary queries used in our application does a distance search and requires that several distances be calculated in order to perform the query in a reasonable timeframe. This query is currently being executed as a text statement. I'd like to end up with the normal behaviour of orm but have the distance value attached to the object when all is said and done. The non-primary mapper has a 'distance' column_property which is just declared as a Column() since the selecting is done with the text sql statement. Previously I was just selecting the id and distance and then running separate queries to get each result as an object which seems horribly inefficient. But it was working. I'd appreciate some help with this. 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] polymorphic_identity not recognized with non-primary mapper
On Nov 8, 2010, at 6:17 PM, William wrote: When I create a non-primary mapper for my class to add an additional computed value in certain contexts when the query runs I get the error: AssertionError: No such polymorphic_identity 'M' is defined This works fine with the primary mapper as several classes are defined as having polymorphic identities. Is there a reason why this wouldn't work? its not something I've ever tried, I believe a non_primary mapper replacing the base for a set of inherited mappers might not be very straightforward since the NP doesn't have the polymorphic_map set up. You might need to copy that attribute over from the primary. But NP mappers are almost a non-use case at this point. It's entirely possible that I'm approaching this in the wrong way. One of the primary queries used in our application does a distance search and requires that several distances be calculated in order to perform the query in a reasonable timeframe. This query is currently being executed as a text statement. I'd like to end up with the normal behaviour of orm but have the distance value attached to the object when all is said and done. Usually you'd define additional computed values on the primary mapper (i.e. column_property(), or deferred(), which lets you enable their inline-loading via options()). In my own practice I use so called hybrid properties for things like this, so that I can add them to a query independently, i.e. query(MyClass.id, MyClass.some_computed_value), or I can get at them on existing instances using Python expressions, myobject.some_computed_value. hybrid properties will be featured prominently in 0.7, and you can use them now based on the derived_attributes/attributes.py example.Or use a plain descriptor that uses object_session(self).query() to invoke a supplemental query lazily. -- 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] sqla and firebird
not sure if anyone knows. I have 2.1 running here for my own tests. On Nov 8, 2010, at 5:05 PM, Domingo Aguilera wrote: Is firebird 2.5 working with sqla. ? -- 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: sqla and firebird
I've been working with a client server GUI app that uses sqlalchemy to handle many backends ( sqlserver, mysql, postgresql , etc ). Now one customer wants that app running with firebird. When I create the tables it seems that primary keys columns are not using generators ( which is the thing that do the autoincrement stuff ). Don't know if this behaviour of not using autoincrement in pk by default is normal in firebird /sqla . On Nov 8, 6:34 pm, Michael Bayer mike...@zzzcomputing.com wrote: not sure if anyone knows. I have 2.1 running here for my own tests. On Nov 8, 2010, at 5:05 PM, Domingo Aguilera wrote: Is firebird 2.5 working with sqla. ? -- 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 athttp://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: sqla and firebird
the FB dialect uses sequences. You have to use the Sequence() construct on your PK columns with Firebird. On Nov 8, 2010, at 8:32 PM, Domingo Aguilera wrote: I've been working with a client server GUI app that uses sqlalchemy to handle many backends ( sqlserver, mysql, postgresql , etc ). Now one customer wants that app running with firebird. When I create the tables it seems that primary keys columns are not using generators ( which is the thing that do the autoincrement stuff ). Don't know if this behaviour of not using autoincrement in pk by default is normal in firebird /sqla . On Nov 8, 6:34 pm, Michael Bayer mike...@zzzcomputing.com wrote: not sure if anyone knows. I have 2.1 running here for my own tests. On Nov 8, 2010, at 5:05 PM, Domingo Aguilera wrote: Is firebird 2.5 working with sqla. ? -- 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 athttp://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] Re: polymorphic_identity not recognized with non-primary mapper
Ahhh, thank you very much, the following seems to be working for me
now:
results = Session.query(Content, Column('distance', Float))\
.from_statement(text(stmt)).all()
contents = []
for content, distance in results:
content.distance = distance
contents.append(content)
return contents
For whatever reason it never occurred to me to try it that way.
Thanks again.
On Nov 8, 4:32 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Nov 8, 2010, at 6:17 PM, William wrote:
When I create a non-primary mapper for my class to add an additional
computed value in certain contexts when the query runs I get the
error:
AssertionError: No such polymorphic_identity 'M' is defined
This works fine with the primary mapper as several classes are defined
as having polymorphic identities. Is there a reason why this wouldn't
work?
its not something I've ever tried, I believe a non_primary mapper replacing
the base for a set of inherited mappers might not be very straightforward
since the NP doesn't have the polymorphic_map set up. You might need to copy
that attribute over from the primary. But NP mappers are almost a
non-use case at this point.
It's entirely possible that I'm approaching this in the wrong way. One
of the primary queries used in our application does a distance search
and requires that several distances be calculated in order to perform
the query in a reasonable timeframe. This query is currently being
executed as a text statement. I'd like to end up with the normal
behaviour of orm but have the distance value attached to the object
when all is said and done.
Usually you'd define additional computed values on the primary mapper (i.e.
column_property(), or deferred(), which lets you enable their inline-loading
via options()). In my own practice I use so called hybrid properties for
things like this, so that I can add them to a query independently, i.e.
query(MyClass.id, MyClass.some_computed_value), or I can get at them on
existing instances using Python expressions, myobject.some_computed_value.
hybrid properties will be featured prominently in 0.7, and you can use them
now based on the derived_attributes/attributes.py example. Or use a plain
descriptor that uses object_session(self).query() to invoke a supplemental
query lazily.
--
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: sqla and firebird
I wonder if you have a very simple example of this. Tks in advance. On Nov 8, 8:13 pm, Michael Bayer mike...@zzzcomputing.com wrote: the FB dialect uses sequences. You have to use the Sequence() construct on your PK columns with Firebird. On Nov 8, 2010, at 8:32 PM, Domingo Aguilera wrote: I've been working with a client server GUI app that uses sqlalchemy to handle many backends ( sqlserver, mysql, postgresql , etc ). Now one customer wants that app running with firebird. When I create the tables it seems that primary keys columns are not using generators ( which is the thing that do the autoincrement stuff ). Don't know if this behaviour of not using autoincrement in pk by default is normal in firebird /sqla . On Nov 8, 6:34 pm, Michael Bayer mike...@zzzcomputing.com wrote: not sure if anyone knows. I have 2.1 running here for my own tests. On Nov 8, 2010, at 5:05 PM, Domingo Aguilera wrote: Is firebird 2.5 working with sqla. ? -- 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 athttp://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 athttp://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: sqla and firebird
Forget about this Michael, I've found the way. i.e. id = Column( Integer, Sequence( column_seq_id, optional = True ), primary_key = True ) On Nov 8, 8:27 pm, Domingo Aguilera domingo.aguil...@gmail.com wrote: I wonder if you have a very simple example of this. Tks in advance. On Nov 8, 8:13 pm, Michael Bayer mike...@zzzcomputing.com wrote: the FB dialect uses sequences. You have to use the Sequence() construct on your PK columns with Firebird. On Nov 8, 2010, at 8:32 PM, Domingo Aguilera wrote: I've been working with a client server GUI app that uses sqlalchemy to handle many backends ( sqlserver, mysql, postgresql , etc ). Now one customer wants that app running with firebird. When I create the tables it seems that primary keys columns are not using generators ( which is the thing that do the autoincrement stuff ). Don't know if this behaviour of not using autoincrement in pk by default is normal in firebird /sqla . On Nov 8, 6:34 pm, Michael Bayer mike...@zzzcomputing.com wrote: not sure if anyone knows. I have 2.1 running here for my own tests. On Nov 8, 2010, at 5:05 PM, Domingo Aguilera wrote: Is firebird 2.5 working with sqla. ? -- 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 athttp://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 athttp://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] Does SQLAlchemy support read-write splitting?
Hello, Does SQLAlchemy support read-write splitting? It seems not mentioned in the document. Or does it is not intended to support in ORM layer? This is somehow important for scaling , and could be convenient if we can achieve this in SQLAlchemy. Thanks, Li-Wen -- 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.
