[sqlalchemy] Unicode String Error on Insert
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.
Re: [sqlalchemy] Unicode String Error on Insert
J.D. jd.cor...@pearson.com wrote:
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)
the error means that your unicode string has to be passed to pysqlite as a
Python unicode object, that is, in Python 2 it has to be with a “u”, u’my
string’.
if you’re losing encoding information, that means that the encoding you’re
using to decode into unicode is probably not correct.
Feel free to share the string literal and I can round trip it into SQLite
for you.
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.
--
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] unicode SAWarning
Hi, I'm just pulling this up because it's a little anoying. Everytime I start my application, this warning shows: *[...]/lib/python2.7/site-packages/sqlalchemy/engine/default.py:471: SAWarning: Unicode type received non-unicode bind param value.** ** processors[key](compiled_params[key])* I don't know if it's some OS+source+db combination I have in my system or something else. Anyway, some info about: * SA 0.8.1 with C extensions - from pip * psycopg2 2.5 (dt dec pq3 ext) - from pip * Python 2.7.4 (default, Apr 19 2013, 18:28:01) - [GCC 4.7.3] on linux2 * OS: [Ubuntu] Linux rkuesters 3.8-10.dmz.1-liquorix-amd64 #1 ZEN SMP PREEMPT Thu May 2 07:09:55 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux * PostgreSQL 9.1 (9.1.9-1ubuntu1) Thanks! Richard :) -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] unicode SAWarning
this happens when you do this: class SomeClass(Base): # ... some_col = Column(Unicode(50)) s = SomeClass(somestring='some value') and then when you commit s to the database, the bytestring is detected and the warning emitted. Note 'some value' is a Python bytestring, not a Unicode string, that is: u'some value'. Solutions: 1. use unicode strings, u'some value'. 2. turn on u'' everywhere, using from __future__ import unicode_literals 3. use a coercing unicode type, see the example at http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#coercing-encoded-strings-to-unicode 4. turn the warning off: http://docs.python.org/2/library/warnings.html 5. use the Unicode type with convert_unicode=False: Column(Unicode(50, convert_unicode=False)) - since you're using psycopg2, SQLAlchemy doesn't need to be involved here and setting this flag will disable the check. On May 6, 2013, at 4:33 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: Hi, I'm just pulling this up because it's a little anoying. Everytime I start my application, this warning shows: [...]/lib/python2.7/site-packages/sqlalchemy/engine/default.py:471: SAWarning: Unicode type received non-unicode bind param value. processors[key](compiled_params[key]) I don't know if it's some OS+source+db combination I have in my system or something else. Anyway, some info about: SA 0.8.1 with C extensions - from pip psycopg2 2.5 (dt dec pq3 ext) - from pip Python 2.7.4 (default, Apr 19 2013, 18:28:01) - [GCC 4.7.3] on linux2 OS: [Ubuntu] Linux rkuesters 3.8-10.dmz.1-liquorix-amd64 #1 ZEN SMP PREEMPT Thu May 2 07:09:55 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux PostgreSQL 9.1 (9.1.9-1ubuntu1) Thanks! Richard :) -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] unicode SAWarning
Thanks Mike. I know I must use unicode, and, in a matter of fact, I do respect the first item of your list. That's why I asked here :) I'll try items 2 and 5 (which seems more appropriate, since my application runs only on Postgres). Best regards, Richard. On 05/06/2013 05:50 PM, Michael Bayer wrote: this happens when you do this: class SomeClass(Base): # ... some_col = Column(Unicode(50)) s = SomeClass(somestring='some value') and then when you commit s to the database, the bytestring is detected and the warning emitted. Note 'some value' is a Python bytestring, not a Unicode string, that is: u'some value'. Solutions: 1. use unicode strings, u'some value'. 2. turn on u'' everywhere, using from __future__ import unicode_literals 3. use a coercing unicode type, see the example at http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#coercing-encoded-strings-to-unicode 4. turn the warning off: http://docs.python.org/2/library/warnings.html 5. use the Unicode type with convert_unicode=False: Column(Unicode(50, convert_unicode=False)) - since you're using psycopg2, SQLAlchemy doesn't need to be involved here and setting this flag will disable the check. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Unicode warnings - show offending value
Am 10.01.2013 00:45, schrieb Michael Bayer: Can't, because as a python warning, the warnings lib caches that message permanently, to support the typical case that the warnings filter is set to once. If the message were unique for every value your app would slowly leak memory I see, bad luck. I'll continue to modify my SQLAlchemy ad hoc then. fs -- 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] Unicode warnings - show offending value
Felix, Another technique I have used, in case you are not aware of it: I add this: import traceback util.warn(str(traceback.format_stack())) to whichever sqlalchemy .py file is producing the error, right at the point where the error is emitted. Then whenever it happens, you get a full stack trace in the log. From here you can usually find out where in you app the non-unicode string is coming from. --bill On Thu, Jan 10, 2013 at 1:37 AM, Felix Schwarz felix.schw...@oss.schwarz.eu wrote: Am 10.01.2013 00:45, schrieb Michael Bayer: Can't, because as a python warning, the warnings lib caches that message permanently, to support the typical case that the warnings filter is set to once. If the message were unique for every value your app would slowly leak memory I see, bad luck. I'll continue to modify my SQLAlchemy ad hoc then. fs -- 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] Unicode warnings - show offending value
sometimes I get unicode warnings from SQLAlchemy (Unicode type received non-unicode bind param value). I find it very hard to fix these issues because I don't know where to look (often it's not a string literal explicitely in the code but some external library which returns a string instead of a unicode string). Could SQLAlchemy maybe add repr(value) to the unicode warning? The downside would be that people who want to ignore these had to actually configure a warning filter instead of just ignoring one line in the output. fs -- 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] unicode in where clause on mysql
Hi there, I am trying to do a select on a table, where a user has a /username/ with a German umlaut like kölbel. The table in the mysql-database is utf-8. I also failed when the table was latin1. My problem is that by no(!) chance I manage to match the user and get a row from the select statement. (see version2) Should it be possible at all or am I just screwing up with my non-existent unicode-skills! ;-) Thanks a lot and kind regards Cornelius # -*- coding: utf-8 -*- from sqlalchemy import create_engine from sqlalchemy import MetaData from sqlalchemy.orm import sessionmaker from sqlalchemy import Table CONNECT_STRING=mysql://my_connect_string TABLE = linotp_user USER = ukölbel engine = create_engine(CONNECT_STRING, echo=False) meta= MetaData() Session = sessionmaker(bind=engine) session = Session() table = Table(TABLE, meta, autoload=True, autoload_with=engine) print type of user: , type(USER) select = table.select(uusername = '%s' % USER) print select statement: , select print type of select: , type(select) print Printing rows, version 1 rows = session.execute(select) for row in rows: print ::: , row print Printing rows, version 2 sel_string=uselect * from %s where username = '%s' % (TABLE, USER) print type(sel_string) print sel_string rows = engine.execute(sel_string) for row in rows: print ::: , row session.close() signature.asc Description: OpenPGP digital signature
Re: [sqlalchemy] unicode in where clause on mysql
easiest way here would be to specify use_unicode=1charset=utf8 with the MySQL connection string, so that your MySQLdb DBAPI handles incoming unicode objects correctly. http://docs.sqlalchemy.org/en/rel_0_7/dialects/mysql.html#unicode On Jul 18, 2012, at 7:45 AM, Cornelius Kölbel wrote: Hi there, I am trying to do a select on a table, where a user has a /username/ with a German umlaut like kölbel. The table in the mysql-database is utf-8. I also failed when the table was latin1. My problem is that by no(!) chance I manage to match the user and get a row from the select statement. (see version2) Should it be possible at all or am I just screwing up with my non-existent unicode-skills! ;-) Thanks a lot and kind regards Cornelius # -*- coding: utf-8 -*- from sqlalchemy import create_engine from sqlalchemy import MetaData from sqlalchemy.orm import sessionmaker from sqlalchemy import Table CONNECT_STRING=mysql://my_connect_string TABLE = linotp_user USER = ukölbel engine = create_engine(CONNECT_STRING, echo=False) meta= MetaData() Session = sessionmaker(bind=engine) session = Session() table = Table(TABLE, meta, autoload=True, autoload_with=engine) print type of user: , type(USER) select = table.select(uusername = '%s' % USER) print select statement: , select print type of select: , type(select) print Printing rows, version 1 rows = session.execute(select) for row in rows: print ::: , row print Printing rows, version 2 sel_string=uselect * from %s where username = '%s' % (TABLE, USER) print type(sel_string) print sel_string rows = engine.execute(sel_string) for row in rows: print ::: , row session.close() PGP.sig Description: This is a digitally signed message part
[sqlalchemy] unicode everywhere
Hi All, I'm keen to avoid being bitten by annoying unicode decoding and encoding errors, particularly later during project development (you know, when the stuff that no longer fits into the encoding you accidentally used turns up once you think development is done and dusted ;-) ) What's the best way to do this? Avoid Text and String columns and use Unicode and UnicodeText columns everywhere? Assuming that is the case, what about where you're reflecting a database's tables rather than defining them and those tables do have encoded columns? I see the convert_unicode option to sqlalchemy.types.String and, more likely to get used by me, the convert_unicode option to create_engine. However, I'm wary of the convert_unicode behaviour warning that it will significant performance overhead. When does this happen and how do I avoid it? cheers, Chris PS: What happens with Text and String columns in Python 3? -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] Unicode problem with MySQL-Table with collation utf8_bin and utf8_general_ci
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 at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Unicode problem with MySQL-Table with collation utf8_bin and utf8_general_ci
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 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 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] Unicode Results from SQL functions
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.
Thanks in advance,
Shawn
=== Sample Output ==
Executing command:
/usr/lib/python2.5/site-packages/aPyIdea-0.1.0a1-py2.5.egg/apyidea/pause.py
/usr/bin/python /home/schurch/Projects/MTZAuto/mtzauto/model/test.py
Simple query, Unicode OK: [(1L, u'a', u'b'), (2L, u'c', u'd')]
IFNULL, No Unicode: [('a', 'b'), ('c', 'd')]
CAST to Unicode, Unicode OK: [(u'a', u'b'), (u'c', u'd')]
CAST to String, Unicode OK: [(u'a', u'b'), (u'c', u'd')]
CONCAT(results), Unicode OK: [(u'a', u'b'), (u'c', u'd')]
Press enter to close window
= test.py ==
from elixir import *
from sqlalchemy import select, func, or_, create_engine, union, cast
class Test(Entity):
field1 = Field(Unicode(50))
field2 = Field(Unicode(50))
if __name__ == __main__:
setup_all()
uri = mysql://test:[EMAIL PROTECTED]/test
engine = create_engine(uri, encoding = latin1, convert_unicode = True)
engine.execute('SET collation_connection = latin1_general_ci')
metadata.bind = engine
metadata.bind.echo = False
drop_all()
create_all()
Test(field1 = ua, field2 = ub)
Test(field1 = uc, field2 = ud)
session.commit()
t = Test.table
q = select([Test.table])
# Simple query
print Simple query, Unicode OK:, list(session.execute(q))
# Query with IFNULL functions that return strings
field2 = func.ifnull(t.c.field2, uUnknown)
field1 = func.ifnull(t.c.field1, field2)
q = select([field1, field2])
print \nIFNULL, No Unicode:, list(session.execute(q))
# Results of IFNULL explicity CAST to Unicode
field2 = cast(func.ifnull(t.c.field2, uUnknown), Unicode)
field1 = cast(func.ifnull(t.c.field1, field2), Unicode)
q = select([field1, field2])
print \nCAST to Unicode, Unicode OK:, list(session.execute(q))
# Results of IFNULL explicity CAST to String
field2 = cast(func.ifnull(t.c.field2, uUnknown), String)
field1 = cast(func.ifnull(t.c.field1, field2), String)
q = select([field1, field2])
print \nCAST to String, Unicode OK:, list(session.execute(q))
# Results of IFNULL used in CONCAT
field2 = func.concat(func.ifnull(t.c.field2, uUnknown))
field1 = func.concat(func.ifnull(t.c.field1, field2))
q = select([field1, field2])
print \nCONCAT(results), Unicode OK:, list(session.execute(q))
--~--~-~--~~~---~--~~
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] Unicode not getting translated to SQL Server?
Hi,
I have a weird issue wherein I send a unicode string to SQL Server
2000 using SqlAlchemy in a wxPython program. A little history would
probably help: I created a timesheet program last year using wxPython
and connected to my databases using pymssql. In the hopes of making
this program more flexible and efficient, I decided to switch to
SqlAlchemy.
In my program, I can view 26 separate time periods that show the
user's time worked. To tell what period I am in, I grab the title of
the wx frame. I store this in one of my tables so that the next time
the user opens their timesheet, it will open to the last opened time
period. Anyway, it appears that the wxPython returns the title as a
unicode string.
When I try to commit this string to my database, SqlAlchemy blows up
and gives me this huge traceback:
Traceback (most recent call last):
File \\debianis\loginscript$\PythonPackages\Development
\Timesheet_sa
\ts_worksheet_MT.py, line 689, in onClose
session.flush()
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\orm\session.py, line 789, in flush
self.uow.flush(self, objects)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\orm\unitofwork.py, line 233, in flush
flush_context.execute()
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\orm\unitofwork.py, line 445, in execute
UOWExecutor().execute(self, tasks)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\orm\unitofwork.py, line 930, in execute
self.execute_save_steps(trans, task)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\orm\unitofwork.py, line 945, in execute_save_steps
self.save_objects(trans, task)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\orm\unitofwork.py, line 936, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\orm\mapper.py, line 1144, in _save_obj
c = connection.execute(statement.values(value_params), params)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\engine\base.py, line 844, in execute
return Connection.executors[c](self, object, multiparams, params)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\engine\base.py, line 895, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\engine\base.py, line 907, in _execute_compiled
self.__execute_raw(context)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\engine\base.py, line 916, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\engine\base.py, line 960, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
\sqlalchemy\engine\base.py, line 942, in _handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) internal error:
None 'UPDATE [tbl_Acct_Prefs] SET pref_value=%(pref_value)s WHERE
[tbl_Acct_Prefs].[empID] = %(tbl_Acct_Prefs_empID)s AND
[tbl_Acct_Prefs].pref_name = %
(tbl_Acct_Prefs_pref_name)s' {'pref_value': u'4',
'tbl_Acct_Prefs_pref_name': 'last_payPeriod', 'tbl_Acct_Prefs_empID':
258}
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.
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.
I have upgraded to the 5.0beta3 too, but it gives the same error as
above. I am using Python 2.5.2 on Windows XP. Let me know if you
require additional information.
Thanks,
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] Unicode data into Binary type
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. The line that is failing in the Mysqldb: def Binary(x): return str(x) SQLAlchemny place: def bind_processor(self, dialect): DBAPIBinary = dialect.dbapi.Binary def process(value): if value is not None: return DBAPIBinary(value) else: return None return process File /usr/local/ltcd/lib/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/engine/base.py, line 789, in execute return Connection.executors[c](self, object, multiparams, params) File /usr/local/ltcd/lib/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/engine/base.py, line 840, in execute_clauseelement return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) 1), distilled_params=params) File /usr/local/ltcd/lib/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/engine/base.py, line 849, in _execute_compiled context = self.__create_execution_context(compiled=compiled, parameters=distilled_params) File /usr/local/ltcd/lib/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/engine/base.py, line 858, in __create_execution_context return self.engine.dialect.create_execution_context(connection=self, **kwargs) File /usr/local/ltcd/lib/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/databases/mysql.py, line 1461, in create_execution_context return MySQLExecutionContext(self, connection, **kwargs) File /usr/local/ltcd/lib/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/engine/default.py, line 174, in __init__ self.parameters = self.__convert_compiled_params(self.compiled_parameters) File /usr/local/ltcd/lib/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/engine/default.py, line 228, in __convert_compiled_params param.append(processors[key](compiled_params[key])) File /usr/local/ltcd/lib/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/types.py, line 440, in process return DBAPIBinary(value) File /var/lib/python-support/python2.5/MySQLdb/__init__.py, line 69, in Binary return str(x) UnicodeEncodeError: 'ascii' codec can't encode character u'\u201c' in position 37: ordinal not in range(128) --~--~-~--~~~---~--~~ 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] unicode support for MSSQL
Hi Mike, Hi all, Our conversation yesterday night on IRC gave me ideas :) 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. Best regards, Florent Aide. --~--~-~--~~~---~--~~ 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] Unicode column cannot be fetched again after a un-unicode string was inserted
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] Unicode URL error
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] unicode
Hi all, I'm trying to insert UTF8 characters (like this: perchè) in PostgreSQL but... File build/bdist.linux-i686/egg/sqlalchemy/orm/attributes.py, line 589, in __init__ File build/bdist.linux-i686/egg/sqlalchemy/orm/attributes.py, line 51, in is_equal File build/bdist.linux-i686/egg/sqlalchemy/orm/strategies.py, line 30, in lambda File build/bdist.linux-i686/egg/sqlalchemy/types.py, line 179, in compare_values UnicodeDecodeError: 'ascii' codec can't decode byte 0xc2 in position 12: ordinal not in range(128) 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 -~--~~~~--~~--~--~---
