[sqlalchemy] PostgreSQL - fetching boolean type gives integer type
In PostgreSQL, this code: {{{ # select a row from website_type_tbl(wtype_id INTEGER PK, show_ads BOOLEAN) row = website_type_tbl.select().execute().fetchone() # insert another row website_type_tbl.insert().execute(wtype_id=5, show_ads=row.show_ads) }}} Produces error - SQLError: (ProgrammingError) ERROR: column show_ads is of type boolean but expression is of type integer What happens is, while fetching, boolean columns are fetched as integer type (0, 1). I don't know whether it is the expected behaviour or a bug. Bringing to notice, in case it is a bug. thanks sanjay --~--~-~--~~~---~--~~ 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: MySQL Functions
It must be something stupid on my end, then. I'm sure I'll smack myself when I figure it out. Thanks for your help. On Dec 20, 6:24 pm, Michael Bayer [EMAIL PROTECTED] wrote: i just tried out your example and it executes fine using mysql 5, query output is: SELECT blog_articles.timestamp AS blog_articles_timestamp, blog_articles.text AS blog_articles_text, blog_articles.id AS blog_articles_id, blog_articles.title AS blog_articles_title FROM blog_articles WHERE year(from_unixtime(blog_articles.timestamp)) = %s ORDER BY blog_articles.id params: ['2006'] --~--~-~--~~~---~--~~ 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: PostgreSQL - fetching boolean type gives integer type
Remember this is not a wiki! {{{}}} won't format the code as you might expect. BTW, everything ok here on pgsql; boolean are True or False. You should try posting at least your db metadata and tell us which version of pgsql and psycopg you're using. -- Alan Franzoni [EMAIL PROTECTED] - Togli .xyz dalla mia email per contattarmi. Remove .xyz from my address in order to contact me. - GPG Key Fingerprint (Key ID = FE068F3E): 5C77 9DC3 BD5B 3A28 E7BC 921A 0255 42AA FE06 8F3E --~--~-~--~~~---~--~~ 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: problem with backref
Michael Bayer ha scritto: the original mapping essentially expressed this relationship: A -- cascade=all, delete-orphan -- B in both directions. What that means is, there cannot be an A with no B in the database, and there cannot be a B with no A in the database, i.e. its either A-B or both will be deleted. its like an oxygen molecule, or something. what you need to do is decide which of A and B can exist on its own without a parent relationship. since you want to delete rows from B and not A, that would indicate that the mapping should be: mapper(A, a) mapper( B, b, properties={ 'a': relation( A, backref=backref('b', lazy=False, uselist=False, cascade='all, delete-orphan'), uselist=False ) } ) i.e. the delete-orphan cascade is only in the direction from A-B. the cascade from B-A is left at its default value of save-update, so delete operations dont propigate from B's to A's. Thanks, this works. The problem was with my original exemple where I create a session without binding it to a connection. Regards 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: problem with backref
Alan Franzoni ha scritto: The problem is that it requires the explicit use of the session. In my case this means that I can not delete the object inside a method of my class. class A(object): def enableB(enable=True): if enable: self.b = B(...) else: del self.b Not a big problem, however. There's the 'threadlocal' that should let you work as you like, but I've read its use is discouraged because it could lead to strange bugs. BTW, couldn't you just pass your session to the methods requiring it? class A(object): def enableB(enable=True, session): if enable: self.b = B(...) else: session.delete(self.b) A better solution is to do sess = object_session(self) Regards 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: problem with backref
A better solution is to do sess = object_session(self) That's wonderful and I didn't know about it! Finally I'll give up passing session around my code! Thanks! -- Alan Franzoni [EMAIL PROTECTED] - Togli .xyz dalla mia email per contattarmi. Remove .xyz from my address in order to contact me. - GPG Key Fingerprint (Key ID = FE068F3E): 5C77 9DC3 BD5B 3A28 E7BC 921A 0255 42AA FE06 8F3E --~--~-~--~~~---~--~~ 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] Partial Metadata Divination?
I'm just learning to use SQLAlchemy and loving the way it can figure out the columns for a simple table. Now, if I need relations, do I need to specify all tables? (Or can I get away with only specifying he relation?) Thanks, A. --~--~-~--~~~---~--~~ 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: PostgreSQL - fetching boolean type gives integer type
thats true the PG boolean type is not currently performing any translation on the value, meaning the 0 and 1 is what psycopg2 is giving you. however, the value should be able to go right back in if thats what its giving you, else psycopg2 is not consistent with itself. anyway as a workaround for now, you can try show_ads=(row.show_ads==1). --~--~-~--~~~---~--~~ 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: Partial Metadata Divination?
if you reflect a table that has a foreign key reference on itself to another table, that other table will be reflected as well, but at that point the second Table object is sitting inside the MetaData instance. to use it in a relation still requires that the related class is mapped, which requires having its table...soyeah for any class you want to map you need to explicitly get a hold of the corresponding Table (or use ActiveMapper, or SqlSoup, which try to work around this step). --~--~-~--~~~---~--~~ 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: PostgreSQL - fetching boolean type gives integer type
thats true the PG boolean type is not currently performing any translation on the value, meaning the 0 and 1 is what psycopg2 is giving you. however, the value should be able to go right back in if thats what its giving you, else psycopg2 is not consistent with itself. ? which version of pgsql and/or psycopg2 are both of you employing? is a pgsql 8.2-related issue? from sqlalchemy import * postgres_engine = create_engine(postgres://user:[EMAIL PROTECTED]:5432/testsa ) metadata = BoundMetaData(postgres_engine) editore = Table(editore, metadata, Column(id, Integer, Sequence(editore_id_seq), primary_key=True ), Column(editore, String(40), nullable=False, unique=True), Column(prova, Boolean), ) metadata.create_all() editore.insert().execute(id=3, editore=agboh, prova=True) e = editore.select(editore.c.id==2).execute().fetchone() print e.prova, type(e.prova) Result: True type 'bool' -- Alan Franzoni [EMAIL PROTECTED] - Togli .xyz dalla mia email per contattarmi. Remove .xyz from my address in order to contact me. - GPG Key Fingerprint (Key ID = FE068F3E): 5C77 9DC3 BD5B 3A28 E7BC 921A 0255 42AA FE06 8F3E --~--~-~--~~~---~--~~ 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: PostgreSQL - fetching boolean type gives integer type
in sanjay's example, its inserting an integer 1 or 0 value, not True. --~--~-~--~~~---~--~~ 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: How to auto-set timestamp: MySQL func.now(), but not SQLite
func.current_timestamp() (which evaulates without the parenthesis in SQL) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---