[sqlalchemy] dictionary returning a collection
Dear All,
Send me some study material on DBMS + $100 !
Or if it fits you better, can you please help on the issue described
below?
The closest situation to what I am facing is described here:
http://www.sqlalchemy.org/docs/04/plugins.html#plugins_associationproxy_
building
But unfortunately the situation is not close enough to help me.
Now, based on the example above, here is the issue:
stocks = Table(stocks, meta,
Column('symbol', String(10), primary_key=True),
Column('description', String(100), nullable=False),
Column('last_price', Numeric)
)
brokers = Table(brokers, meta,
Column('id', Integer,primary_key=True),
Column('name', String(100), nullable=False)
)
holdings = Table(holdings, meta,
Column('broker_id', Integer,
ForeignKey('brokers.id'), primary_key=True),
Column('symbol', String(10),
ForeignKey('stocks.symbol'), primary_key=True),
Column('shares', Integer),
Column('date', DateTime, primary_key=True) # -- HERE IS THE
DIFFERENCE
)
The example in the doc provides a nice way to retrieve one holding:
holding = broker.holdings[stock]
But what if holdings are parameterized by a new attribute ('date' in our
case)?
How would you implement a solution allowing you to do something like
this:
date = datetime.datetime(2007,1,1)
holdings = broker.holdingsByDate[date]
where 'holdings' is a collection of what the broker is holding at a
given date.
Thank you for your help,
Christophe
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Tipo Money con Postgres
Hola a todos... Estoy intentando utilizar en una bd postgres el tipo Money, debido a que estoy haciendo una migracion de una bd vieja y necesito mantener ese tipo de datos. Pero veo que SQLAlchemy no lo posee, y no esta definido como tipo nativo de Postgres. Entonces, cual seria la mejor forma de usarlo? Creando un nuevo tipo de datos? Como seria eso? Si no me he expresado bien, me avisan y reformulo mi consulta. Cualquier ayuda que reciba, desde ya la agradezco Marcos --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Large eager fetches not so eager?
On Jan 16, 2008, at 8:44 PM, Rick Morrison wrote: Thanks for the tips. I am sure that none of the objects are loaded beforehand: I've got it localized to a run of unit tests that I can start afresh each time. So far it's intermittent, seems to happen only one of say 10 runs. Happens more frequently on a fresh build of sample data for the test runs. The sample data build takes 45mins, so it's painful to run repeatedly. I'll see if I can wire up something that will loop on it overnight. yeah but, you should really put sanity checks in regardlessif you run the thing all night and you get the error 10 times, that doesnt say much unless you can narrow it down. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to MSSQL with a System DSN on Windows
On Jan 16, 7:38 pm, Paul Johnston [EMAIL PROTECTED] wrote: Hi, 'mssql://DSN=MyDSN'. How can I get this to work without specifying You were nearly there... mssql://?dsn=mydsn Paul Thanks, Paul, For some reason it didn't work for me (sqlalchemy 0.4.2p3). I tried mssql://?dsn=MyDSN, and also mssql://?DSN=MyDSN. Oh well. Vinay --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Tipo Money con Postgres
Hola a todos... Estoy intentando utilizar en una bd postgres el tipo Money, debido a que estoy haciendo una migracion de una bd vieja y necesito mantener ese tipo de datos. Pero veo que SQLAlchemy no lo posee, y no esta definido como tipo nativo de Postgres. Entonces, cual seria la mejor forma de usarlo? Creando un nuevo tipo de datos? Como seria eso? Si no me he expresado bien, me avisan y reformulo mi consulta. Cualquier ayuda que reciba, desde ya la agradezco Maybe is better you answer in English, however I will answer you in Portuguese. Eu estou utilizando Numeric(18,4) creio que deva ser este o tipo interno utilizado no money, eu mesmo iria pesquisar ou perguntar aqui no grupo a respeito disso. --- May someone have a best solution to postgresql Money datatype? or I need to use Numeric? thank's -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to MSSQL with a System DSN on Windows
Hi,
For some reason it didn't work for me (sqlalchemy 0.4.2p3). I tried
mssql://?dsn=MyDSN, and also mssql://?DSN=MyDSN. Oh well.
Sorry, it's
mssql:///?dsn=mydsn
This definitely works, just tested it
eng = sqlalchemy.create_engine('mssql:///?dsn=bob')
eng.execute('select 1')
sqlalchemy.engine.base.ResultProxy object at 0x00D17CB0
Paul
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Schema Display recipe
I was just taking a look at the recipes on the SA wiki and stumbled across this one: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay It is a pretty nice little piece of code to automatically create schema diagrams from a DB engine and UML diagrams for a set of mappers. I am very interested in using this because I think it would help documentation on our current project quite a bit. It is very easy for a diagram to get out of sync with the code, and this looks like a great tool to generate the diagram directly from the code. Very sweet. :) The problem is that I can't seem to get the code to work with SA 0.3.11. It looks like it expects some old naming conventions to get mappers. This seems to me like a very nice tool that could prove useful as an addon to SA. Am I alone in thinking this or is anyone else successfully using it? -Allen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Large eager fetches not so eager?
yeah but, you should really put sanity checks in regardlessif you run the thing all night and you get the error 10 times, that doesnt say much unless you can narrow it down. uh, yeah I figured that was assumed. In addition to some application checks, I've got four checks in the SA machinery so far: one in Mapper._instance() inside the if identitykey in session.identity_map two in EagerLoader._create_row_decorator(): one for the can't make alias case, and one for the no identity key case one in EagerLoader._create_row_processor(): for the degrading to lazy case (really this is the same check as one of the previous two) The EagerLoader checks I'm running full-time, the Mapper check is behind a check for a global set by the test setup. So far no hits. I think it knows I'm watching :0 On Jan 17, 2008 10:54 AM, Michael Bayer [EMAIL PROTECTED] wrote: On Jan 16, 2008, at 8:44 PM, Rick Morrison wrote: Thanks for the tips. I am sure that none of the objects are loaded beforehand: I've got it localized to a run of unit tests that I can start afresh each time. So far it's intermittent, seems to happen only one of say 10 runs. Happens more frequently on a fresh build of sample data for the test runs. The sample data build takes 45mins, so it's painful to run repeatedly. I'll see if I can wire up something that will loop on it overnight. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Tipo Money con Postgres
Em Qui, 2008-01-17 às 10:40 -0800, Marcos escreveu: Thank's Alexandre for the tip, but in my first and only one answer, y received a pair of solutions in spanish, so I tried first to ask in my language. As you can see, I'm not very good writing in english. But you are right. I will try to ask in this language from now. And thanks for the solution. That's works for me. ( E graças pela solução, isso funcionou para mim ) -- I used a translator for that : ) Marcos Alcázar No problems, my English is very bad too, because it's not my fist language and I learn more from internet and working with it. about your question, I think numeric will work for you. more one tip, I've found at postgresql documentation this note: Note: The money type is deprecated. Use numeric or decimal instead, in combination with the to_char function. from 7.3 to 8.2... and maybe others in http://www.postgresql.org/docs/7.3/interactive/datatype-money.html and http://www.postgresql.org/docs/8.2/interactive/datatype-money.html I suggest, if you can, change the datatype in next versions of your software. Att, -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: dictionary returning a collection
Christophe Alexandre wrote:
Dear All,
Send me some study material on DBMS + $100 !
Or if it fits you better, can you please help on the issue described
below?
The closest situation to what I am facing is described here:
http://www.sqlalchemy.org/docs/04/plugins.html#plugins_associationproxy_
building
But unfortunately the situation is not close enough to help me.
Now, based on the example above, here is the issue:
stocks = Table(stocks, meta,
Column('symbol', String(10), primary_key=True),
Column('description', String(100), nullable=False),
Column('last_price', Numeric)
)
brokers = Table(brokers, meta,
Column('id', Integer,primary_key=True),
Column('name', String(100), nullable=False)
)
holdings = Table(holdings, meta,
Column('broker_id', Integer,
ForeignKey('brokers.id'), primary_key=True),
Column('symbol', String(10),
ForeignKey('stocks.symbol'), primary_key=True),
Column('shares', Integer),
Column('date', DateTime, primary_key=True) # -- HERE IS THE
DIFFERENCE
)
The example in the doc provides a nice way to retrieve one holding:
holding = broker.holdings[stock]
But what if holdings are parameterized by a new attribute ('date' in our
case)?
How would you implement a solution allowing you to do something like
this:
date = datetime.datetime(2007,1,1)
holdings = broker.holdingsByDate[date]
where 'holdings' is a collection of what the broker is holding at a
given date.
A couple approaches come to mind. How many Holdings rows in total do
you expect per Broker?
If the number is large, then on-demand querying for chunks of the set by
date could be a good option. That can be wrapped up in a dict-like
class with __getattr__ for an interface like holdingsByDate[date]. You
could cache too, if you wanted. No mapper relation from Broker-Holding
would be needed, but you'd want Holding-Broker.
If the number is small enough that loading the entire collection of
associated Holdings per Broker isn't a big deal, then a whole bunch of
options come to mind, but none of these are built-in.
- A dict-like proxy similar to the large collection approach, except
scanning .holdings in-memory and filtering by .date on demand
- Add some append/remove event handlers to the collection backing
.holding, and use them to maintain a obj.holdingsByDate dictionary
transparently
- Use the collections framework to build a fully featured partitioned
collection type
These are all pretty simple to implement- something like #2 is just a
few lines of code.
An implementation of #3 (a dictionary of lists with full instrumented
goodness) is probably not a ton of work at all and could make a good
addition to either the collections or ext package.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Tipo Money con Postgres
Thank's Alexandre for the tip, but in my first and only one answer, y received a pair of solutions in spanish, so I tried first to ask in my language. As you can see, I'm not very good writing in english. But you are right. I will try to ask in this language from now. And thanks for the solution. That's works for me. ( E graças pela solução, isso funcionou para mim ) -- I used a translator for that : ) Marcos Alcázar On 17 ene, 15:49, Alexandre da Silva [EMAIL PROTECTED] wrote: Hola a todos... Estoy intentando utilizar en una bd postgres el tipo Money, debido a que estoy haciendo una migracion de una bd vieja y necesito mantener ese tipo de datos. Pero veo que SQLAlchemy no lo posee, y no esta definido como tipo nativo de Postgres. Entonces, cual seria la mejor forma de usarlo? Creando un nuevo tipo de datos? Como seria eso? Si no me he expresado bien, me avisan y reformulo mi consulta. Cualquier ayuda que reciba, desde ya la agradezco Maybe is better you answer in English, however I will answer you in Portuguese. Eu estou utilizando Numeric(18,4) creio que deva ser este o tipo interno utilizado no money, eu mesmo iria pesquisar ou perguntar aqui no grupo a respeito disso. --- May someone have a best solution to postgresql Money datatype? or I need to use Numeric? thank's -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Schema Display recipe
Allen Bierbaum wrote: Thanks, that worked great. Have their been any new capabilities added to this code? no idea, never used it -Allen On Jan 17, 2008 12:21 PM, [EMAIL PROTECTED] wrote: use sqlalchemy.orm.class_mapper(cls) instead of cls.mapper, and it should work? Allen Bierbaum wrote: I was just taking a look at the recipes on the SA wiki and stumbled across this one: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to MSSQL with a System DSN on Windows
On Jan 17, 5:59 pm, Paul Johnston [EMAIL PROTECTED] wrote:
Hi,
For some reason it didn't work for me (sqlalchemy 0.4.2p3). I tried
mssql://?dsn=MyDSN, and also mssql://?DSN=MyDSN. Oh well.
Sorry, it's
mssql:///?dsn=mydsn
This definitely works, just tested it
eng = sqlalchemy.create_engine('mssql:///?dsn=bob')
eng.execute('select 1')
sqlalchemy.engine.base.ResultProxy object at 0x00D17CB0
Paul
Oh, I see... trying it with the extra / ... It works for me, too.
Thanks!
Vinay
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Schema Display recipe
Thanks, that worked great. Have their been any new capabilities added to this code? -Allen On Jan 17, 2008 12:21 PM, [EMAIL PROTECTED] wrote: use sqlalchemy.orm.class_mapper(cls) instead of cls.mapper, and it should work? Allen Bierbaum wrote: I was just taking a look at the recipes on the SA wiki and stumbled across this one: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay It is a pretty nice little piece of code to automatically create schema diagrams from a DB engine and UML diagrams for a set of mappers. I am very interested in using this because I think it would help documentation on our current project quite a bit. It is very easy for a diagram to get out of sync with the code, and this looks like a great tool to generate the diagram directly from the code. Very sweet. :) The problem is that I can't seem to get the code to work with SA 0.3.11. It looks like it expects some old naming conventions to get mappers. This seems to me like a very nice tool that could prove useful as an addon to SA. Am I alone in thinking this or is anyone else successfully using it? -Allen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Schema Display recipe
use sqlalchemy.orm.class_mapper(cls) instead of cls.mapper, and it should work? Allen Bierbaum wrote: I was just taking a look at the recipes on the SA wiki and stumbled across this one: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay It is a pretty nice little piece of code to automatically create schema diagrams from a DB engine and UML diagrams for a set of mappers. I am very interested in using this because I think it would help documentation on our current project quite a bit. It is very easy for a diagram to get out of sync with the code, and this looks like a great tool to generate the diagram directly from the code. Very sweet. :) The problem is that I can't seem to get the code to work with SA 0.3.11. It looks like it expects some old naming conventions to get mappers. This seems to me like a very nice tool that could prove useful as an addon to SA. Am I alone in thinking this or is anyone else successfully using it? -Allen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: dictionary returning a collection
jason kirtland wrote:
Christophe Alexandre wrote:
Dear All,
Send me some study material on DBMS + $100 !
Or if it fits you better, can you please help on the issue described
below?
The closest situation to what I am facing is described here:
http://www.sqlalchemy.org/docs/04/plugins.html#plugins_associationproxy_
building
But unfortunately the situation is not close enough to help me.
Now, based on the example above, here is the issue:
stocks = Table(stocks, meta,
Column('symbol', String(10), primary_key=True),
Column('description', String(100), nullable=False),
Column('last_price', Numeric)
)
brokers = Table(brokers, meta,
Column('id', Integer,primary_key=True),
Column('name', String(100), nullable=False)
)
holdings = Table(holdings, meta,
Column('broker_id', Integer,
ForeignKey('brokers.id'), primary_key=True),
Column('symbol', String(10),
ForeignKey('stocks.symbol'), primary_key=True),
Column('shares', Integer),
Column('date', DateTime, primary_key=True) # -- HERE IS THE
DIFFERENCE
)
The example in the doc provides a nice way to retrieve one holding:
holding = broker.holdings[stock]
But what if holdings are parameterized by a new attribute ('date' in our
case)?
How would you implement a solution allowing you to do something like
this:
date = datetime.datetime(2007,1,1)
holdings = broker.holdingsByDate[date]
where 'holdings' is a collection of what the broker is holding at a
given date.
A couple approaches come to mind. How many Holdings rows in total do
you expect per Broker?
If the number is large, then on-demand querying for chunks of the set by
date could be a good option. That can be wrapped up in a dict-like
class with __getattr__ for an interface like holdingsByDate[date]. You
could cache too, if you wanted. No mapper relation from Broker-Holding
would be needed, but you'd want Holding-Broker.
If the number is small enough that loading the entire collection of
associated Holdings per Broker isn't a big deal, then a whole bunch of
options come to mind, but none of these are built-in.
- A dict-like proxy similar to the large collection approach, except
scanning .holdings in-memory and filtering by .date on demand
- Add some append/remove event handlers to the collection backing
.holding, and use them to maintain a obj.holdingsByDate dictionary
transparently
- Use the collections framework to build a fully featured partitioned
collection type
These are all pretty simple to implement- something like #2 is just a
few lines of code.
An implementation of #3 (a dictionary of lists with full instrumented
goodness) is probably not a ton of work at all and could make a good
addition to either the collections or ext package.
hmm. this sounds interesting.
in dbcook i have a similar testcase, where the intermediate association
table has 3 columns in its primary key, but a) they are all references to
somewhere b) its only sort of syntax test and doesnt do anything real with
3rd column.
thanks for reminding me.. one day i may need it
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] ceODBC support in sqlalchemy?
I was able to connect to mssql vi ceODBC driver on linux using unixODBC.
How hard would it be to support ceODBC in sqlalchemy?
--
Instructions to setup dsn on unix:
http://lucasmanual.com/mywiki/TurboGears#head-bab4c0bbfb83fb7c376a8a0cb68841c4e76ad130
apt-get install python-dev
apt-get install unixodbc
apt-get install unixodbc-dev
To get ceODBC:
svn co https://ceodbc.svn.sourceforge.net/svnroot/ceodbc/trunk
cd trunk
python setup.py build
python setup.py install
In python:
import ceODBC
c = ceODBC.Connection(DSN=MYDSN;UID=USERNAME;PWD=PASSWORD)
cursor=c.cursor()
cursor.execute('SELECT * FROM INFORMATION_SCHEMA.TABLES')
ceODBC.Cursor on ceODBC.Connection to DSN=MYDSN;UID=USERNAME;PWD=PASSWORD
data=cursor.fetchall()
and you are connected.
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] msqsql and sqlalchemy on linux -was Re: mssql connection uri
On Jan 14, 2008 3:21 PM, Rick Morrison [EMAIL PROTECTED] wrote:
The mssql URI is going to take the same conceptual form as any other
sqlalchemry dburi. The format is documented and available in the SA docs. We
can't give you the exact URI, because it's going to contain things that are
specific to your application, like database name, password, etc.
ODBC under Linux I can't help you with (yet). Your best bet for MSSQL + *nix
is going to be using pymssql for now. Pymssql (and any other unix-to-MSSQL
connection scheme), is going to require FreeTDS to establish the basic
network connection. Installing FreeTDS on Linux is going to be different for
various distributions, but in general is available via normal package
repositories. From your sig, I'll assume you're looking for a Debian dpkg
package. I know there's one for Ubuntu, which should install on Debian, and
there may be a straight-up Debian package in their repositories as well.
Make sure you get a fairly recent version, as older versions assumed a
Sybase set of connection parameters instead of MSSQL. The FreeTDS docs
outline the issue with the various protocol versions. If you have the
protocol version set wrong, then MSSQL dates won't transfer correctly. Much
of this information is on the SQLAlchemy wiki under database notes, check
there as well.
Here are the instruction on how to setup the dsn in linux using
unixODBC and freeTDS
http://lucasmanual.com/mywiki/TurboGears#head-bab4c0bbfb83fb7c376a8a0cb68841c4e76ad130
Now I tried connecting to mssql via:
0.3
db = sqlalchemy.create_engine('mssql://user:[EMAIL PROTECTED]/dbname',
module=adodbapi)
db = sqlalchemy.create_engine('mssql://user:[EMAIL PROTECTED]/dbname',
module=adodbapi)
Traceback (most recent call last):
File stdin, line 1, in ?
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/__init__.py,
line 91, in create_engine
return strategy.create(*args, **kwargs)
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/strategies.py,
line 41, in create
dialect = module.dialect(**dialect_args)
File /usr/lib/python2.4/site-packages/sqlalchemy/databases/mssql.py,
line 270, in __init__
ansisql.ANSIDialect.__init__(self, **params)
File /usr/lib/python2.4/site-packages/sqlalchemy/ansisql.py, line
33, in __init__
super(ANSIDialect,self).__init__(**kwargs)
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/default.py,
line 34, in __init__
self._figure_paramstyle()
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/default.py,
line 96, in _figure_paramstyle
self._paramstyle = db.paramstyle
AttributeError: 'module' object has no attribute 'paramstyle'
trunk:
db = sqlalchemy.create_engine('mssql://user:[EMAIL PROTECTED]/dbname')
Traceback (most recent call last):
File stdin, line 1, in ?
File sqlalchemy/engine/__init__.py, line 173, in create_engine
return strategy.create(*args, **kwargs)
File sqlalchemy/engine/strategies.py, line 62, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File sqlalchemy/databases/mssql.py, line 443, in dbapi
raise ImportError('No DBAPI module detected for MSSQL - please
install pyodbc, pymssql, or adodbapi')
ImportError: No DBAPI module detected for MSSQL - please install
pyodbc, pymssql, or adodbapi
db = sqlalchemy.create_engine(''mssql://user:[EMAIL PROTECTED]/dbname'',
module=adodbapi)
Traceback (most recent call last):
File stdin, line 1, in ?
File sqlalchemy/engine/__init__.py, line 173, in create_engine
return strategy.create(*args, **kwargs)
File sqlalchemy/engine/strategies.py, line 67, in create
dialect = dialect_cls(**dialect_args)
File sqlalchemy/databases/mssql.py, line 418, in __new__
return dialect(*args, **kwargs)
TypeError: 'NoneType' object is not callable
- odbc ---
Now I tired the odbc version
---0.3--
eng = create_engine('mssql:///?dsn=MYDSN')
Traceback (most recent call last):
File stdin, line 1, in ?
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/__init__.py,
line 91, in create_engine
return strategy.create(*args, **kwargs)
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/strategies.py,
line 52, in create
raise exceptions.InvalidRequestError(Cant get DBAPI module for
dialect '%s' % dialect)
sqlalchemy.exceptions.InvalidRequestError: Cant get DBAPI module for
dialect 'sqlalchemy.databases.mssql.MSSQLDialect object at
0xb7869c0c'
--trunk--
from sqlalchemy import *
eng = create_engine('mssql:///?dsn=MYDSN')
Traceback (most recent call last):
File stdin, line 1, in ?
File sqlalchemy/engine/__init__.py, line 173, in create_engine
return strategy.create(*args, **kwargs)
File sqlalchemy/engine/strategies.py, line 62, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File sqlalchemy/databases/mssql.py, line 443, in dbapi
raise ImportError('No DBAPI module detected for
[sqlalchemy] Re: msqsql and sqlalchemy on linux -was Re: mssql connection uri
Adodbapi is Windows-only, you're definitely barking up the wrong tree with that one. Pyodbc on Linux is theoretically possible, but I know of no users and have very little experience myself with it. Pymssql is supported on Linux, and it works within certain restrictions (30 char identifier length, no unicode) as outlined on the SQLAlchemy wiki. You're pretty much breaking new ground if you're trying pyodbc + SQLAlchemy + Unix. Not that I'm discouraging that, I've been meaning to tackle it, but just so you know But Linux + adodbapi is not going to fly at all. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: msqsql and sqlalchemy on linux -was Re: mssql connection uri
Wow, sweet! That is going to be a *huge* addition. Let me know when you're close enough to do some MSSQL testing. Rick On Jan 17, 2008 4:35 PM, jason kirtland [EMAIL PROTECTED] wrote: Rick Morrison wrote: Adodbapi is Windows-only, you're definitely barking up the wrong tree with that one. Pyodbc on Linux is theoretically possible, but I know of no users and have very little experience myself with it. Pymssql is supported on Linux, and it works within certain restrictions (30 char identifier length, no unicode) as outlined on the SQLAlchemy wiki. You're pretty much breaking new ground if you're trying pyodbc + SQLAlchemy + Unix. Not that I'm discouraging that, I've been meaning to tackle it, but just so you know But Linux + adodbapi is not going to fly at all. A devel aside: I have pyodbc on Unix nearly working with MySQL. I haven't tried pyodbc/MSSQL from Unix yet. I've got a local branch going to formalize the multi-driver support ease using SQLAlchemy on alternate VMs. Adding ODBC to a single-driver dialect in CPython was my step 1, next up is sqlite via JDBC for Jython, and then the same in IronPython. -J --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ceODBC support in sqlalchemy?
Theoretically it should not be very different at all than pyodbc support. However we have three db modules already in MSSQL, and pyodbc is already being used for Windows (so it's not coming out). Adding a fourth db-api module in MSSQL is not a terribly attractive option for me. If you want to have a go at it, feel free: I'll review the code. But first, I would consider pymssql, or give pyodbc another shot. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: msqsql and sqlalchemy on linux -was Re: mssql connection uri
Rick Morrison wrote: Adodbapi is Windows-only, you're definitely barking up the wrong tree with that one. Pyodbc on Linux is theoretically possible, but I know of no users and have very little experience myself with it. Pymssql is supported on Linux, and it works within certain restrictions (30 char identifier length, no unicode) as outlined on the SQLAlchemy wiki. You're pretty much breaking new ground if you're trying pyodbc + SQLAlchemy + Unix. Not that I'm discouraging that, I've been meaning to tackle it, but just so you know But Linux + adodbapi is not going to fly at all. A devel aside: I have pyodbc on Unix nearly working with MySQL. I haven't tried pyodbc/MSSQL from Unix yet. I've got a local branch going to formalize the multi-driver support ease using SQLAlchemy on alternate VMs. Adding ODBC to a single-driver dialect in CPython was my step 1, next up is sqlite via JDBC for Jython, and then the same in IronPython. -J --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Setting instrument list items
I'm having trouble with the instrumented lists used for relation collections: def setProp(self, new_prop, exclusive=True): self.props = [p for p if p.typ != new_prop.typ] self.props.append(new_prop) so if 'props' is a relation on some mapped class, and I try C.setProp(newprop), then a later flush() fails when it tries to write a null FK in the properties table. I suspect it's the list comprehension when there's items in there that haven't been flushed yet. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Same schema, multiple databases, simultaneously
Are you using auto-incrementing primary keys?
I would bet that after the first flush(), your mapped object now has a
primary key, and so looks persistent.
Perhaps try a copy.copy() on the mapped object before the first flush, write
one copy to the first database, and the copy to the second, unless Mike has
a better idea.
Rick
On Jan 17, 2008 6:40 PM, Paul-Michael Agapow [EMAIL PROTECTED]
wrote:
I'm puzzling out how to connect to multiple databases that have the
same tables (and the same table definitions). The use case is that
data may be retrieved from two or more similar dbs and perhaps copied
between them. Following earlier advice, I've used a single metadata
and 'bind' in the session creation to indicate which engine. However
there's a problem. Example code below::
from sqlalchemy import *
from sqlalchemy.orm import *
uri1 = # blah blah
uri2 = # blah blah
class Dummy (object):
simple mapped class
pass
class DbConnection (object):
encapsulate engine session
def __init__ (self, uri, dbschema):
engine = create_engine (uri)
Session = sessionmaker (bind=engine,
autoflush=False,
transactional=False)
self._session = Session()
dbschema.metadata.create_all (bind=engine,
checkfirst=True)
def __del__ (self):
self._session.close()
class DbSchema (object):
metadata table defintions
def __init__ (self):
self.metadata = MetaData()
self.dummy_table = Table('dummy', self.metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(40)),
)
mapper (Dummy, self.dummy_table)
# define tables and connect to both dbs
schema = DbSchema()
conn1 = DbConnection (uri1, schema)
conn2 = DbConnection (uri2, schema)
# create object
x = Dummy()
x.name = u'testname'
# save to one and detach object
conn1._session.save_or_update (x)
conn1._session.flush ()
conn1._session.expunge (x)
# save to two silently fails!
conn2._session.save_or_update (x)
conn2._session.flush ()
conn2._session.expunge (x)
The object saves obediently to the first db, but fails to save to the
second (although tables are created correctly). The successful write
is always the first one, regardless of the order of opening
connections. It doesn't depend on how the primary key is defined:
autogenerated or supplied. I therefore surmise this is because
'save_or_update' detects the object as already being persistent,
despite the use of 'expunge'.
Is there another step required to detach the object, or is another
approach necessary to handle multiple dbs?
--
Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health
[EMAIL PROTECTED] / [EMAIL PROTECTED]
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Same schema, multiple databases, simultaneously
I'm puzzling out how to connect to multiple databases that have the
same tables (and the same table definitions). The use case is that
data may be retrieved from two or more similar dbs and perhaps copied
between them. Following earlier advice, I've used a single metadata
and 'bind' in the session creation to indicate which engine. However
there's a problem. Example code below::
from sqlalchemy import *
from sqlalchemy.orm import *
uri1 = # blah blah
uri2 = # blah blah
class Dummy (object):
simple mapped class
pass
class DbConnection (object):
encapsulate engine session
def __init__ (self, uri, dbschema):
engine = create_engine (uri)
Session = sessionmaker (bind=engine, autoflush=False,
transactional=False)
self._session = Session()
dbschema.metadata.create_all (bind=engine,
checkfirst=True)
def __del__ (self):
self._session.close()
class DbSchema (object):
metadata table defintions
def __init__ (self):
self.metadata = MetaData()
self.dummy_table = Table('dummy', self.metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(40)),
)
mapper (Dummy, self.dummy_table)
# define tables and connect to both dbs
schema = DbSchema()
conn1 = DbConnection (uri1, schema)
conn2 = DbConnection (uri2, schema)
# create object
x = Dummy()
x.name = u'testname'
# save to one and detach object
conn1._session.save_or_update (x)
conn1._session.flush ()
conn1._session.expunge (x)
# save to two silently fails!
conn2._session.save_or_update (x)
conn2._session.flush ()
conn2._session.expunge (x)
The object saves obediently to the first db, but fails to save to the
second (although tables are created correctly). The successful write
is always the first one, regardless of the order of opening
connections. It doesn't depend on how the primary key is defined:
autogenerated or supplied. I therefore surmise this is because
'save_or_update' detects the object as already being persistent,
despite the use of 'expunge'.
Is there another step required to detach the object, or is another
approach necessary to handle multiple dbs?
--
Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health
[EMAIL PROTECTED] / [EMAIL PROTECTED]
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Setting instrument list items
Rick Morrison wrote: I'm having trouble with the instrumented lists used for relation collections: def setProp(self, new_prop, exclusive=True): self.props = [p for p if p.typ != new_prop.typ] self.props.append(new_prop) so if 'props' is a relation on some mapped class, and I try C.setProp(newprop), then a later flush() fails when it tries to write a null FK in the properties table. I suspect it's the list comprehension when there's items in there that haven't been flushed yet. What's the real code doing? That doesn't parse,.. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Setting instrument list items
def setProp(self, new_prop, exclusive=True): if exclusive: self.props = [p for p if p.typ != new_prop.typ] self.props.append(new_prop) o = MappedObject() o.setProp(Prop(typ='a', val='b') o.setProp(Prop(typ='b', val='c') MappedObject has a simple relation() for Prop. at flush() time, the prop for (typ='b') is saved, but the one for (typ='a') throws an error on the database insert saying that table.foreignkey is null On Jan 17, 2008 6:59 PM, jason kirtland [EMAIL PROTECTED] wrote: Rick Morrison wrote: I'm having trouble with the instrumented lists used for relation collections: def setProp(self, new_prop, exclusive=True): self.props = [p for p if p.typ != new_prop.typ] self.props.append(new_prop) so if 'props' is a relation on some mapped class, and I try C.setProp(newprop), then a later flush() fails when it tries to write a null FK in the properties table. I suspect it's the list comprehension when there's items in there that haven't been flushed yet. What's the real code doing? That doesn't parse,.. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Setting instrument list items
Rick Morrison wrote: def setProp(self, new_prop, exclusive=True): if exclusive: self.props = [p for p if p.typ != new_prop.typ] self.props.append(new_prop) o = MappedObject() o.setProp(Prop(typ='a', val='b') o.setProp(Prop(typ='b', val='c') MappedObject has a simple relation() for Prop. at flush() time, the prop for (typ='b') is saved, but the one for (typ='a') throws an error on the database insert saying that table.foreignkey is null You'd need delete-orphan in the cascade for that relation to dispose of all of the instances you're discarding. But it looks like the trunk is still complaining about 'instance ... is an unsaved, pending instance and is an orphan[...]'. I thought that condition had been changed to a warning or a no-op somewhere around 0.4.0, guess it didn't. I don't think the discussion about discarding unpersisted collection orphans made the hop onto the list (IRC only) - anyone have an opinion? I think I'm still in favor of quashing this exception. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Same schema, multiple databases, simultaneously
On Jan 17, 2008, at 6:40 PM, Paul-Michael Agapow wrote:
I'm puzzling out how to connect to multiple databases that have the
same tables (and the same table definitions). The use case is that
data may be retrieved from two or more similar dbs and perhaps copied
between them. Following earlier advice, I've used a single metadata
and 'bind' in the session creation to indicate which engine. However
there's a problem. Example code below::
from sqlalchemy import *
from sqlalchemy.orm import *
uri1 = # blah blah
uri2 = # blah blah
class Dummy (object):
simple mapped class
pass
class DbConnection (object):
encapsulate engine session
def __init__ (self, uri, dbschema):
engine = create_engine (uri)
Session = sessionmaker (bind=engine, autoflush=False,
transactional=False)
self._session = Session()
dbschema.metadata.create_all (bind=engine,
checkfirst=True)
def __del__ (self):
self._session.close()
class DbSchema (object):
metadata table defintions
def __init__ (self):
self.metadata = MetaData()
self.dummy_table = Table('dummy', self.metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(40)),
)
mapper (Dummy, self.dummy_table)
# define tables and connect to both dbs
schema = DbSchema()
conn1 = DbConnection (uri1, schema)
conn2 = DbConnection (uri2, schema)
# create object
x = Dummy()
x.name = u'testname'
# save to one and detach object
conn1._session.save_or_update (x)
conn1._session.flush ()
conn1._session.expunge (x)
# save to two silently fails!
conn2._session.save_or_update (x)
conn2._session.flush ()
conn2._session.expunge (x)
The object saves obediently to the first db, but fails to save to the
second (although tables are created correctly). The successful write
is always the first one, regardless of the order of opening
connections. It doesn't depend on how the primary key is defined:
autogenerated or supplied. I therefore surmise this is because
'save_or_update' detects the object as already being persistent,
despite the use of 'expunge'.
thats correct, an object has an _instance_key attribute which marks
it as persistent.
Is there another step required to detach the object, or is another
approach necessary to handle multiple dbs?
the particular way youd like to handle multiple dbs here is that
youre trying to replicate a full set of operations between two of
them. we dont have a recommended way to approach this, other than
usually replication is handled at the database level, not the python
level, using the replication abilities of the database itself or
external tools designed for this purpose. But Im accustomed to
people rejecting that suggestion when this kind of thing comes up, so
heres some things you can try in python:
you can try putting objects into the session using session.merge(),
which creates a copy of the object in the session, leaving the given
object unchanged, where it could then be merged into a second
session. Another approach would be to manually remove the
_instance_key attribute before saving twice, but this can lead to
complexities when instances with relations to other instances are
manipulated (mostly because we dont currently support that use case
and havent created tests against it). Finally, you can wrap up the
full set of operations (i.e. the full sequence of create object,
populate, save) into a single callable which is called twice, being
passed two different sessions.
Another approach which is technically feasable but is not implemented
right now is to create a proxying engine (or really, just a proxying
Connection would do) which distributes all SQL calls to more than one
engine. If you were willing to write a little more code you could go
this route as well, binding just a single Session to your proxying
Connection which routes all execute calls to two or more embedded,
actual Connection objects. This would provide the most seamless
experience but would require a little more tinkering.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
[sqlalchemy] Re: Setting instrument list items
two clarifications: in my case the relation cascade is 'all, delete-orphan'. But in the case I'm running into, all of the children are unpersisted before the flush. and the exception I get is not a complaint about an orphan, it's a database error when SA tries to persist the (typ='a') item, but does not populate it's parent id. On Jan 17, 2008 7:32 PM, jason kirtland [EMAIL PROTECTED] wrote: Rick Morrison wrote: def setProp(self, new_prop, exclusive=True): if exclusive: self.props = [p for p if p.typ != new_prop.typ] self.props.append(new_prop) o = MappedObject() o.setProp(Prop(typ='a', val='b') o.setProp(Prop(typ='b', val='c') MappedObject has a simple relation() for Prop. at flush() time, the prop for (typ='b') is saved, but the one for (typ='a') throws an error on the database insert saying that table.foreignkey is null You'd need delete-orphan in the cascade for that relation to dispose of all of the instances you're discarding. But it looks like the trunk is still complaining about 'instance ... is an unsaved, pending instance and is an orphan[...]'. I thought that condition had been changed to a warning or a no-op somewhere around 0.4.0, guess it didn't. I don't think the discussion about discarding unpersisted collection orphans made the hop onto the list (IRC only) - anyone have an opinion? I think I'm still in favor of quashing this exception. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Setting instrument list items
On Jan 17, 2008, at 7:32 PM, jason kirtland wrote:
I don't think the discussion about discarding unpersisted collection
orphans made the hop onto the list (IRC only) - anyone have an
opinion?
I think I'm still in favor of quashing this exception.
yeah just tell me what youd like to happen here:
mapper(A, a, properties={
'bs':relation(B, cascade=all, delete-orphan)
})
b = B()
sess.save(b)
sess.flush()
versus here:
a = A()
b = B()
sess.save(a)
a.bs.append(b) # does sess.save(b) due to cascade rule
a.bs.remove(b)
sess.flush()
no error in either case ? or just case #1 ? (or a warning in one/
both cases) ? if only an error in case #1, can you see why I'm
uncomfortable with it ? or do list remove operations now cascade
as expunge operations...and is that a surprise behavior ...
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Setting instrument list items
Rick Morrison wrote: On Jan 17, 2008 7:32 PM, jason kirtland [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Rick Morrison wrote: def setProp(self, new_prop, exclusive=True): if exclusive: self.props = [p for p if p.typ != new_prop.typ] self.props.append(new_prop) o = MappedObject() o.setProp(Prop(typ='a', val='b') o.setProp(Prop(typ='b', val='c') MappedObject has a simple relation() for Prop. at flush() time, the prop for (typ='b') is saved, but the one for (typ='a') throws an error on the database insert saying that table.foreignkey is null You'd need delete-orphan in the cascade for that relation to dispose of all of the instances you're discarding. But it looks like the trunk is still complaining about 'instance ... is an unsaved, pending instance and is an orphan[...]'. I thought that condition had been changed to a warning or a no-op somewhere around 0.4.0, guess it didn't. I don't think the discussion about discarding unpersisted collection orphans made the hop onto the list (IRC only) - anyone have an opinion? I think I'm still in favor of quashing this exception. two clarifications: in my case the relation cascade is 'all, delete-orphan'. But in the case I'm running into, all of the children are unpersisted before the flush. and the exception I get is not a complaint about an orphan, it's a database error when SA tries to persist the (typ='a') item, but does not populate it's parent id. I think there must be something more going on here. Assuming that the truncated list comprehension is supposed to read: [p for p in self.props if ...], and delete-orphan is on, then nothing gets nulled when I run the code. Do Props have more than one non-nullable parent relation? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Setting instrument list items
On Jan 17, 2008, at 7:36 PM, Rick Morrison wrote: two clarifications: in my case the relation cascade is 'all, delete- orphan'. But in the case I'm running into, all of the children are unpersisted before the flush. and the exception I get is not a complaint about an orphan, it's a database error when SA tries to persist the (typ='a') item, but does not populate it's parent id. do your setProp() operation, then do this: (added, unchanged, deleted) = MyClass.props.get_history(myobject) to see whats actually in there. it should just be a collection of added objects (and also, the correct objects). Then, use echo_uow=True on your session to see the flush plan. everything in the flush plan should also be in an added collection somewhere. also look inside of session.new, see if all the correct stuff is in there too. Objects which get removed from the collection due to your operation should be raising an orphan error right now, since theyre in the session but have no parentseems like thats what your FK error is but its unusual that the orphan detection wouldn't be working. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Setting instrument list items
On Jan 17, 2008, at 7:51 PM, Rick Morrison wrote: An expunge on an collection remove that creates an orphan wouldn't be a surprise to me; I'm surprised that it doesn't do that now. +1 for error on case 1 plus auto-remove in case 2 would the expunge happen at attribute change time or at flush time? does that expunge cascade through the expunged object too ? what if the expunged object references things that are persistent ? do we expunge them still ? what if the expunged object references other pending objects, but they are valid, and might even be referenced by other, valid pending/ persistent objects in the session ? do we expunge those ? seems like a ton of guessworkwhich is why i prefer the developer to have a clue whats in and whats not in his or her session before flushing.. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Setting instrument list items
On Jan 17, 2008, at 8:03 PM, Michael Bayer wrote: On Jan 17, 2008, at 7:51 PM, Rick Morrison wrote: An expunge on an collection remove that creates an orphan wouldn't be a surprise to me; I'm surprised that it doesn't do that now. +1 for error on case 1 plus auto-remove in case 2 would the expunge happen at attribute change time or at flush time? does that expunge cascade through the expunged object too ? what if the expunged object references things that are persistent ? do we expunge them still ? what if the expunged object references other pending objects, but they are valid, and might even be referenced by other, valid pending/ persistent objects in the session ? do we expunge those ? seems like a ton of guessworkwhich is why i prefer the developer to have a clue whats in and whats not in his or her session before flushing.. this is based on the fact that expunge has its own cascade path. if we say, expunge along the delete-orphan path, thats something different so maybe that would work, still seems a little creepy to me. I think the expunge has to happen as an on-attribute event though, otherwise we'd have to track that action somewhere. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Setting instrument list items
do your setProp() operation, then do this: (added, unchanged, deleted) = MyClass.props.get_history(myobject) to see whats actually in there. it should just be a collection of added objects (and also, the correct objects). Yep, so far so good. Two Prop items in 'added' Then, use echo_uow=True on your session to see the flush plan. everything in the flush plan should also be in an added collection somewhere. also look inside of session.new, see if all the correct stuff is in there too. session.new contents looks ok, with both Prop items and some other objects The UOW is large and complex (attached, the Prop items are named Invoiceattr), but I do see the Prop items in there, two of them. and.. same (database) error on flush Objects which get removed from the collection due to your operation should be raising an orphan error right now, since theyre in the session but have no parentseems like thats what your FK error is but its unusual that the orphan detection wouldn't be working. OK, I get the basic idea; expunge isn't automatic. Let me rework the setProp to get things running again here, and then I'll pick up the discussion about that. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- r Description: Binary data
[sqlalchemy] Re: Setting instrument list items
OK, I get the basic idea; expunge isn't automatic. Let me rework the setProp to get things running again here, and then I'll pick up the discussion about that. For the archives: This rework sidesteps the issue: def setProp(self, prop, exclusive=True): if exclusive: curprops = [p for p in self.props] for p in curprops: if p.typ == prop.typ: session_object(p).expunge(p) self.props.remove(p) self.props.append(prop) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Relating objects of the same type Using Joined Table Inheritance
I'm new to SQLAlchemy (but not python ORMs *cough*SQLObject*cough*)
and I really like the idea of Joined Table Inheritance, and the
example in the Documentation makes it look really elegant
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_inheritance_joined
My question is what if you wanted to add a 'hired' field to say the
employees table object (from the example in that link) that
references a manager object. In this way I imagine that we are making
another reference to an employee object and might be an issue when
trying to figure out the join. So I'd imagine that the tables look
like:
employees = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('name', String(50)),
Column('type', String(30), nullable=False)
)
engineers = Table('engineers', metadata,
Column('employee_id', Integer, ForeignKey('employees.employee_id'),
primary_key=True),
Column('hired_by_id',Integer,ForeignKey('managers.employee_id')),
###INTERESTING PART
Column('engineer_info', String(50)),
)
managers = Table('managers', metadata,
Column('employee_id', Integer, ForeignKey('employees.employee_id'),
primary_key=True),
Column('manager_data', String(50)),
)
and the mappers look like:
mapper(Employee, employees, polymorphic_on=employees.c.type,
polymorphic_identity='employee')
mapper(Engineer, engineers, inherits=Employee,
polymorphic_identity='engineer',
properties={
'hirer':relation(Manager,uselist=False,backref='hired')
})
mapper(Manager, managers, inherits=Employee,
polymorphic_identity='manager')
But the error message you'd get if you do this is:
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'Engineer.hirer (Manager)'. If the
underlying error cannot be corrected, you should specify the
'primaryjoin' (and 'secondaryjoin', if there is an association table
present) keyword arguments to the relation() function (or for
backrefs, by specifying the backref using the backref() function with
keyword arguments) to explicitly specify the join conditions. Nested
error is Can't determine join between 'Join object on
employees(14680464) and engineers(14680880)' and '_FromGrouping
object'; tables have more than one foreign key constraint relationship
between them. Please specify the 'onclause' of this join explicitly.
Might I add that this is an extremely informative error message!
Basically, my question is how do I satisfy this requirement as
described by the error message? I have to make my join more
specific? How would I do that?
And another question, is Joined Table Inheritance a common thing to do
in SQLAlchemy?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
