[sqlalchemy] UnicodeEncodeError on saving to MySQL
I've recently moved from SQLite to MySQL as the underlying database for my application. The move is causing me various problems, principally around Unicode. I *think* they were not occurring when SQLite was there, so I thought I would ask if anybody on the list has had similar problems. My current difficultly is that when saving content (Chinese, generally) to the database, I get the dreaded: *** UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-4: ordinal not in range(128) error and a rollback. I have checked that: - the MySQL table has a suitable encoding (CHARACTER SET utf8 COLLATE utf8_general_ci;) - the MySQL connection string has the charset on the end (?charset=utf8) - The type for the relevant Column is Unicode (this worked previously with just string, but I have changed it anyway just in case) - The content is unicode at the point it gets sent to the the database (again, this was a plain string previously but seemed to work) However, on save I get the above error from SQLAlchemy. What do I need to be doing to get the content in the right format to send through? Thanks, Ben -- 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] UnicodeEncodeError on saving to MySQL
File scraper.py, line 77, in run session.commit() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 617, in commit self.transaction.commit() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 293, in commit self._prepare_impl() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 277, in _prepare_impl self.session.flush() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1493, in flush self._flush(objects) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1562, in _flush flush_context.execute() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py, line 327, in execute rec.execute(self) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py, line 471, in execute uow File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/mapper.py, line 2107, in _save_obj execute(statement, params) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1399, in execute params) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1532, in _execute_clauseelement compiled_sql, distilled_params File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1633, in _execute_context context) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py, line 325, in do_execute cursor.execute(statement, parameters) File /usr/lib64/python2.6/site-packages/MySQLdb/cursors.py, line 158, in execute query = query % db.literal(args) File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 265, in literal return self.escape(o, self.encoders) File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 198, in string_literal return db.string_literal(obj) UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-3: ordinal not in range(128) On 29 September 2011 13:59, Michael Bayer mike...@zzzcomputing.com wrote: Can you attach a traceback for that? Sent from my iPhone On Sep 29, 2011, at 7:45 AM, Benjamin Sims benjamins...@gmail.com wrote: I've recently moved from SQLite to MySQL as the underlying database for my application. The move is causing me various problems, principally around Unicode. I *think* they were not occurring when SQLite was there, so I thought I would ask if anybody on the list has had similar problems. My current difficultly is that when saving content (Chinese, generally) to the database, I get the dreaded: *** UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-4: ordinal not in range(128) error and a rollback. I have checked that: - the MySQL table has a suitable encoding (CHARACTER SET utf8 COLLATE utf8_general_ci;) - the MySQL connection string has the charset on the end (?charset=utf8) - The type for the relevant Column is Unicode (this worked previously with just string, but I have changed it anyway just in case) - The content is unicode at the point it gets sent to the the database (again, this was a plain string previously but seemed to work) However, on save I get the above error from SQLAlchemy. What do I need to be doing to get the content in the right format to send through? Thanks, Ben -- 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. -- 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
Re: [sqlalchemy] UnicodeEncodeError on saving to MySQL
OK strange, MySQLdb is attempting to run an ascii encoder on a string (which
is likely already a string, not a unicode).Try ensuring that MySQLdb isn't
anticipating Python unicode objects, using a connection form as:
create_engine('mysql+mysqldb://user:pass@host/mydb?charset=utf8use_unicode=0')
also what is the exact version of MySQLdb - I'm on 1.2.3 which appears to be
the latest, but can't reproduce the error.
ms wrote:
File scraper.py, line 77, in run
session.commit()
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py,
line 617, in commit
self.transaction.commit()
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py,
line 293, in commit
self._prepare_impl()
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py,
line 277, in _prepare_impl
self.session.flush()
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py,
line 1493, in flush
self._flush(objects)
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py,
line 1562, in _flush
flush_context.execute()
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py,
line 327, in execute
rec.execute(self)
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py,
line 471, in execute
uow
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
line 2107, in _save_obj
execute(statement, params)
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 1399, in execute
params)
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 1633, in _execute_context
context)
File
/home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py,
line 325, in do_execute
cursor.execute(statement, parameters)
File /usr/lib64/python2.6/site-packages/MySQLdb/cursors.py, line 158, in
execute
query = query % db.literal(args)
File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 265,
in literal
return self.escape(o, self.encoders)
File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 198,
in string_literal
return db.string_literal(obj)
UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-3:
ordinal not in range(128)
On 29 September 2011 13:59, Michael Bayer mike...@zzzcomputing.com wrote:
Can you attach a traceback for that?
Sent from my iPhone
On Sep 29, 2011, at 7:45 AM, Benjamin Sims benjamins...@gmail.com wrote:
I've recently moved from SQLite to MySQL as the underlying database for my
application. The move is causing me various problems, principally around
Unicode. I *think* they were not occurring when SQLite was there, so I
thought I would ask if anybody on the list has had similar problems.
My current difficultly is that when saving content (Chinese, generally) to
the database, I get the dreaded:
*** UnicodeEncodeError: 'ascii' codec can't encode characters in position
0-4: ordinal not in range(128)
error and a rollback.
I have checked that:
- the MySQL table has a suitable encoding (CHARACTER SET utf8 COLLATE
utf8_general_ci;)
- the MySQL connection string has the charset on the end (?charset=utf8)
- The type for the relevant Column is Unicode (this worked previously with
just string, but I have changed it anyway just in case)
- The content is unicode at the point it gets sent to the the database
(again, this was a plain string previously but seemed to work)
However, on save I get the above error from SQLAlchemy. What do I need to be
doing to get the content in the right format to send through?
Thanks,
Ben
--
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
[sqlalchemy] Re: UnicodeEncodeError on saving to MySQL
I am of the opinion that unless you are absolutely certain that the data will be pure ASCII you should declare string fields in model as Unicode type rather than String. Also have a look at http://farmdev.com/talks/unicode/ because it will help you understand what the dreaded error really means. On Sep 29, 9:06 am, Benjamin Sims benjamins...@gmail.com wrote: File scraper.py, line 77, in run session.commit() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 617, in commit self.transaction.commit() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 293, in commit self._prepare_impl() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 277, in _prepare_impl self.session.flush() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1493, in flush self._flush(objects) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1562, in _flush flush_context.execute() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py, line 327, in execute rec.execute(self) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py, line 471, in execute uow File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/mapper.py, line 2107, in _save_obj execute(statement, params) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1399, in execute params) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1532, in _execute_clauseelement compiled_sql, distilled_params File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1633, in _execute_context context) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py, line 325, in do_execute cursor.execute(statement, parameters) File /usr/lib64/python2.6/site-packages/MySQLdb/cursors.py, line 158, in execute query = query % db.literal(args) File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 265, in literal return self.escape(o, self.encoders) File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 198, in string_literal return db.string_literal(obj) UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-3: ordinal not in range(128) On 29 September 2011 13:59, Michael Bayer mike...@zzzcomputing.com wrote: Can you attach a traceback for that? Sent from my iPhone On Sep 29, 2011, at 7:45 AM, Benjamin Sims benjamins...@gmail.com wrote: I've recently moved from SQLite to MySQL as the underlying database for my application. The move is causing me various problems, principally around Unicode. I *think* they were not occurring when SQLite was there, so I thought I would ask if anybody on the list has had similar problems. My current difficultly is that when saving content (Chinese, generally) to the database, I get the dreaded: *** UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-4: ordinal not in range(128) error and a rollback. I have checked that: - the MySQL table has a suitable encoding (CHARACTER SET utf8 COLLATE utf8_general_ci;) - the MySQL connection string has the charset on the end (?charset=utf8) - The type for the relevant Column is Unicode (this worked previously with just string, but I have changed it anyway just in case) - The content is unicode at the point it gets sent to the the database (again, this was a plain string previously but seemed to work) However, on save I get the above error from SQLAlchemy. What do I need to be doing to get the content in the right format to send through? Thanks, Ben -- 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
Re: [sqlalchemy] Re: UnicodeEncodeError on saving to MySQL
Thanks to you both. Michael, I must apologise - I had overlooked that another field was receiving Unicode characters. Changing this field to the Unicode type seems to have fixed the problem. Victor, you are right of course - I do have an understanding of what the dreaded error actually means, but I had been lazy in prototyping and used Strings. I'm not really sure why SQLite was apparently happy with this, but your mention of it made me think again and understand that I had misdirected myself as to the source of the problem. I will now go back and change all Strings to Unicode to be on the safe side. Ben On 29 September 2011 15:18, Victor Olex victor.o...@vtenterprise.comwrote: I am of the opinion that unless you are absolutely certain that the data will be pure ASCII you should declare string fields in model as Unicode type rather than String. Also have a look at http://farmdev.com/talks/unicode/ because it will help you understand what the dreaded error really means. On Sep 29, 9:06 am, Benjamin Sims benjamins...@gmail.com wrote: File scraper.py, line 77, in run session.commit() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 617, in commit self.transaction.commit() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 293, in commit self._prepare_impl() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 277, in _prepare_impl self.session.flush() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1493, in flush self._flush(objects) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1562, in _flush flush_context.execute() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py, line 327, in execute rec.execute(self) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py, line 471, in execute uow File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/mapper.py, line 2107, in _save_obj execute(statement, params) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1399, in execute params) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1532, in _execute_clauseelement compiled_sql, distilled_params File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1633, in _execute_context context) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py, line 325, in do_execute cursor.execute(statement, parameters) File /usr/lib64/python2.6/site-packages/MySQLdb/cursors.py, line 158, in execute query = query % db.literal(args) File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 265, in literal return self.escape(o, self.encoders) File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 198, in string_literal return db.string_literal(obj) UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-3: ordinal not in range(128) On 29 September 2011 13:59, Michael Bayer mike...@zzzcomputing.com wrote: Can you attach a traceback for that? Sent from my iPhone On Sep 29, 2011, at 7:45 AM, Benjamin Sims benjamins...@gmail.com wrote: I've recently moved from SQLite to MySQL as the underlying database for my application. The move is causing me various problems, principally around Unicode. I *think* they were not occurring when SQLite was there, so I thought I would ask if anybody on the list has had similar problems. My current difficultly is that when saving content (Chinese, generally) to the database, I get the dreaded: *** UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-4: ordinal not in range(128) error and a rollback. I have checked that: - the MySQL table has a suitable encoding (CHARACTER SET utf8 COLLATE utf8_general_ci;) - the MySQL connection string has the charset on the end (?charset=utf8) - The type for the relevant Column is Unicode (this worked previously with just string, but I have changed it anyway just in case) - The content
[sqlalchemy] Re: Softcoding .filter(...)
I think he means that if you only need an exact match on your query (instead
of a 'like' or a '' etc) you can do this:
dict_from_web = {'Title':'The Book','Author':'Bob Smith'}
for b in session.query(Book).filter_by(**dict_from_web)
will return the books that have the exact Title 'The Book' and the exact
author 'Bob Smith'
MB
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/8Yz-FVAb4TMJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] How do I get date types to match between MSSQL and Postgres
I have a postgres database local and stores data about our active students. The remote database for our SIS (Student Information System) is using Microsoft SQL. I'm using psycopg2 and pymssql libraries respectively. The date object types I'm getting back from these databases are different, so when I do a session.merge() from the remote to the local databases, is_modified() gets set and the history for that date attribute shows that a change is pending, even when the date is exactly the same in both instances. smalldatetime in MSSQL ends up as a datetime.datetime object, and date in postgresql ends up as a datetime.date. Is there an easy way I can force the MSSQL attribute to return a datetime.date object, or do I just need to do a type lookup on the object to make a change before I merge each time? Thanks in advance. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/5gBCNZKgyvUJ. 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.
