Re: [sqlalchemy] Postgres migration issue
Hi David
Thanks for that - much appreciated :-)
Hi
I usually use MySQL to develop on, however I need to work with Postgres for
the first time today so I fired it up. I have a routine which converts a
non-SQL database into the database of choice, converting its schema into a
new table in the target database using SQA, and then copies all the data in
the source database into the new SQL table.
That all worked fine into the Postgres+pg8000 database. My problem is when I
then attempt to open up a table again using auto reflection I get an error
I've never seen before, and I don't get how this can be, given the table was
created via sqlalchemy? The data looks fine in the table, and all columns
are created as I expected (converting to the correct Postrgres column types
etc.
Error when I issue t = Table('my_table', meta, autoload=True) is; (sorry
about the screen shot, I'm working in a bad RDP client and can't cut/paste
into my Mac. :-(
PastedGraphic-1.png
So it appears to be having some problem in the reflection, but I can't see
why - I hope there is a setting in the connection or something I can do to
fix this up? I've never used Postgres before, so I'm groping in the dark..
From Googling around, it appears that there is some issue with determining
the schema or some such, but it's all assuming I know a lot more about
Postgres than I do!
Cheers
Warwick
Hi Warwick,
You are using pg8000 1.08 and PostgreSQL = 9.0. Upgrade to pg8000 1.09, it
fixes this issue (there are new PostgreSQL types introduced in version 9
which pg8000 didn't know of in 1.08, and added in 1.09).
regards
--
David Moore
Senior Software Engineer
St. James Software
Email: dav...@sjsoft.com
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Determining sqa type from dialect type
Hi All If I have a Column() object, is there a way of determining the sqlalchemy type from the dialect specific type? e.g. I have a Postgres TIMESTAMP column, and I want to be able to map that back the a sqa DateTime type. Why? If I want to automatically clone tables from one database to another (of different types) I can't simply clone the table metadata and use it to table.create in the destination engine. In my case source is MySQL destination is Postgres and it fails (correctly) saying that it does not know what a datetime type is. (Because it's using the MySQL dialect DATETIME not sqa DateTime as the basis of the column. Effectively, I want to reverse engineer the creation of the column. Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] how can i generate IS NOT True instead of != True ( orm ) ?
in postgresql i have a boolean field that allows Null values. i'd like to query for the items that are not 'true' filter( tablename.is_deleted != True ) creates this sql: is_deleted != True however this is incorrect and doesn't match the resultset i want. it needs to read : is_deleted IS NOT True I was hoping that i could do TableName.column_name.not_( True ) , but that didn't work. in postgresql ( and they claim the sql standard), !=/== and IS NOT/IS are different types of comparisons. http://www.postgresql.org/docs/8.2/static/functions-comparison.html Do not write expression = NULL because NULL is not equal to NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard. The ordinary comparison operators yield null (signifying unknown) when either input is null. Another way to do comparisons is with the IS [ NOT ] DISTINCT FROM construct: expression IS DISTINCT FROM expression expression IS NOT DISTINCT FROM expression For non-null inputs, IS DISTINCT FROM is the same as the operator. However, when both inputs are null it will return false, and when just one input is null it will return true. Similarly, IS NOT DISTINCT FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these constructs effectively act as though null were a normal data value, rather than unknown. Boolean values can also be tested using the constructs expression IS TRUE expression IS NOT TRUE expression IS FALSE expression IS NOT FALSE expression IS UNKNOWN expression IS NOT UNKNOWN These will always return true or false, never a null value, even when the operand is null. A null input is treated as the logical value unknown. Notice that IS UNKNOWN and IS NOT UNKNOWN are effectively the same as IS NULL and IS NOT NULL, respectively, except that the input expression must be of Boolean 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how can i generate IS NOT True instead of != True ( orm ) ?
Jonathan Vanasco jonat...@findmeon.com writes: in postgresql i have a boolean field that allows Null values. i'd like to query for the items that are not 'true' filter( tablename.is_deleted != True ) creates this sql: is_deleted != True however this is incorrect and doesn't match the resultset i want. it To be fair, it is correct in terms of doing what you asked, though if you want it to include NULLs I agree it doesn't do what you want... needs to read : is_deleted IS NOT True There are is and isnot operators in sqlalchemy.sql.operators, but I'm not entirely sure how to trigger from within an expression (and they don't seem to be column operators, but I'm probably missing something simple). However, you should be able to use the generic op method, as in: filter(tablename.is_deleted.op(IS NOT)(True)) Alternatively, you could explicitly manage the handling of NULL entries: from sqlalchemy.sql.functions import coalesce filter(coalesce(tablename.is_deleted, False) != True) This way you avoid the treatment of NULLs in the comparison entirely. This method also extrapolates to other cases more readily since it works with non-boolean fields as well. -- David -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how can i generate IS NOT True instead of != True ( orm ) ?
To be fair, it is correct in terms of doing what you asked, though if you want it to include NULLs I agree it doesn't do what you want... You're absolutely correct. Poorly worded on my part. I meant to convey that it's not the correct statement for me to call ; it is indeed the correct sql for that statement. != and is not are completely different comparisons. I just spent 4 hours digging through sqlalchemy docs and code to try and figure out how to get this to work on columns. is isnot should really be column operators. there's also no UNKNOWN value/keyword in sqlalchemy -- which is a concept that is in postgresql and might have helped me in my wild chase. Anyways, Thanks a ton! filter(tablename.is_deleted.op(IS NOT)(True)) works perfectly , crisis averted! Your coalesce idea is a good one. I feel a little bit better using the .op() method though - this query is already kind of complex and slow. adding a handful of coalesce statements instead of using a native comparison is not a road i want to explore right now. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Postgres migration issue
On Aug 9, 2012, at 7:58 PM, Warwick Prince wrote: Hi I usually use MySQL to develop on, however I need to work with Postgres for the first time today so I fired it up. I have a routine which converts a non-SQL database into the database of choice, converting its schema into a new table in the target database using SQA, and then copies all the data in the source database into the new SQL table. That all worked fine into the Postgres+pg8000 database. My problem is when I then attempt to open up a table again using auto reflection I get an error I've never seen before, and I don't get how this can be, given the table was created via sqlalchemy? The data looks fine in the table, and all columns are created as I expected (converting to the correct Postrgres column types etc. I'd highly recommend using psycopg2 here. Its featureset, performance (it's written completely in C), project velocity , and support responsiveness are unmatched not just by pg8000 but any other DBAPI I've used. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Determining sqa type from dialect type
On Aug 10, 2012, at 5:19 AM, Warwick Prince wrote: Hi All If I have a Column() object, is there a way of determining the sqlalchemy type from the dialect specific type? e.g. I have a Postgres TIMESTAMP column, and I want to be able to map that back the a sqa DateTime type. Why? column.type._type_affinity -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] how can i generate IS NOT True instead of != True ( orm ) ?
On Aug 10, 2012, at 3:24 PM, Jonathan Vanasco wrote: in postgresql i have a boolean field that allows Null values. i'd like to query for the items that are not 'true' filter( tablename.is_deleted != True ) creates this sql: is_deleted != True however this is incorrect and doesn't match the resultset i want. it needs to read : is_deleted IS NOT True I was hoping that i could do TableName.column_name.not_( True ) , but that didn't work. this is an API omission and http://www.sqlalchemy.org/trac/ticket/2544 is added. There is direct support for IS IS NOT, and to workaround the lack of the method looks like this: from sqlalchemy.sql import column from sqlalchemy.sql.expression import _BinaryExpression from sqlalchemy.sql import operators def is_(a, b): return _BinaryExpression(a, b, operators.is_, negate=operators.isnot) print is_(column(x), True) print ~is_(column(x), True) output: x IS true x IS NOT true in postgresql ( and they claim the sql standard), !=/== and IS NOT/IS are different types of comparisons. http://www.postgresql.org/docs/8.2/static/functions-comparison.html Do not write expression = NULL because NULL is not equal to NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard. The ordinary comparison operators yield null (signifying unknown) when either input is null. Another way to do comparisons is with the IS [ NOT ] DISTINCT FROM construct: expression IS DISTINCT FROM expression expression IS NOT DISTINCT FROM expression For non-null inputs, IS DISTINCT FROM is the same as the operator. However, when both inputs are null it will return false, and when just one input is null it will return true. Similarly, IS NOT DISTINCT FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these constructs effectively act as though null were a normal data value, rather than unknown. Boolean values can also be tested using the constructs expression IS TRUE expression IS NOT TRUE expression IS FALSE expression IS NOT FALSE expression IS UNKNOWN expression IS NOT UNKNOWN These will always return true or false, never a null value, even when the operand is null. A null input is treated as the logical value unknown. Notice that IS UNKNOWN and IS NOT UNKNOWN are effectively the same as IS NULL and IS NOT NULL, respectively, except that the input expression must be of Boolean 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Determining sqa type from dialect type
Hi Michel Thanks! I knew it was in there somewhere! :-) Cheers Warwick Hi All If I have a Column() object, is there a way of determining the sqlalchemy type from the dialect specific type? e.g. I have a Postgres TIMESTAMP column, and I want to be able to map that back the a sqa DateTime type. Why? column.type._type_affinity -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
