pyodbc utf-8

[Nebros]

Hello community

 

i have a next problem. i have connected with pyodbc to a mssql db. i read values out of it, all works, but i have problems with "ä" "ö" and "ü".

on my html.page i made this into the header:

 

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

 

on all my pages this umlauts works, but not the values of the pyodbc...

when i only give out the row, i become something like this: Hansj\xf6rg ,but it have to be this: Hansjörg

when i print this value out into a table, the field keeps empty.

 

it must be a problem of pyodbc, but i dont know how to fix it. i tryed something like this:

 

views.py --------------------------------------------------------

def kundendaten(request):

    ret = request.POST
    form = ret['kunde']

    conn = pyodbc.connect('DRIVER={SQL Server};SERVER=MAURITIUS;DATABASE=baan5c;UID=portal;PWD=P0rtalReader')
    cursor = conn.cursor()
    cursor.execute("SELECT x.t_name, x.t_user, y.t_mail FROM tttaad200000 as x, tttcmf200000 as y WHERE (x.t_name = y.t_name) AND (x.t_user = '%s')"%form)
    rows = unicode(cursor.fetchall(), 'utf-8')

    now = datetime.datetime.now()

    return render_to_response("kundendaten.html", { 'rows': rows, 'current_date': now, 'form': form}, context_instance=RequestContext(request))

---------------------------------------------------------------------

but the page gave this error:

TypeError at /kundendaten/

coercing to Unicode: need string or buffer, list found

 

i dont know how i can fix my problem... what i use:

win7 32bit

python 2.7

django 1.4.1

pyodbc 3.0.6

 

can someone halp me to fix this problem?

[Nebros]

 edit: in the db the values are correct and i cant change there something...

[Daniel Roseman]

On Wednesday, 28 November 2012 13:17:39 UTC, Nebros wrote:

 edit: in the db the values are correct and i cant change there something...

Meanwhile we still don't know why you won't use Django's proper ORM functionality, which is perfectly compatible with MSSQL and ODBC.

--

DR. 

[Nebros]

Its compatible with mysql, but it's not working with mssql. it gave only errors with the correct settings... thats why i have to make own settings for the odbc.

[Nebros]

Ich habe einen Holzweg beschritten und ich werde ihn auch zu Ende gehen... :)
I have taken a wrong path and I will follow it through to the end ... :)

[Nebros]

Can that be, that my computer have the problem and not python itself?

[Nebros]

I use win7 32bit... dont know what you need to know aswell. ^^

[Nebros]

It was not possible to find a way by myself. thats maybe cause im new in python / django.

i know there is one who can helps...

 

it will be very friendly for a usable response. :)

 

Am Dienstag, 27. November 2012 09:49:37 UTC+1 schrieb Nebros:

[Chris Cogdon]

Perhaps you can help the django team figure out why the ORM doesn't work with MSSQL? I understand that its not technically supported, but there must be SOME people working on it.

If you're going straight to pyodbc, then this is likeyl to be a python/pyodbc issue, and not django. So... you're not going to get the level of help here that you might from the pyodbc user's group. We'll try, but no guarantees.

For starters, you should read the docs on pyodbc. The way you're handling the execute statement is very bad, and will open you to SQL injection attacks. It might also be the reason it doesnt work.

bad:

cursor.execute("SELECT x.t_name, x.t_user, y.t_mail FROM tttaad200000 as x, tttcmf200000 as y WHERE (x.t_name = y.t_name) AND (x.t_user = '%s')"%form)

Good:

cursor.execute("SELECT x.t_name, x.t_user, y.t_mail FROM tttaad200000 as x, tttcmf200000 as y WHERE (x.t_name = y.t_name) AND (x.t_user = %s)", ( form, ) )

Note that we are no longer using the python % operator. %s here is specific to db-api2, but please find out what pyodbc requires. it could be %s, could be ?, or something else.

The 2nd parameter to "execute" must be a sequence. So if there's just one element, make sure you use ( elem, ) or [ elem ]

In this form, the execute statement will do all the proper quoting for you.

Now, the error that you're getting is likely in the rows = unicode(cursor.fetchall(), 'utf-8') line

cursor.fetcall() returns a list of tuples. you cant unicode convert all that at once. you have to do it on the individual elements.

[Chris Cogdon]

You're totally correct. This is why I said "but please find out what pyodbc uses" :)

sqlite3 uses %s

On Wednesday, December 5, 2012 3:46:01 PM UTC-8, Dennis Lee Bieber wrote:

On Wed, 5 Dec 2012 13:13:31 -0800 (PST), Chris Cogdon <ch...@cogdon.org>
declaimed the following in gmane.comp.python.django.user:

 
> Good:
>
> cursor.execute("SELECT x.t_name, x.t_user, y.t_mail FROM tttaad200000 as x,
> tttcmf200000 as y WHERE (x.t_name = y.t_name) AND (x.t_user = %s)", ( form,
> ) )
>
> Note that we are no longer using the python % operator. %s here is specific
> to db-api2, but please find out what pyodbc requires. it could be %s, could
> be ?, or something else.

        DB-API2 does NOT mandate using %s -- that is just one of something
like four or five permitted styles; the style choice is determined by
the actual adapter used.

        From PEP 249:

>         paramstyle
>          
>             String constant stating the type of parameter marker
>             formatting expected by the interface. Possible values are
>             [2]:
>
>                 'qmark'         Question mark style,
>                                 e.g. '...WHERE name=?'
>                 'numeric'       Numeric, positional style,
>                                 e.g. '...WHERE name=:1'
>                 'named'         Named style,
>                                 e.g. '...WHERE name=:name'
>                 'format'        ANSI C printf format codes,
>                                 e.g. '...WHERE name=%s'
>                 'pyformat'      Python extended format codes,
>                                 e.g. '...WHERE name=%(name)s'
>

        MySQLdb uses %s (MySQL did not have prepared statements prior to v5;
everything was sent as fully formatted statements, and MySQLdb uses
Python string interpolation to generate the statements -- after passing
each parameter through a function that escapes special characters and
wraps SQL quotes around it). [probably works with "pyformat" too]

        SQLite3 uses ?
--
        Wulfraed                 Dennis Lee Bieber         AF6VN
        wlf...@ix.netcom.com    HTTP://wlfraed.home.netcom.com/