By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,001 Members | 1,176 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,001 IT Pros & Developers. It's quick & easy.

Problems Writing =?ISO-8859-1?Q?=A3?= (pound sterling) To MS SQLServer using pymssql

P: n/a
Hi.

I'm relatively new to python so please be gentle :)

I'm trying to write a £ symbol to an MS SQL server using pymsssql . This
works but when selecting the data back (e.g. using SQL management
studio) the £ symbol is replaced with £ (latin capital letter A with
circumflex).

I can reproduce it like so:
>>con = pymssql.connect(host='testdb',user='testuser',pass word='password',database='test')
sql = "insert into table_1 values ('£')"
cur.execute(sql)
con.commit()
sql = "insert into table_1 values ('1')"
cur.execute(sql)
con.commit()
sql = "select * from table_1"
cur.execute(sql)
cur.fetchall()
[('\xc2\xa3',), ('1',)]

If I insert a £ symbol in using SQL management studio then select it back from Python I get this:

[('\xc2\xa3',), ('1',), ('\xa3',)]

If I look in SQL management studio it says this:

£

for the inserts I do through Python/pymssql.

Does anyone have any ideas whats happening and how to fix it?

Thanks

--
Darren Mansell <da************@opengi.co.uk>
Nov 17 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Nov 17, 2:52*pm, Darren Mansell <darren.mans...@opengi.co.uk>
wrote:
Hi.

I'm relatively new to python so please be gentle :)

I'm trying to write a symbol to an MS SQL server using pymsssql . This
works but when selecting the data back (e.g. using SQL management
studio) the symbol is replaced with £ (latin capital letter A with
circumflex).

I can reproduce it like so:
>con = pymssql.connect(host='testdb',user='testuser',pass word='password',database='test')
sql = "insert into table_1 values ('')"
cur.execute(sql)
con.commit()
sql = "insert into table_1 values ('1')"
cur.execute(sql)
con.commit()
sql = "select * from table_1"
cur.execute(sql)
cur.fetchall()

[('\xc2\xa3',), ('1',)]

If I insert a symbol in using SQL management studio then select it back from Python I get this:

[('\xc2\xa3',), ('1',), ('\xa3',)]

If I look in SQL management studio it says this:

£

for the inserts I do through Python/pymssql.

Does anyone have any ideas whats happening and how to fix it?

Thanks
I recommend that you work with Unicode wherever possible. If pymssql
can't handle Unicode then use UTF-8 when talking to it. The result
should look something like this:
>>con = pymssql.connect(host='testdb',user='testuser',pass word='password',database='test')
sql = u"insert into table_1 values ('')".encode("utf-8")
cur.execute(sql)
con.commit()
sql = u"insert into table_1 values ('1')".encode("utf-8")
cur.execute(sql)
con.commit()
sql = u"select * from table_1".encode("utf-8")
cur.execute(sql)
result = cur.fetchall()
result
[('\xc2\xa3',), ('1',)]

You'll then need to decode from UTF-8:
>>[tuple(field.decode("utf-8") for field in row) for row in result]
[(u'\xa3',), (u'1',)]
>>print u'\xa3'

Nov 17 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.