[sqlalchemy] Re: Unicode String Error on Insert
My solution didn't work. I was able to get my Portuguese data to load by
decoding it in ISO-8859-1, but by decoding I lose all the special
characters like tildes.
So I still don't understand how to get the engine to accept my data
properly.
J.D.
On Tuesday, March 3, 2015 at 3:00:24 PM UTC-7, J.D. wrote:
I actually figured this out.
It had nothing to do with my SQLAlchemy create_engine configuration.
The data I was trying to create an object with was in ISO-8859-1 format,
so I just had to construct my Object the text decoded properly.
Once I did this, the data was inserted into my sqlite3 table just fine.
On Tuesday, March 3, 2015 at 1:58:32 PM UTC-7, J.D. wrote:
Hi,
I am getting the following error, when I try to execute code to insert a
new row into one of my tables, and I've googled for answers and tried
everything I could find online and nothing seems to resolve the issue.
sqlalchemy.exc.ProgrammingError: (ProgrammingError) You must not use
8-bit bytestrings unless you use a text_factory that can interpret 8-bit
bytestrings (like text_factory = str)
*I am using the following software:*
SQLAlchemy v0.9.8
SQLite 3.8.8.2.
*I am creating my engine as follows,*
engine = create_engine('sqlite+pysqlite:///prototype.db', module=sqlite)
#engine.raw_connection().connection.text_factory = str
#engine.connect().connection.connection.text_factory = str
session = sessionmaker(bind=engine)()
meta.Base.metadata.bind = engine
meta.Base.metadata.create_all(engine)
*The object I am trying to insert via session.add(..) has a structure
similar to the following: (shortened for brevity)*
..
id = Column(Integer, primary_key=True, unique=True, nullable=False)
title = Column(String, nullable=False) // This is the column
that gets the Portuguese data with unicode characters -- I've tried using
the column type Unicode
# title = Column(Unicode, nullable=False)
book_id = Column(Integer, nullable=False)
code = Column(Integer, nullable=False)
...
*I've tried setting the text_factory on the connection to no avail. I'm
at a loss how to fix this so I can insert my data with unicode chars.*
Here is the SQL that is generated for the insert
It is highly recommended that you instead just switch your application to
Unicode strings. u'INSERT INTO books (title, book_id, code) VALUES (?, ?,
?)' ('Demana/Blitzer: Pr\x8e-c\x87lculo, 2e', '93810', 'removed')
I would appreciate some insight to how to fix this issue so I can insert
my data.
Thanks,
J.D.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Unicode String Error on Insert
I actually figured this out.
It had nothing to do with my SQLAlchemy create_engine configuration.
The data I was trying to create an object with was in ISO-8859-1 format, so
I just had to construct my Object the text decoded properly.
Once I did this, the data was inserted into my sqlite3 table just fine.
On Tuesday, March 3, 2015 at 1:58:32 PM UTC-7, J.D. wrote:
Hi,
I am getting the following error, when I try to execute code to insert a
new row into one of my tables, and I've googled for answers and tried
everything I could find online and nothing seems to resolve the issue.
sqlalchemy.exc.ProgrammingError: (ProgrammingError) You must not use 8-bit
bytestrings unless you use a text_factory that can interpret 8-bit
bytestrings (like text_factory = str)
*I am using the following software:*
SQLAlchemy v0.9.8
SQLite 3.8.8.2.
*I am creating my engine as follows,*
engine = create_engine('sqlite+pysqlite:///prototype.db', module=sqlite)
#engine.raw_connection().connection.text_factory = str
#engine.connect().connection.connection.text_factory = str
session = sessionmaker(bind=engine)()
meta.Base.metadata.bind = engine
meta.Base.metadata.create_all(engine)
*The object I am trying to insert via session.add(..) has a structure
similar to the following: (shortened for brevity)*
..
id = Column(Integer, primary_key=True, unique=True, nullable=False)
title = Column(String, nullable=False) // This is the column that
gets the Portuguese data with unicode characters -- I've tried using the
column type Unicode
# title = Column(Unicode, nullable=False)
book_id = Column(Integer, nullable=False)
code = Column(Integer, nullable=False)
...
*I've tried setting the text_factory on the connection to no avail. I'm
at a loss how to fix this so I can insert my data with unicode chars.*
Here is the SQL that is generated for the insert
It is highly recommended that you instead just switch your application to
Unicode strings. u'INSERT INTO books (title, book_id, code) VALUES (?, ?,
?)' ('Demana/Blitzer: Pr\x8e-c\x87lculo, 2e', '93810', 'removed')
I would appreciate some insight to how to fix this issue so I can insert
my data.
Thanks,
J.D.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: unicode everywhere
Hi Chris, Use Unicode/UnicodeText wherever you have text (and are able to know its character encoding at storage time, preferably always). Text and String are really just byte sequences, and should be used if it's arbitrary bytes you want to store. If you don't have control of the DB schema, then you must follow it; if it only stores bytes, then it's bytes you put in (i.e. you have to encode your text in some particular character encoding). But your application and your collaborators and successors will thank you for enforcing consistency: always store your text in the same character encoding. Generally: your application is responsible for knowing/guessing the character encoding of incoming textual data. Your data model should be unconcerned with it if at all possible. The significant performance overhead quoted in the docs is if you are forcing sqlalchemy to do unicode conversion on a platform that already natively supports it. I wouldn't worry about it otherwise. Under Python 3, Text and String still mean the same thing: they still refer to byte-sequences stored in the DB, and Unicode and UnicodeText still refer to text. The difference under Python 3 is that str is a unicode string type (unlike str in Python 2.x), and the byte-sequence type is called bytes (which is what str is for in Python 2.x). So in Python 3 you pass an object of type bytes for Text/String columns where you would have passed an object of type str in Python 2.x, and you pass an object of type str for UnicodeText/Unicode columns where you would have passed an object of type unicode in Python 2.x. Regards, - Gulli -- 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: Unicode problem with MySQL-Table with collation utf8_bin and utf8_general_ci
Thanks for the help so far. What I'm looking for is a possibility to get always unicode, not utf-8. My current workaround is to decode the result after every select into unicode if necessary. On 9 Dez., 16:35, Michael Bayer mike...@zzzcomputing.com wrote: specify use_unicode=0 on your MySQL engine. SQLAlchemy will perform the utf-8 decode instead. On Dec 9, 2010, at 6:56 AM, Marko Krause wrote: Hello, I have a problem with sqlalchemy 5.8 and mysql 5.1. I have a table with a text-column, which has the collation utf8_general_ci. When I access the table sqlalchemy returns a Unicode, as it was expected. But when I change the collation of the column to utf8_bin, sqlalchemy returns an utf8-encoded string. The mysql-engine is created with ?charset=utf8 and I'm using Python 2.6.5 on Ubuntu Lucid. Here is a example query: selectTexts = sqlalchemy.select([ table.c.id, table.c.text, ]).where(and_(table.c.id==id)); result = conn.execute(selectTexts).fetchall(); print result[0][1] With utf8_general_ci I get: u'test \xe4 \xf6 \xfc' and with utf8_bin I get: 'test \xc3\xa4 \xc3\xb6 \xc3\xbc' I this behaviour intended? Is there a way to get Unicode from utf8_bin- Tables/Columns? Thanks in advance, Marko -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Unicode Length
On Sep 5, 2009, at 10:40 AM, gizli wrote:
Hi all,
I searched the posts about this but could not find anything related. I
just had a quick question about the Unicode type and its length.
I did a number of tests and it seems that if I specify Unicode(10) in
my column type, this does not mean I can store unicode objects 10
unicode characters long. Here's a simple test:
engine = create_engine('mysql://localhost/mydb', echo=True)
make sure you're using the connect instructions for MySQL at
http://www.sqlalchemy.org/trac/wiki/DatabaseNotes#MySQL
. without the charset and use_unicode=0 flags you'll get double
encoding issues.
Information on how MySQL handles character sets is at :
http://dev.mysql.com/doc/refman/5.1/en/charset.html
I'm not sure myself if the charset flag sent to MySQLdb causes the
SET NAMES 'utf8'; instruction to be sent but I have a hunch that
this is the effect is has.
As far as the 10 in VARCHAR(10) meaning ten characters as opposed
to ten bytes, I'm not sure if MySQL's utf-8 mode accomplishes this
as well, you'd have to experiment.you definitely want to use CHAR/
VARCHAR/TEXT for storing characters though, not BLOB. SQLA
currently doesn't address the length of VARCHAR and similar in a
database-agnostic way with regards to characters, every database has
different quirks in this area and it would be a large and complex area
of study to cover that entire field.
--~--~-~--~~~---~--~~
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: Unicode Results from SQL functions
On Wed, Sep 24, 2008 at 10:45 PM, jason kirtland [EMAIL PROTECTED]wrote: Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. Ok, that works. I thought that create_engine(uri, encoding = latin1, convert_unicode = True) would do this. I am guessing from this that the create_engine arguments are NOT being passed along to the dbapi connector? --~--~-~--~~~---~--~~ 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: Unicode Results from SQL functions
Shawn Church wrote: On Wed, Sep 24, 2008 at 10:45 PM, jason kirtland [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. Ok, that works. I thought that create_engine(uri, encoding = latin1, convert_unicode = True) would do this. I am guessing from this that the create_engine arguments are NOT being passed along to the dbapi connector? No. I believe both of those are specifying the treatment of string data going _to_ the DB-API only, not bidirectional 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: Unicode Results from SQL functions
On Wed, Sep 24, 2008 at 11:04 PM, jason kirtland [EMAIL PROTECTED]wrote: Shawn Church wrote: On Wed, Sep 24, 2008 at 10:45 PM, jason kirtland [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. Ok, that works. I thought that create_engine(uri, encoding = latin1, convert_unicode = True) would do this. I am guessing from this that the create_engine arguments are NOT being passed along to the dbapi connector? No. I believe both of those are specifying the treatment of string data going _to_ the DB-API only, not bidirectional behavior. OK, lets see, check database encoding, table encoding, column encoding, connection encoding/convert to unicode, sqlalchemy encoding/convert to unicode, and client encoding and if they all match up I should be good to go :-) Please don't take that as a criticism of SQLAlchemy which is an excellent package it just always amazes me how a simple (YES Unicode is SIMPLE) idea can get so complicated. Thanks again for the help, Shawn --~--~-~--~~~---~--~~ 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: Unicode Results from SQL functions
On Sep 25, 2008, at 2:19 AM, Shawn Church wrote: On Wed, Sep 24, 2008 at 11:04 PM, jason kirtland [EMAIL PROTECTED] wrote: Shawn Church wrote: On Wed, Sep 24, 2008 at 10:45 PM, jason kirtland [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. Ok, that works. I thought that create_engine(uri, encoding = latin1, convert_unicode = True) would do this. I am guessing from this that the create_engine arguments are NOT being passed along to the dbapi connector? No. I believe both of those are specifying the treatment of string data going _to_ the DB-API only, not bidirectional behavior. OK, lets see, check database encoding, table encoding, column encoding, connection encoding/convert to unicode, sqlalchemy encoding/convert to unicode, and client encoding and if they all match up I should be good to go :-) Please don't take that as a criticism of SQLAlchemy which is an excellent package it just always amazes me how a simple (YES Unicode is SIMPLE) idea can get so complicated. use sqlite, and everything is unicode instantly ;) --~--~-~--~~~---~--~~ 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: Unicode Results from SQL functions
On Thu, Sep 25, 2008 at 6:47 AM, Michael Bayer [EMAIL PROTECTED]wrote: Converting *all* strings to unicode would then lead to havoc as soon as someone adds a Binary column to their schema. Another solution, which we have support for, would be for ResultProxy to attempt to match TypeEngine objects to the DBAPI types in cursor.description. We already do this for Oracle binary types, so perhaps this functionality could be enabled for all types. Its backwards incompatible though so it would need to be a create_engine() option (I'd call it detect_result_types). A technical issue with this option is that it creates issues with table reflection which would have to be worked around somehow. The ?charset=utf8use_unicode=1 parameter produces the expected behavior. I DID check the documentation and mailing list archives (many posts) before raising this issue but I thought use_unicode and convert_unicode were different forms of the same thing. I in fact DID try ?convert_unicode=1 as a URI paramater which of course was incorrecct. I'll try to write up a wiki page on Unicode. I'm assuming that the '?uri_params' are specific to each driver (ie mysqldb) so the syntax for unicode support may be different for each? I'm also planning on using binary strings so I need to write some more tests, although I THINK I understand what is going on now. Thanks again, Shawn --~--~-~--~~~---~--~~ 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: Unicode Results from SQL functions
A proposed patch which addresses the issue at the SQLAlchemy ResultProxy level is at: http://www.sqlalchemy.org/trac/ticket/1179 On Sep 25, 2008, at 3:43 PM, Shawn Church wrote: On Thu, Sep 25, 2008 at 6:47 AM, Michael Bayer [EMAIL PROTECTED] wrote: Converting *all* strings to unicode would then lead to havoc as soon as someone adds a Binary column to their schema. Another solution, which we have support for, would be for ResultProxy to attempt to match TypeEngine objects to the DBAPI types in cursor.description. We already do this for Oracle binary types, so perhaps this functionality could be enabled for all types. Its backwards incompatible though so it would need to be a create_engine() option (I'd call it detect_result_types). A technical issue with this option is that it creates issues with table reflection which would have to be worked around somehow. The ?charset=utf8use_unicode=1 parameter produces the expected behavior. I DID check the documentation and mailing list archives (many posts) before raising this issue but I thought use_unicode and convert_unicode were different forms of the same thing. I in fact DID try ?convert_unicode=1 as a URI paramater which of course was incorrecct. I'll try to write up a wiki page on Unicode. I'm assuming that the '?uri_params' are specific to each driver (ie mysqldb) so the syntax for unicode support may be different for each? I'm also planning on using binary strings so I need to write some more tests, although I THINK I understand what is going on now. Thanks again, Shawn --~--~-~--~~~---~--~~ 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: Unicode Results from SQL functions
On Sep 24, 2008, at 10:13 PM, Shawn Church wrote: I am trying to construct a select query in mysql (version 5.0.51a-3ubuntu5.3-log) using SQL functions. Once I set the convert_unicode flag = True on my engine some function results are returned as type str and some results are returned as type unicode (I want, and expected, all unicode). Although this problem can be resolved with an explict cast I suspect there might be a deeper problem. It seems that when an obvious string function is used (i.e. CONCAT in my case) then unicode is correctly returned. However when the return type is dependent on the results of the query (i.e. IFNULL, could return any type dependent on the arguments) then a str is returned. Am I just missing something or is this a problem with mysql or is there some other problem? Sample code and output is included below. the func.XXX() construct can draw upon a module of known functions such as CONCAT, in which case the return type of the function is known (sqlalchemy terms these generic functions). However, if the function name given is not a known function, then the return type is not known to be a string (which is necessary for convert_unicode to take effect).To coerce an arbitrary func to apply String processing to the result, use the type_ parameter: func.foo(arg1, arg2, ..., type_=Unicode) we can of course add more functions to the list of known functions such as ifnull() (it would be best if ifnull() is a SQL standard function, I'm not sure if it is). --~--~-~--~~~---~--~~ 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: Unicode Results from SQL functions
On Wed, Sep 24, 2008 at 7:37 PM, Michael Bayer [EMAIL PROTECTED]wrote: we can of course add more functions to the list of known functions such as ifnull() (it would be best if ifnull() is a SQL standard function, I'm not sure if it is). Not sure this will work for IFNULL since it's type depends upon the runtime arguments. I missed the func type_ argument when I read the documentation. That is a good solution for the general case of specifiying the type when it cannot be determined from the function or the function arguments. In fact I'm going to use it any time the type is not obvious. For what it is worth the following patch modifies ResultProxy to convert strings to unicode if convert_unicode == True. It 'fixes' my example and test/testall.py still passes. [EMAIL PROTECTED]:~/Projects/sqlalchemy$ svn diff Index: lib/sqlalchemy/engine/base.py === --- lib/sqlalchemy/engine/base.py(revision 5123) +++ lib/sqlalchemy/engine/base.py(working copy) @@ -1630,7 +1630,10 @@ if processor: return processor(row[index]) else: -return row[index] +result = row[index] +if isinstance(result, str) and self.context.dialect.convert_unicode: +result = result.decode(self.context.dialect.encoding) +return result def _fetchone_impl(self): return self.cursor.fetchone() [EMAIL PROTECTED]:~/Projects/sqlalchemy$ Thanks, Shawn --~--~-~--~~~---~--~~ 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: Unicode Results from SQL functions
Shawn Church wrote: On Wed, Sep 24, 2008 at 7:37 PM, Michael Bayer [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: we can of course add more functions to the list of known functions such as ifnull() (it would be best if ifnull() is a SQL standard function, I'm not sure if it is). Not sure this will work for IFNULL since it's type depends upon the runtime arguments. I missed the func type_ argument when I read the documentation. That is a good solution for the general case of specifiying the type when it cannot be determined from the function or the function arguments. In fact I'm going to use it any time the type is not obvious. For what it is worth the following patch modifies ResultProxy to convert strings to unicode if convert_unicode == True. It 'fixes' my example and test/testall.py still passes. Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. --~--~-~--~~~---~--~~ 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: Unicode not getting translated to SQL Server?
On Aug 29, 2008, at 10:53 AM, Mike wrote: If I use pymssql instead, it works. As I understand it, SA should be using pymssql anyway, so I don't know why this is happening. I can cast the unicode to a string, so it's not a big deal. However, I thought someone might want to know that this is happening. without a sample of the code and schema in use, we can't say for sure what the issue is. Are you making usage of the Unicode type at least ? Oh, and I use autoload to load my Table() objects. I'm not sure if that's significant or not though. I am also using a session.commit() to get the error above. You might want to enable convert_unicode=True on your Engine, or override the specific columns requiring unicode compatibility with the Unicode type. String columns are reflected by default without unicode awareness. --~--~-~--~~~---~--~~ 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: Unicode not getting translated to SQL Server?
Hi Mr. Bayer,
On Aug 29, 10:10 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Aug 29, 2008, at 10:53 AM, Mike wrote:
If I use pymssql instead, it works. As I understand it, SA should be
using pymssql anyway, so I don't know why this is happening. I can
cast the unicode to a string, so it's not a big deal. However, I
thought someone might want to know that this is happening.
without a sample of the code and schema in use, we can't say for sure
what the issue is. Are you making usage of the Unicode type at least ?
Since I am autoloading, I didn't know I could use the Unicode type to
override what was reflected. How do I do that?
There's only so much I can give being that I'm talking about
accounting data, but here's a sample that should work:
code
from sqlalchemy import create_engine, Column, Float, Integer
from sqlalchemy import MetaData, String, Table, Unicode
from sqlalchemy.orm import mapper, sessionmaker
class Acct_Prefs(object):
''' Table object for tbl_Acct_Prefs '''
def __init__(self, empID, pref_name, pref_value):
self.empID = empID
self.pref_name = pref_name
self.pref_value = pref_value
def __repr__(self):
return Acct_Prefs ('%s', '%s', '%s') % (self.empID,
self.pref_name,
self.pref_value)
class TimeEntries(object):
''' Table object for tbl_TimeEntries '''
def __init__(self, dateworked, empid, reg, ot, ce,
hol, sklv, vac, ct, conv, misc,
comments):
self.dateworked = dateworked
self.empid = empid
self.reg = reg
self.ot = ot
self.ce = ce
self.hol = hol
self.sklv = sklv
self.vac = vac
self.ct = ct
self.conv = conv
self.misc = misc
self.comments = comments
def __repr__(self):
return TimeEntries ('%s', '%s') % (self.dateworked,
self.empid)
# Connect to the database
print 'connecting to MCISAccounting DB...'
engine = create_engine('sqlite:///sample.db')
metadata = MetaData(engine)
# Load the tables
print 'loading tables...'
prefs_table = Table('tbl_Acct_Prefs', metadata,
Column('empID', String(5), primary_key=True),
Column('pref_name', String(50), primary_key=True,
nullable=False),
Column('pref_value', String(50), nullable=False)
)
entry_table = Table('tbl_TimeEntries', metadata,
Column('dateworked', String(10),
primary_key=True),
Column('empid', Integer, primary_key=True,
autoincrement=False),
Column('reg', Float),
Column('ot', Float),
Column('ce', Float),
Column('hol', Float),
Column('sklv', Float),
Column('vac', Float),
Column('ct', Float),
Column('conv', Float),
Column('misc', Float),
Column('comments',
Unicode(256))
)
metadata.create_all()
# Map the tables
print 'mapping tables...'
mapper(TimeEntries, entry_table)
mapper(Acct_Prefs, prefs_table)
# Create a session object
print 'creating session...'
Session = sessionmaker(bind=engine)
session = Session()
prefOne = Acct_Prefs(258, 'last_payPeriod', 2)
session.Add(prefOne)
session.commit()
/code
That should get it to look as similar as possible to the state the SQL
Server is in. Now here's where I get the issue. I do the following
query
pref = self.session.query(Acct_Prefs).filter_by(empID=258,
pref_name='last_payPeriod').first()
and then I do the following where someValue happens to be a unicode
string:
pref.pref_value = someValue
session.commit()
Oh, and I use autoload to load my Table() objects. I'm not sure if
that's significant or not though. I am also using a session.commit()
to get the error above.
You might want to enable convert_unicode=True on your Engine, or
override the specific columns requiring unicode compatibility with the
Unicode type. String columns are reflected by default without unicode
awareness.
This might work. I'll give it a whirl anyway. I hope my code sample
makes sense and isn't too ugly. I'm still pretty green with your cool
ORM and SQL in general.
Mike
--~--~-~--~~~---~--~~
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: Unicode not getting translated to SQL Server?
and then I do the following where someValue happens to be a unicode string: pref.pref_value = someValue session.commit() That's not going to work with pymssql as your DBAPI. Pymssql is based on Microsoft's DBLib (circa 1991 or so), which does not support unicode and never will: it's been unmaintained for something like 10 years now. You'll either need to convert everything to str() before it hits the DBAPI (which is what I do, I'm still using pymssql), or switch to pyodbc. I am using Python 2.5.2 on Windows XP. on windows, it should be easy to switch to pyodbc. Just install pyodbc, SQLAlchemy will look for it first, in preference over pymssql. 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: Unicode not getting translated to SQL Server?
Hi Rick, On Aug 29, 11:02 am, Rick Morrison [EMAIL PROTECTED] wrote: and then I do the following where someValue happens to be a unicode string: pref.pref_value = someValue session.commit() That's not going to work with pymssql as your DBAPI. Pymssql is based on Microsoft's DBLib (circa 1991 or so), which does not support unicode and never will: it's been unmaintained for something like 10 years now. You'll either need to convert everything to str() before it hits the DBAPI (which is what I do, I'm still using pymssql), or switch to pyodbc. I am using Python 2.5.2 on Windows XP. on windows, it should be easy to switch to pyodbc. Just install pyodbc, SQLAlchemy will look for it first, in preference over pymssql. Rick That sounds like a plan. I think I used to have that installed, but I may be confusing it with adodb. Thanks for the tip! Mike --~--~-~--~~~---~--~~ 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: Unicode data into Binary type
Mike Bernson wrote: Database is mysql I am having a problem with unicode and binary type. I am using an XML parse the return Unicode strings. I have a table in mysql with the default character set as utf8 I set the charset to utf8 in the connect string to charset=utf8 I am trying to send a unicode string to the Binary column in the table and get the following traceback What does the python side need to be so that the Binary type case accept the data. [...snip...] UnicodeEncodeError: 'ascii' codec can't encode character u'\u201c' in position 37: ordinal not in range(128) The binary column types are expecting binary data- e.g. str instances, not unicode. The driver is trying to munge your unicode into bytes it can send over the wire and failing. So either encode/decode as utf8 in and out of the blob, or, better yet use a Unicode clob type instead and Unicode will go in and out automagically. --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
Hi Florent, Just realised we'd gone quiet on this thread... humm What bothers me is that I already get this comportement when running my query program from a Linux host (using pyodbc same version) but need the above mentioned patch on a windows host so there is definitely a different behavior. Is this a difference in PyODBC or SQLAlchemy? I suspect the former, but good if you can confirm. From my point of view I am responsible to give the engine the right encoding when I instantiate it. At the moment I have a master database that provides me this info and so I feed it to the constructor at engine creation time. That sounds ok. I'd be happy to add a string_charset option or something that defaults to None which means no decoding. In fact, this wouldn't have to be MSSQL specific, it could apply to any DB. 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] Re: unicode support for MSSQL
On Nov 25, 2007 6:24 PM, Paul Johnston [EMAIL PROTECTED] wrote: Hi Florent, Just realised we'd gone quiet on this thread... humm What bothers me is that I already get this comportement when running my query program from a Linux host (using pyodbc same version) but need the above mentioned patch on a windows host so there is definitely a different behavior. Is this a difference in PyODBC or SQLAlchemy? I suspect the former, but good if you can confirm. the diff is in SA. Please look at my last patch to see why. This is because the support unicode flag is controlled by the test: supports_unicode = sys.maxunicode == 65535 and on Linux (mine at least) is equals to false, but on Windows it equals to True... my patch makes this flag always false on Windows and then all works as intended Florent. --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
On Nov 8, 2007 7:30 PM, Paul Johnston [EMAIL PROTECTED] wrote: Hi, I have isolated the problem a little bit more: my column is defined in the MSSQL server as a user defined type which is based on VARCHAR. Ok, so in this case you'd like SA to return a python unicode object when a VARCHAR is fetched, by decoding using the database's encoding? While I yep :) understand your requirement, this seems to me to be a special case. I think most people would expect a normal string in this case. I wonder if you should define a MyString class in your app and use that. humm What bothers me is that I already get this comportement when running my query program from a Linux host (using pyodbc same version) but need the above mentioned patch on a windows host so there is definitely a different behavior. If we do decide to implement this, does anyone know how python can find out what database encoding MSSQL is using? in each mssql db there is a syscolumns table that references each column by name and has a collation column that give the corresponding collation name. This is far from perfect but I don't know better at the moment :-/. From my point of view I am responsible to give the engine the right encoding when I instantiate it. At the moment I have a master database that provides me this info and so I feed it to the constructor at engine creation time. Florent. --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
On Nov 8, 2007 1:36 PM, Florent Aide [EMAIL PROTECTED] wrote: [...] My patch then works because in fact the object that come back from sql server is a binary string encoded using the aforementioned 'codepage'. I had contact with Christophe de Vienne (see above) who has access to the same databases from the same proprietary editor and confirmed the issue. The patch works when the column is defined in the model as String (because Unicode still returns type str even with my patch). Regards, Florent. --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
Hi Paul and others, I attached a new patch to the ticket (#839). It corrects the comportements I have and I now receive unicode objects in all cases: either when I declared String or Unicode as the column type and whatever the type of my columns in MSSQL (varchar based or nvarchar based) I think this is more correct than the previous patch. BTW: I use ms sql2000, pyodbc 2.0.38. I have other issues with 2.0.39 and SA that I'll post about later on :) --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
Hi, I have isolated the problem a little bit more: my column is defined in the MSSQL server as a user defined type which is based on VARCHAR. Ok, so in this case you'd like SA to return a python unicode object when a VARCHAR is fetched, by decoding using the database's encoding? While I understand your requirement, this seems to me to be a special case. I think most people would expect a normal string in this case. I wonder if you should define a MyString class in your app and use that. Rick - do you have a feel on this one? If we do decide to implement this, does anyone know how python can find out what database encoding MSSQL is using? 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] Re: unicode support for MSSQL
This is going to be messy, as the support for unicode varies among the various MSSQL DBAPIS (which is in lart part why multiple DBAPI support is needed by the MSSQL driver). ODBC looks to me to tell the best story: The newer ODBC drivers have an autotranslate feature that somehow retrieves the codepage setting of the server, and will translate from unicode on the client side to whatever the database encoding is. There may be a way to get the code page from that, or perhaps if the ODBC connection can be set to autotranslate, the code page fetch might not even be needed. In addition to Florent's case, I would lay odds that there are a fair number of legacy databases out there that store utf-8 data in varchar, binary and varbinary fields, and it would be great to support them too. If there was a way to set the autotranslate feature to utf8 on the database side, it might be possible to support both utf-8 data on any server and native codepage on those other servers. The question is whether pyodbc can do that or not. Any idea? On 11/8/07, Paul Johnston [EMAIL PROTECTED] wrote: Hi, I have isolated the problem a little bit more: my column is defined in the MSSQL server as a user defined type which is based on VARCHAR. Ok, so in this case you'd like SA to return a python unicode object when a VARCHAR is fetched, by decoding using the database's encoding? While I understand your requirement, this seems to me to be a special case. I think most people would expect a normal string in this case. I wonder if you should define a MyString class in your app and use that. Rick - do you have a feel on this one? If we do decide to implement this, does anyone know how python can find out what database encoding MSSQL is using? 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] Re: unicode support for MSSQL
On Nov 5, 2007 6:50 PM, Paul Johnston [EMAIL PROTECTED] wrote: [...] Are your strings VARCHAR or NVARCHAR? If they're NVARCHAR, all this will just work as-is with SA and MSSQL - are you having any specific problems? If they're VARCHAR, then we need to think some more. I'm still not sure what the semantics should be when trying to save a unicode object in a non-unicode storage area. I have isolated the problem a little bit more: my column is defined in the MSSQL server as a user defined type which is based on VARCHAR. I tried to force the user defined type to be based on NVARCHAR and it works. BUT... I cannot do this in production since I don't own the dbs and they are used by other applications, I just have a read-only access on them to do some interrogations. My patch then works because in fact the object that come back from sql server is a binary string encoded using the aforementioned 'codepage'. I had contact with Christophe de Vienne (see above) who has access to the same databases from the same proprietary editor and confirmed the issue. Christophe also noted that in Linux since the supports_unicode resolves to False, we don't encounter problems. But since my code will run on Windows I am still searching for a way to sort that mess :) Thanks for the help and time Paul :-) Regards, Florent. --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
Hi, I have 10 different instances the each have their own collation names (latin1, greek, russian...) I have a master database that references all thos instances + their collation names. I use this master database to create the engines to the different dbs. I would like to be able to just pass the encoding for each engine at creation time an forget about it. Are your strings VARCHAR or NVARCHAR? If they're NVARCHAR, all this will just work as-is with SA and MSSQL - are you having any specific problems? If they're VARCHAR, then we need to think some more. I'm still not sure what the semantics should be when trying to save a unicode object in a non-unicode storage area. 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] Re: unicode support for MSSQL
On Nov 4, 2007, at 3:58 PM, Florent Aide wrote: But in all the cases my encoding/decoding is always on the borders of my program and I can always count on the fact that all my strings are unicode objects everywhere. i think we're in agreement that that's the right way to do things; your program should deal with Unicode objects and the engines should convert to the appropriate encodings translating in/out from DBAPI. --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
Florent, I just added ticket #839 to the trac and attached a patch that enables unicode conversion for MSSQL dialects. I tested it with pyodbc but it should work the same with the other dialects. What's the benefit of doing this? I've found that you usually want to pass python unicode objects to PyODBC and adodbapi; utf-8 encoded strings don't work properly (they end up as literally that - utf-8 byte strings, stored in a 16-bit string container). 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] Re: Unicode column cannot be fetched again after a un-unicode string was inserted
that error is being thrown by your DBAPI (database driver), since its an OperationalError.so its something to do with your database configuration or the database-specific paramters you are sending it when you connect. On May 21, 2007, at 3:44 AM, Jiang Sanyi wrote: The column is set to Unicode，why SQLAlchemy still can save a un- unicode string, and now I cannot fetch this row. show_q_a_all() Traceback (most recent call last): File console, line 1, in ? File D:\Riverleaf-Python2.4package\RiverSMS\cli\question.py, line 6, in show _q_a_all qs = model.Question.select() File build\bdist.win32\egg\sqlalchemy\ext\assignmapper.py, line 7, in do File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 301, in select File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 309, in select_wher eclause File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 798, in _select_sta tement File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 696, in execute File build\bdist.win32\egg\sqlalchemy\orm\query.py, line 750, in instances File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 971, in fetchall OperationalError: Could not decode to UTF-8 column 'questions_headline' with tex t '为什么制定了MML的文档计划,今天却不去实施?' ~ 姜三义 Jiang Sanyi HZ03952 81925609/13376819539(Free) Skype ID: amthree 三义定律：工作没做好 Sanyi Rule: Work not well done ~~ --~--~-~--~~~---~--~~ 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: Unicode URL error
fix in changeset 2475
On Mar 30, 5:19 am, Andrew Stromnov [EMAIL PROTECTED] wrote:
ascii-encoded string:
url = 'mysql://login:[EMAIL PROTECTED]/adverts?charset=cp1251'
engine = sqlalchemy.create_engine(url, convert_unicode=True,
pool_recycle=4)
metadata = sqlalchemy.BoundMetaData(engine)
ad_table = sqlalchemy.Table('adverts', metadata, autoload=True)
unicode string:
url = u'mysql://login:[EMAIL PROTECTED]/adverts?charset=cp1251'
engine = sqlalchemy.create_engine(url, convert_unicode=True,
pool_recycle=4)
metadata = sqlalchemy.BoundMetaData(engine)
ad_table = sqlalchemy.Table('adverts', metadata, autoload=True)
Traceback (most recent call last):
File interactive input, line 1, in ?
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\schema.py, line
167, in __call__
metadata.get_engine().reflecttable(table)
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\engine\base.py,
line 754, in reflecttable
conn = self.contextual_connect()
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\engine\base.py,
line 748, in contextual_connect
return Connection(self, close_with_result=close_with_result,
**kwargs)
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\engine\base.py,
line 374, in __init__
self.__connection = connection or engine.raw_connection()
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\engine\base.py,
line 769, in raw_connection
return self.connection_provider.get_connection()
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\engine
\default.py, line 19, in get_connection
return self._pool.connect()
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\pool.py, line
147, in connect
return _ConnectionFairy(self).checkout()
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\pool.py, line
232, in __init__
self._connection_record = pool.get()
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\pool.py, line
160, in get
return self.do_get()
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\pool.py, line
429, in do_get
con = self.create_connection()
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\pool.py, line
143, in create_connection
return _ConnectionRecord(self)
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\pool.py, line
180, in __init__
self.connection = self.__connect()
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\pool.py, line
210, in __connect
connection = self.__pool._creator()
File c:\python24\develop\sqlalchemy\lib\sqlalchemy\engine
\strategies.py, line 71, in connect
raise exceptions.DBAPIError(Connection failed, e)
DBAPIError: (Connection failed) (TypeError) Connect() keywords must be
strings
--~--~-~--~~~---~--~~
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: unicode
Lee McFadden wrote:
Going from the line number in your exception that would be because
your columns are of type String. Change the columns to Unicode and it
should solve your problem.
foo_table = Table('foo', metadata,
Column('id', Integer, primary_key=True),
Column('bar', Unicode(255))
)
In case you're using reflection:
Overriding Reflected Columns
Individual columns can be overridden with explicit values when
reflecting tables; this is handy for specifying custom datatypes,
constraints such as primary keys that may not be configured within the
database, etc.
mytable = Table('mytable', meta,
... Column('id', Integer, primary_key=True), # override reflected 'id' to
have primary key
... Column('mydata', Unicode(50)),# override reflected 'mydata' to be
Unicode
... autoload=True)
http://www.sqlalchemy.org/docs/metadata.myt#metadata
or use convert_unicode=True in create_engine, as I do, it should help.
In my case, I have UTF8 as default encoding for postgres, and SA
reflects columns as PGString.
--~--~-~--~~~---~--~~
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: unicode
Marco Mariani wrote:
Lee McFadden wrote:
Going from the line number in your exception that would be because
your columns are of type String. Change the columns to Unicode and it
should solve your problem.
foo_table = Table('foo', metadata,
Column('id', Integer, primary_key=True),
Column('bar', Unicode(255))
)
In case you're using reflection:
Overriding Reflected Columns
Individual columns can be overridden with explicit values when
reflecting tables; this is handy for specifying custom datatypes,
constraints such as primary keys that may not be configured within the
database, etc.
mytable = Table('mytable', meta,
... Column('id', Integer, primary_key=True), # override reflected 'id' to
have primary key
... Column('mydata', Unicode(50)),# override reflected 'mydata' to be
Unicode
... autoload=True)
http://www.sqlalchemy.org/docs/metadata.myt#metadata
or use convert_unicode=True in create_engine, as I do, it should help.
In my case, I have UTF8 as default encoding for postgres, and SA
reflects columns as PGString.
Ciao Marco,
I replaced line 7 with line 8 in my model but the error is still there...
1 from turbogears import database
2 from sqlalchemy import Table, relation
3 from sqlalchemy.engine import create_engine
4 from sqlalchemy.ext.assignmapper import assign_mapper
5 database.bind_meta_data()
6 session = database.session
7 # engine = database.metadata.engine
8 engine = create_engine(postgres://username:[EMAIL PROTECTED]/sfera,
convert_unicode=True, encoding='utf-8')
9 context = session.context
...
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: unicode
let me show you whats happening:
rawdata = 'Alors vous imaginez ma surprise, au lever du jour, quand une
dr\xc3\xb4le de petit voix m\xe2\x80\x99a r\xc3\xa9veill\xc3\xa9. Elle
disait: \xc2\xab S\xe2\x80\x99il vous pla\xc3\xaet\xe2\x80\xa6
dessine-moi un mouton! \xc2\xbb\n'
unicodedata = rawdata.decode('utf-8')
print rawdata == unicodedata
will give you that error message. because you have
convert_unicode=True, the rows returned from your database will be as
unicode, not str, objects. dealing with raw utf-8 strings on the other
side produces this mismatch. so either dont use
convert_unicode/sqlalchemy.types.Unicode or insure that you pass
only unicode() objects to your object properties.
--~--~-~--~~~---~--~~
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: Unicode in sqlite
sqlite hasnt been tested with that particular configurational change (never knew it was possible, and it seems like it may not be working completely). however, SA will by default have nothing to do with unicode, if you arent using convert_unicode or any Unicode column types. encoding does nothing if you arent using convert_unicode or Unicode types. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
