Ramblings

July 6, 2008

Getting rid of the dreaded “can’t encode” errors with Python and MySQL and SqlSoup

Filed under: automation, cool, dev, mysql, python, sqlalchemy, sqlsoup, tip, xls — michaelangela @ 10:47 pm

So I was getting problems inserting data that has the ‘™’ symbol in it. Brutal stuff. Couldn’t find a way around it. Did it by hand in the end. Nasty. The problem was that I was getting

UnicodeEncodeError: 'latin-1' codec can't encode character u'\u2122' in position 12: ordinal not in range(256)

where the '\u2122' is the ‘™’ symbol. There were some others as well. Finally happened upon this post which, fortunately, cleared it up. To use this tip with SqlSoup, the connection string needs a couple of extra parameters.

Instead of

db = SqlSoup('mysql://user:password@host:port/database')

use

db = SqlSoup('mysql://user:password@host:port/database?charset=utf8&use_unicode=0')

The requirement of course is that the database tables are set up with the UTF-8 charset.

But now I can thrash data at will! Mwahahahaha! Sorry… but now I can pull in unicode data from an Excel file and throw it into a database with rediculous ease. OK not rediculous ease but wow this simplifies it.

[edit]I like to use iPython to do my data thrashing with SqlSoup and SqlAlchemy. If you use the second connection string with the charset and use_unicode options, don’t reset the db connection to the previous one without those. Once doing that I couldn’t set it back and had to exit and restart the session. Bummer.[/edit]

mysql unicode issues – sqlalchemy | Google Groups

a. have use_unicode=0, set charset=’utf8′ on the connection, AND

b. ensure you have
table options = {‘mysql_charset’: ‘utf8’} when creating tables, and
all should be well.

Advertisements

March 13, 2008

Simple web2py and Elixir

Filed under: dev, elixir, orm, sqlalchemy, web2py — michaelangela @ 6:56 am

So I have a sample data set in a database created by web2py. After some experimentation I can access, modify and create data with Elixir/SQLAlchemy. Just some notes, like remember to encode web2py blobs with utf8 if you’re not going through web2py’s functions.

r=Recipe.query.all()
r[0].instructions=u’some text to submit’.encode(‘utf8’)
cb.session.flush()

And then something happend before this got pulished and it broke. Shame. Oh well…

March 10, 2008

Elixir or straight SQLAlchemy

Filed under: dev, elixir, orm, python, sqlalchemy — michaelangela @ 1:40 pm

Or “When not to use something that looks like it’s obviously going to make your life so much easier” 🙂

SQLAlchemy, Declarative layers, and the ORM ‘Problem’

On first glance, its pretty obvious that everyone should love Elixir vs the obviously more tedious SA approach of layout out your tables, then mapping them to the class objects. However, look at these two examples, and try to quickly answer the following questions:

  • How do you add a column to the many to many table to store an additional bit of info for the join?
  • Do they both enforce referential integrity?
  • How do you control whether SA is eager loading the relation? Can you restrict it to just one column of the relation?
  • What are the table names used?
  • How many tables are in your database?
  • Where do you change the id column name?
  • Which one is closer to the Zen of Python?

Create a free website or blog at WordPress.com.