[sqlalchemy] Differentiate ANSIIdentifierPreparer.format_column select clause from where clause??
Is there a way in the format_column method of ANSIIdentifierPreparer
to determine if the column is part of the select clause, where clause,
order clause, etc?
What I'm attempting to do is override the default postgres dialect to
format string columns as 'lower(colname)' only when the column is not
being used as part of the select clause.
Also, if interested, I can submit a patch to url.py that imports the
dialect from the full module path if the dialect is not found. This
would allow someone to override a dialect, or create a proprietary new
one and not have to worry about the dialect residing in alchemy's
databases module, i.e. :
pg_db = create_engine('myapp.database.postgres://scott:[EMAIL PROTECTED]:
5432/mydatabase')
Thanks,
Troy
--~--~-~--~~~---~--~~
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] SA skips integrity referential?
Hi all,
I wonder how SA could delete a row of my table (postgresql) linked with
another table.
Take a look...
pg= select * from attivita where cod_specie='33';
codice | descrizione | cod_specie
+-+
21311 | Sezionamento selvaggina allevata | 33
pg= select * from specie where codice='33';
codice | descrizione
---+-
33 | Selvaggina
(1 row)
sfera= delete from specie where codice='33';
ERROR: update or delete on specie violates foreign key constraint
attivita_cod_specie_fkey on attivita
DETAIL: Key (codice)=(33) is still referenced from table attivita.
--
let's try now using SA:
tg-admin shell
In [1] aa=Specie.get_by(codice='33')
In [3]: aa.delete
Out[3]: bound method Specie.do of Specie 33
In [4]: aa.delete()
In [5]: aa.flush()
2007-02-16 15:30:31,955 sqlalchemy.engine.base.Engine.0x..f4 INFO BEGIN
2007-02-16 15:30:31,958 sqlalchemy.engine.base.Engine.0x..f4 INFO SELECT
attivita.cod_specie AS attivita_cod_specie, attivita.attivo AS
attivita_attivo, attivita.cod_attivita_istat AS
attivita_cod_attivita_istat, attivita.descrizione AS
attivita_descrizione, attivita.cod_prodotto AS attivita_cod_prodotto,
attivita.cod_tipologia_struttura AS attivita_cod_tipologia_s_2e27,
attivita.cod_organizzazione AS attivita_cod_organizzazione,
attivita.cod_orientamento_produttivo AS attivita_cod_orientament_583a,
attivita.codice AS attivita_codice
FROM attivita
WHERE %(lazy_b4ba)s = attivita.cod_specie ORDER BY attivita.codice
2007-02-16 15:30:31,958 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'lazy_b4ba': '33'}
2007-02-16 15:30:31,979 sqlalchemy.engine.base.Engine.0x..f4 INFO UPDATE
attivita SET cod_specie=%(cod_specie)s WHERE attivita.codice =
%(attivita_codice)s
2007-02-16 15:30:31,980 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'cod_specie': None, 'attivita_codice': '01302'}
2007-02-16 15:30:31,986 sqlalchemy.engine.base.Engine.0x..f4 INFO DELETE
FROM specie WHERE specie.codice = %(codice)s
2007-02-16 15:30:31,986 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'codice': '33'}
2007-02-16 15:30:31,990 sqlalchemy.engine.base.Engine.0x..f4 INFO COMMIT
pg= select * from specie where codice='33';
codice | descrizione
---+-
pg= select * from specie where codice='33';
codice | descrizione
---+-
(0 row)
--~--~-~--~~~---~--~~
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] postgres, autoload, and odd datatypes
I found the discussion last month regarding the lack of support for specialised datatypes in the postgres reflection code. I have a lot of odd datatypes in my schemas... besides inet, there's postgis datatypes and tsearch2's tsvector, etc. However, most of these odd fields are never manipulated directly at the python level (they tend to be updated internally via triggers); and the ones that are touched (like 'inet' for example) work fine as text fields. Postgresql does the coersion. Thus, my quick little hack to make reflection work without having to tediously override the table columns, or create new datatypes, is simply to make all unknown datatypes be 'text' datatypes: --- postgres.py.origFri Feb 16 09:52:59 2007 +++ postgres.py Fri Feb 16 09:55:26 2007 @@ -392,7 +392,10 @@ elif attype == 'timestamp without time zone': kwargs['timezone'] = False -coltype = ischema_names[attype] +if ischema_names.has_key(attype): +coltype = ischema_names[attype] +else: +coltype = ischema_names['text'] coltype = coltype(*args, **kwargs) colargs= [] if default is not None: I realise this is a bit dodgy, but it has simplified things for me greatly. I'm wondering if something like this might not be a useful concept for an some sort of autoload option... rather than crashing with a KeyError. Either a default datatype to substitute, or even just ignoring fields of unknown datatypes. (Also it would be very helpful, especially when autoloading a lot of foreign key tables, if that KeyError exception was caught and returned a more meaningful error message... like what table and field name is the problem...) --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
Referential integrity isn't being violated here - SA is nulling the foreign
key before deleting the row it points to. Try adding nullable=False to the
declaration of attivita.cod_specie. That should make it fail in the way you
expect, because SA will no longer be able to null the foreign key.
On 2/16/07, Jose Soares [EMAIL PROTECTED] wrote:
Hi all,
I wonder how SA could delete a row of my table (postgresql) linked with
another table.
Take a look...
pg= select * from attivita where cod_specie='33';
codice | descrizione | cod_specie
+-+
21311 | Sezionamento selvaggina allevata | 33
pg= select * from specie where codice='33';
codice | descrizione
---+-
33 | Selvaggina
(1 row)
sfera= delete from specie where codice='33';
ERROR: update or delete on specie violates foreign key constraint
attivita_cod_specie_fkey on attivita
DETAIL: Key (codice)=(33) is still referenced from table attivita.
--
let's try now using SA:
tg-admin shell
In [1] aa=Specie.get_by(codice='33')
In [3]: aa.delete
Out[3]: bound method Specie.do of Specie 33
In [4]: aa.delete()
In [5]: aa.flush()
2007-02-16 15:30:31,955 sqlalchemy.engine.base.Engine.0x..f4 INFO BEGIN
2007-02-16 15:30:31,958 sqlalchemy.engine.base.Engine.0x..f4 INFO SELECT
attivita.cod_specie AS attivita_cod_specie, attivita.attivo AS
attivita_attivo, attivita.cod_attivita_istat AS
attivita_cod_attivita_istat, attivita.descrizione AS
attivita_descrizione, attivita.cod_prodotto AS attivita_cod_prodotto,
attivita.cod_tipologia_struttura AS attivita_cod_tipologia_s_2e27,
attivita.cod_organizzazione AS attivita_cod_organizzazione,
attivita.cod_orientamento_produttivo AS attivita_cod_orientament_583a,
attivita.codice AS attivita_codice
FROM attivita
WHERE %(lazy_b4ba)s = attivita.cod_specie ORDER BY attivita.codice
2007-02-16 15:30:31,958 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'lazy_b4ba': '33'}
2007-02-16 15:30:31,979 sqlalchemy.engine.base.Engine.0x..f4 INFO UPDATE
attivita SET cod_specie=%(cod_specie)s WHERE attivita.codice =
%(attivita_codice)s
2007-02-16 15:30:31,980 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'cod_specie': None, 'attivita_codice': '01302'}
2007-02-16 15:30:31,986 sqlalchemy.engine.base.Engine.0x..f4 INFO DELETE
FROM specie WHERE specie.codice = %(codice)s
2007-02-16 15:30:31,986 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'codice': '33'}
2007-02-16 15:30:31,990 sqlalchemy.engine.base.Engine.0x..f4 INFO COMMIT
pg= select * from specie where codice='33';
codice | descrizione
---+-
pg= select * from specie where codice='33';
codice | descrizione
---+-
(0 row)
--~--~-~--~~~---~--~~
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] eager loading and nested property
Hi. I have a mapper A, with some property eager loaded. When I query A, I can use the options method to remove some property from being loaded (with noload). I have now a mapper B, with a property of type A eager loaded. When I query B, A, and all its property are eager loaded and it seems that I can not use noload here. Can options be used with B to control property from A? Thanks Manlio Perillo --~--~-~--~~~---~--~~ 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: Reflection including Relations
Foreign key relations *are* reflected. That information is used when you set-up 'properties'. Notice that you don't have to specify any keys when you define properties. SA just doesn't do the 'properties' automatically (by design I think). Steve On Feb 16, 12:14 pm, Andreas Jung [EMAIL PROTECTED] wrote: --On 16. Februar 2007 17:09:12 +0100 Andreas Jung [EMAIL PROTECTED] wrote: SA provide reflection support through Table(...autoload=True). Is there some extension for SA to include reflection support for relations? Otherwise you have specify the relationships using the 'properties' on your own. Sorry for this weird posting. Of course the mapped class should obtain the additional properties as defined manually through 'properties' automatically from the knowledge of the relations of the table instance. -aj application_pgp-signature_part 1KDownload --~--~-~--~~~---~--~~ 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] Function preface
I want to write a function for each of the databases our product supports and call them in the same way for all databases. This works with most databases, but MS-SQL Server does something different and forces the function to be called with an owner specifier, so what would be simply myfunc() in most DB engines must be instead dbo.myfunc() In MSSQL. Is there any way to tell the the SA func function to use such a prefix for MSSQL and not for other database engines? Note that this is for user-defined functions only, MSSQL built-in functions can be called without the prefix. Thanks, Rick --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
Gary Bernhardt wrote:
Referential integrity isn't being violated here - SA is nulling the
foreign key before deleting the row it points to. Try adding
nullable=False to the declaration of attivita.cod_specie. That should
make it fail in the way you expect, because SA will no longer be able
to null the foreign key.
This seems to me a trick to avoid integrity referential.
I expected the nullable=False was the default behavior for any foreign
key otherwise the referential integrity is violated here?
I'm very, very surprised for this behavior.
Anyway, I'm using autoload to define my tables thus I don't know how to
add nullable=False to my tables.
jo
On 2/16/07, *Jose Soares* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi all,
I wonder how SA could delete a row of my table (postgresql) linked
with
another table.
Take a look...
pg= select * from attivita where cod_specie='33';
codice | descrizione |
cod_specie
+-+
21311 | Sezionamento selvaggina allevata | 33
pg= select * from specie where codice='33';
codice | descrizione
---+-
33 | Selvaggina
(1 row)
sfera= delete from specie where codice='33';
ERROR: update or delete on specie violates foreign key constraint
attivita_cod_specie_fkey on attivita
DETAIL: Key (codice)=(33) is still referenced from table attivita.
--
let's try now using SA:
tg-admin shell
In [1] aa=Specie.get_by(codice='33')
In [3]: aa.delete
Out[3]: bound method Specie.do of Specie 33
In [4]: aa.delete()
In [5]: aa.flush()
2007-02-16 15:30:31,955 sqlalchemy.engine.base.Engine.0x..f4 INFO
BEGIN
2007-02-16 15:30:31,958 sqlalchemy.engine.base.Engine.0x..f4 INFO
SELECT
attivita.cod_specie AS attivita_cod_specie, attivita.attivo AS
attivita_attivo, attivita.cod_attivita_istat AS
attivita_cod_attivita_istat, attivita.descrizione AS
attivita_descrizione, attivita.cod_prodotto AS attivita_cod_prodotto,
attivita.cod_tipologia_struttura AS attivita_cod_tipologia_s_2e27,
attivita.cod_organizzazione AS attivita_cod_organizzazione,
attivita.cod_orientamento_produttivo AS attivita_cod_orientament_583a,
attivita.codice AS attivita_codice
FROM attivita
WHERE %(lazy_b4ba)s = attivita.cod_specie ORDER BY attivita.codice
2007-02-16 15:30:31,958 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'lazy_b4ba': '33'}
2007-02-16 15:30:31,979 sqlalchemy.engine.base.Engine.0x..f4 INFO
UPDATE
attivita SET cod_specie=%(cod_specie)s WHERE attivita.codice =
%(attivita_codice)s
2007-02-16 15:30:31,980 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'cod_specie': None, 'attivita_codice': '01302'}
2007-02-16 15:30:31,986 sqlalchemy.engine.base.Engine.0x..f4 INFO
DELETE
FROM specie WHERE specie.codice = %(codice)s
2007-02-16 15:30:31,986 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'codice': '33'}
2007-02-16 15:30:31,990 sqlalchemy.engine.base.Engine.0x..f4 INFO
COMMIT
pg= select * from specie where codice='33';
codice | descrizione
---+-
pg= select * from specie where codice='33';
codice | descrizione
---+-
(0 row)
--~--~-~--~~~---~--~~
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: SA skips integrity referential?
On 2/16/07, jose [EMAIL PROTECTED] wrote: Gary Bernhardt wrote: Referential integrity isn't being violated here - SA is nulling the foreign key before deleting the row it points to. Try adding nullable=False to the declaration of attivita.cod_specie. That should make it fail in the way you expect, because SA will no longer be able to null the foreign key. This seems to me a trick to avoid integrity referential. I expected the nullable=False was the default behavior for any foreign key otherwise the referential integrity is violated here? I'm very, very surprised for this behavior. Guess it would surprise you to learn about the SQL 92 ON DELETE SET NULL functionality too. :) --~--~-~--~~~---~--~~ 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: postgres, autoload, and odd datatypes
I'd prefer to not have them loaded at all (maybe with log.warning) than to have them loaded with a known-to-be-incorrect type. If you really don't want to manipulate them from Python, not loading them is the Right Thing. If you do want to manipulate them then the Right Thing is to add the necessary datatypes. On 2/16/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I found the discussion last month regarding the lack of support for specialised datatypes in the postgres reflection code. I have a lot of odd datatypes in my schemas... besides inet, there's postgis datatypes and tsearch2's tsvector, etc. However, most of these odd fields are never manipulated directly at the python level (they tend to be updated internally via triggers); and the ones that are touched (like 'inet' for example) work fine as text fields. Postgresql does the coersion. Thus, my quick little hack to make reflection work without having to tediously override the table columns, or create new datatypes, is simply to make all unknown datatypes be 'text' datatypes: --- postgres.py.origFri Feb 16 09:52:59 2007 +++ postgres.py Fri Feb 16 09:55:26 2007 @@ -392,7 +392,10 @@ elif attype == 'timestamp without time zone': kwargs['timezone'] = False -coltype = ischema_names[attype] +if ischema_names.has_key(attype): +coltype = ischema_names[attype] +else: +coltype = ischema_names['text'] coltype = coltype(*args, **kwargs) colargs= [] if default is not None: I realise this is a bit dodgy, but it has simplified things for me greatly. I'm wondering if something like this might not be a useful concept for an some sort of autoload option... rather than crashing with a KeyError. Either a default datatype to substitute, or even just ignoring fields of unknown datatypes. (Also it would be very helpful, especially when autoloading a lot of foreign key tables, if that KeyError exception was caught and returned a more meaningful error message... like what table and field name is the problem...) --~--~-~--~~~---~--~~ 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: Reflection including Relations
You say defined manually... automatically. It can't be both. :) SA won't try to guess what properties you want on your mapped classes, because it could guess wrong. (Believe me, whatever pattern you are thinking of, someone could find an exception where automatically setting it up is not the right thing to do.) Explicit is better than implicit... On 2/16/07, Andreas Jung [EMAIL PROTECTED] wrote: --On 16. Februar 2007 17:09:12 +0100 Andreas Jung [EMAIL PROTECTED] wrote: SA provide reflection support through Table(...autoload=True). Is there some extension for SA to include reflection support for relations? Otherwise you have specify the relationships using the 'properties' on your own. Sorry for this weird posting. Of course the mapped class should obtain the additional properties as defined manually through 'properties' automatically from the knowledge of the relations of the table instance. -aj --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
Jonathan Ellis wrote: On 2/16/07, jose [EMAIL PROTECTED] wrote: Gary Bernhardt wrote: Referential integrity isn't being violated here - SA is nulling the foreign key before deleting the row it points to. Try adding nullable=False to the declaration of attivita.cod_specie. That should make it fail in the way you expect, because SA will no longer be able to null the foreign key. This seems to me a trick to avoid integrity referential. I expected the nullable=False was the default behavior for any foreign key otherwise the referential integrity is violated here? I'm very, very surprised for this behavior. Guess it would surprise you to learn about the SQL 92 ON DELETE SET NULL functionality too. :) Seems to me the SQL92 'ON DELETE SET NULL' is an explicit functionality, instead in our case, SA does this functionality in implicit way. :-( I hope there's a sort of configuration to disable this functionality. jo --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
On 2/16/07, jose [EMAIL PROTECTED] wrote: Jonathan Ellis wrote: Guess it would surprise you to learn about the SQL 92 ON DELETE SET NULL functionality too. :) Seems to me the SQL92 'ON DELETE SET NULL' is an explicit functionality, instead in our case, SA does this functionality in implicit way. :-( I hope there's a sort of configuration to disable this functionality. Sure, set a NOT NULL constraint on the column you don't want to be null. Which you should really be doing anyway. :) --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
Jonathan Ellis wrote: On 2/16/07, jose [EMAIL PROTECTED] wrote: Jonathan Ellis wrote: Guess it would surprise you to learn about the SQL 92 ON DELETE SET NULL functionality too. :) Seems to me the SQL92 'ON DELETE SET NULL' is an explicit functionality, instead in our case, SA does this functionality in implicit way. :-( I hope there's a sort of configuration to disable this functionality. Sure, set a NOT NULL constraint on the column you don't want to be null. Which you should really be doing anyway. :) No Jonathan, I don't want this column is set as NOT NULL, I have to allow null values for this column and I don't want enable the ON DELETE SET NULL functionality. I would like SA have the same behavior as PostgreSQL has, I like the message: pg delete from specie where codice='89'; ERROR: update or delete on specie violates foreign key constraint attivita_cod_specie_fkey on attivita DETAIL: Key (codice)=(89) is still referenced from table attivita. Is there another way to do that? jo --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
If you want to do things the hard way, you can do this by turning off cascade on the relationship (cascade='none'). But you can't have your cake and eat it too, you'll have to manually handle adding new subordinate objects to the session when saving, etc. On 2/16/07, jose [EMAIL PROTECTED] wrote: Jonathan Ellis wrote: On 2/16/07, jose [EMAIL PROTECTED] wrote: Jonathan Ellis wrote: Guess it would surprise you to learn about the SQL 92 ON DELETE SET NULL functionality too. :) Seems to me the SQL92 'ON DELETE SET NULL' is an explicit functionality, instead in our case, SA does this functionality in implicit way. :-( I hope there's a sort of configuration to disable this functionality. Sure, set a NOT NULL constraint on the column you don't want to be null. Which you should really be doing anyway. :) No Jonathan, I don't want this column is set as NOT NULL, I have to allow null values for this column and I don't want enable the ON DELETE SET NULL functionality. I would like SA have the same behavior as PostgreSQL has, I like the message: pg delete from specie where codice='89'; ERROR: update or delete on specie violates foreign key constraint attivita_cod_specie_fkey on attivita DETAIL: Key (codice)=(89) is still referenced from table attivita. Is there another way to do that? jo --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
jose wrote: Jonathan Ellis wrote: On 2/16/07, jose [EMAIL PROTECTED] wrote: Jonathan Ellis wrote: Guess it would surprise you to learn about the SQL 92 ON DELETE SET NULL functionality too. :) Seems to me the SQL92 'ON DELETE SET NULL' is an explicit functionality, instead in our case, SA does this functionality in implicit way. :-( I hope there's a sort of configuration to disable this functionality. Sure, set a NOT NULL constraint on the column you don't want to be null. Which you should really be doing anyway. :) No Jonathan, I don't want this column is set as NOT NULL, I have to allow null values for this column and I don't want enable the ON DELETE SET NULL functionality. I would like SA have the same behavior as PostgreSQL has, I like the message: pg delete from specie where codice='89'; ERROR: update or delete on specie violates foreign key constraint attivita_cod_specie_fkey on attivita DETAIL: Key (codice)=(89) is still referenced from table attivita. Is there another way to do that? jo I think I found the way to avoid ON DELETE SET NULL... if I delete the backref for 'specie' it works. :-) assign_mapper(context, Attivita, tbl['attivita'], properties = dict( tipologia_struttura = relation(TipologiaStruttura, backref='attivita'), organizzazione = relation(Organizzazione, backref='attivita'), prodotto= relation(Prodotto, backref='attivita'), orientamento_produttivo = relation(OrientamentoProduttivo, backref='attivita'), specie = relation(Specie, backref='attivita'), attivita_istat = relation(AttivitaIstat, backref='attivita'), )) --~--~-~--~~~---~--~~ 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: Differentiate ANSIIdentifierPreparer.format_column select clause from where clause??
On Feb 16, 2007, at 3:18 AM, Troy wrote:
Is there a way in the format_column method of ANSIIdentifierPreparer
to determine if the column is part of the select clause, where clause,
order clause, etc?
What I'm attempting to do is override the default postgres dialect to
format string columns as 'lower(colname)' only when the column is not
being used as part of the select clause.
i suppose this is not terribly hard since it would just mean some
extra argument passed along. of course the question is *why* would
you need this. if its some kind of case-insensitive resultproxy
issue, id rather do something on that end.
Also, if interested, I can submit a patch to url.py that imports the
dialect from the full module path if the dialect is not found. This
would allow someone to override a dialect, or create a proprietary new
one and not have to worry about the dialect residing in alchemy's
databases module, i.e. :
pg_db = create_engine('myapp.database.postgres://
scott:[EMAIL PROTECTED]:
5432/mydatabase')
so the rule would be, look for an SA dialect first, else try to
import module ?would we want to do this in a more indirect way,
i.e. register_dialect(postgres, mydialect) so that one can
install custom dialects across the board for a given type ?
--~--~-~--~~~---~--~~
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: SA skips integrity referential?
On Feb 16, 2007, at 3:46 PM, jose wrote: No Jonathan, I don't want this column is set as NOT NULL, I have to allow null values for this column and I don't want enable the ON DELETE SET NULL functionality. I would like SA have the same behavior as PostgreSQL has, I like the message: pg delete from specie where codice='89'; ERROR: update or delete on specie violates foreign key constraint attivita_cod_specie_fkey on attivita DETAIL: Key (codice)=(89) is still referenced from table attivita. Is there another way to do that? basically, if you have A-B, and you delete A without any kind of delete cascade to B, you are basically telling SA, delete A from the database and keep B. SA appropriately is smart enough to know that it should break the relationship from A to B before deleting A. if that operation violates an integrity constraint in your application then youd have a NOT NULL constraint sitting on B's foreign key to A. That you say you dont want the NOT NULL on the column is a little strange...I dont exactly understand an occasional not-null constraint on a foreign key column. you might find setting delete-orphan cascade on the relationship might raise an error for B sitting in the session by itself, though. but then you cant save any Bs by themselves. --~--~-~--~~~---~--~~ 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: Function preface
id want the MS-SQL dialect to add the dbo in its own visit_function () code when it generates the SQL. this would require the dialect knows an MS-SQL builtin from a user-defined. the reason we dont do it in the func itself is because the SQL constructs themselves remain database-agnostic at all times (so that SQL constructs and mappers can work for a DynamicMetaData that touches different kinds of DBs, for example). On Feb 16, 2007, at 1:21 PM, Rick Morrison wrote: I want to write a function for each of the databases our product supports and call them in the same way for all databases. This works with most databases, but MS-SQL Server does something different and forces the function to be called with an owner specifier, so what would be simply myfunc() in most DB engines must be instead dbo.myfunc() In MSSQL. Is there any way to tell the the SA func function to use such a prefix for MSSQL and not for other database engines? Note that this is for user-defined functions only, MSSQL built-in functions can be called without the prefix. Thanks, Rick --~--~-~--~~~---~--~~ 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: eager loading and nested property
On Feb 16, 2007, at 12:19 PM, Manlio Perillo wrote: Hi. I have a mapper A, with some property eager loaded. When I query A, I can use the options method to remove some property from being loaded (with noload). I have now a mapper B, with a property of type A eager loaded. When I query B, A, and all its property are eager loaded and it seems that I can not use noload here. Can options be used with B to control property from A? sure canif its not working send along an example --~--~-~--~~~---~--~~ 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: Bug with unicode database paths on win32
uh yeah definitely...create_engine takes the str or unicode so the BoundMetaData should as well...rev 2325. On Feb 16, 2007, at 12:41 PM, Evandro Miquelito wrote: Hello! First of all I would like to congratulate SQLAlchemy developers for this amazing toolkit. I have been worked with SQLObject until I realized how well organized, full featured and well documented SQLAlchemy is. After that, making the decision about switching to it was easy. As the subject of this message says, I'm having an issue with unicode database paths on win32. After playing a litle bit with the code I got the following and kinda trivial patch. I hope it helps. Regards. -- Evandro Vale Miquelito [EMAIL PROTECTED] Index: lib/sqlalchemy/schema.py === --- lib/sqlalchemy/schema.py (revision 2324) +++ lib/sqlalchemy/schema.py (working copy) @@ -914,7 +914,7 @@ builds upon MetaData to provide the capability to bind to an Engine implementation. def __init__(self, engine_or_url, name=None, **kwargs): super(BoundMetaData, self).__init__(name, **kwargs) -if isinstance(engine_or_url, str): +if isinstance(engine_or_url, basestring): self._engine = sqlalchemy.create_engine(engine_or_url, **kwargs) else: self._engine = engine_or_url --~--~-~--~~~---~--~~ 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: postgres, autoload, and odd datatypes
OK, i propose we make the KeyError more friendly, and add an option skip_unknown_types to Table to indicate just to skip the columns. if we really want the default fallback type, id prefer an option for that too, like default_type=sometype and it will shove that type in for the unknown types. Additionally we have to implement this for all the dialects, not just PG. if you guys want to work up a ticket/patch for this that would be great (im falling behind on tickets). On Feb 16, 2007, at 2:55 PM, Jonathan Ellis wrote: I'd prefer to not have them loaded at all (maybe with log.warning) than to have them loaded with a known-to-be-incorrect type. If you really don't want to manipulate them from Python, not loading them is the Right Thing. If you do want to manipulate them then the Right Thing is to add the necessary datatypes. On 2/16/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I found the discussion last month regarding the lack of support for specialised datatypes in the postgres reflection code. I have a lot of odd datatypes in my schemas... besides inet, there's postgis datatypes and tsearch2's tsvector, etc. However, most of these odd fields are never manipulated directly at the python level (they tend to be updated internally via triggers); and the ones that are touched (like 'inet' for example) work fine as text fields. Postgresql does the coersion. Thus, my quick little hack to make reflection work without having to tediously override the table columns, or create new datatypes, is simply to make all unknown datatypes be 'text' datatypes: --- postgres.py.origFri Feb 16 09:52:59 2007 +++ postgres.py Fri Feb 16 09:55:26 2007 @@ -392,7 +392,10 @@ elif attype == 'timestamp without time zone': kwargs['timezone'] = False -coltype = ischema_names[attype] +if ischema_names.has_key(attype): +coltype = ischema_names[attype] +else: +coltype = ischema_names['text'] coltype = coltype(*args, **kwargs) colargs= [] if default is not None: I realise this is a bit dodgy, but it has simplified things for me greatly. I'm wondering if something like this might not be a useful concept for an some sort of autoload option... rather than crashing with a KeyError. Either a default datatype to substitute, or even just ignoring fields of unknown datatypes. (Also it would be very helpful, especially when autoloading a lot of foreign key tables, if that KeyError exception was caught and returned a more meaningful error message... like what table and field name is the problem...) --~--~-~--~~~---~--~~ 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: Function preface
Thinking about this a bit more, it seems to me that this may be a useful construct in its own right. Take the example where a user want to call a function that was declared in a different schema than the base tables that it is going to operate on. This is sometimes the case when there is a library of database functions that is installed it its own private schema. /me goes and tries some things... ...well it looks as if a schema qualifier already works for func, so that func.myschema.myfunc(parms) works just like you would expect, and calls myfunc in the myschema schema. So a viable plan B is to create that private function schema and just put everything there. Once again, SA rocks! On 2/16/07, Michael Bayer [EMAIL PROTECTED] wrote: id want the MS-SQL dialect to add the dbo in its own visit_function () code when it generates the SQL. this would require the dialect knows an MS-SQL builtin from a user-defined. the reason we dont do it in the func itself is because the SQL constructs themselves remain database-agnostic at all times (so that SQL constructs and mappers can work for a DynamicMetaData that touches different kinds of DBs, for example). On Feb 16, 2007, at 1:21 PM, Rick Morrison wrote: I want to write a function for each of the databases our product supports and call them in the same way for all databases. This works with most databases, but MS-SQL Server does something different and forces the function to be called with an owner specifier, so what would be simply myfunc() in most DB engines must be instead dbo.myfunc() In MSSQL. Is there any way to tell the the SA func function to use such a prefix for MSSQL and not for other database engines? Note that this is for user-defined functions only, MSSQL built-in functions can be called without the prefix. Thanks, Rick --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
Michael Bayer wrote: On Feb 16, 2007, at 3:46 PM, jose wrote: No Jonathan, I don't want this column is set as NOT NULL, I have to allow null values for this column and I don't want enable the ON DELETE SET NULL functionality. I would like SA have the same behavior as PostgreSQL has, I like the message: pg delete from specie where codice='89'; ERROR: update or delete on specie violates foreign key constraint attivita_cod_specie_fkey on attivita DETAIL: Key (codice)=(89) is still referenced from table attivita. Is there another way to do that? basically, if you have A-B, and you delete A without any kind of delete cascade to B, you are basically telling SA, delete A from the database and keep B. SA appropriately is smart enough to know that it should break the relationship from A to B before deleting A. if that operation violates an integrity constraint in your application then youd have a NOT NULL constraint sitting on B's foreign key to A. That you say you dont want the NOT NULL on the column is a little strange...I dont exactly understand an occasional not-null constraint on a foreign key column. In a perfect world, we will not need NULLs, but for the moment it is better to be realistic, we don't have an answer for any question and sometimes we have to respond I DON'T KNOW and this transalted to SQL as NULL. This is why we have LEFT/RIGTH JOINs in SQL. In my case I need a foreign key that sometimes haven't a value. Anyway, I think I need to wonderstand better how backrefs works, seems to me that SA activate the ON DELETE SET NULL when there's a backref in the relation. My definition was: assign_mapper(context, Attivita, tbl['attivita'], properties = dict(specie = relation(Specie, backref='attivita')) now it is: assign_mapper(context, Attivita, tbl['attivita'], properties = dict(specie = relation(Specie)) and it works as I want. :-) jo you might find setting delete-orphan cascade on the relationship might raise an error for B sitting in the session by itself, though. but then you cant save any Bs by themselves. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
