Re: [pysqlite] Encoding for connect to SqLite3

P-Y Delens wrote:
> Gerhard H=E4ring wrote:
>> Hello,
>>
>> I'll cc the mailing list. Please direct future questions there.
>>   =

> *1. Users list*
> I cc to [email protected]
> I see "list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org" on your mail. Is it
> the same list, should I subscribe to this second list

The second list ist the one to use. Sourceforge hasn't been used for
this project since 4 years.

>> PY-Delens wrote:
>>   =

>>> Bonjour,
>>>
>>> I fail to display correctly accentuated characters from aSqLite 3 table
>>> (stored in UTF-8 after abandoned UTF-16 scenario).
>>> I try to add parameter _/encoding /_to thre PySqlite connect function,
>>> but this is refused: [...]
>>>     =

>>
>> No surprise. pysqlite doesn't have an encoding parameter anywhere. To
>> store text that is not UTF8 encoded, you can achieve that using your
>> custom text_factory (See
>> http://oss.itsystementwicklung.de/download/pysqlite/doc/sqlite3.html).
>>   =

> *2. documentation*
> I was mislead by the 'pysqlite2 documentation: ' on page
> http://oss.itsystementwicklung.de/trac/pysqlite/
> (out of date)

This documentation isn't out of date, even though though the version
number in the docs said "2.5.0" until a minute ago.

> Is there more doc available, than the one pointed by you, here above ?
> *
> 3. 'encode' parameter in connect  function*
> (sorry: not 'encoding')
> I found this in documentation, perhaps out of date (pySqlite Usage
> manual for pySqLite 2.4.0). I discover via your mail a fresher
> documentation.

As far as I remember (and I should ;-) there never was a parameter
"encode" in pysqlite2.

> But I was confused  because when asking for
>     >>> dir (pysqlite2.dbapi2.connect.__module__)
> I get well 'encode' attribute in the dir.

If you have a function or method, then __module__ is a string describing
where it lives in the module hierarchy. Being a string, it has a
"encode" method, as well as a zfill method etc.

> But test is refusing the keyword
> =

>     >>> from pysqlite2 import dbapi2 as Sqlite
>     >>> cx =3D
>     Sqlite.connect('Z:\\72_BasesDeDonnees_GestionBureau\\SqLite-CAO\\SqLi=
te-CAO-1b.db',
>     encode=3D"UTF-8")
>     Traceback (most recent call last):
>       File "<pyshell#16>", line 1, in <module>
>         cx =3D
>     Sqlite.connect('Z:\\72_BasesDeDonnees_GestionBureau\\SqLite-CAO\\SqLi=
te-CAO-1b.db',
>     encode=3D"UTF-8")
>     TypeError: 'encode' is an invalid keyword argument for this function
>     >>>
> =

> As indeed the doc you pointed to doesn't mention 'encode' any more,
> where is this feature gone up? Or why wasit decided not necessary any
> more? (because SqLite 3 supposed alqways serving UTF-8?)

There is and there never was an encode parameter in pysqlite2. It may be
that older versions allowed arbitrary parameters but discarded them ...
I just checked. pysqlite version 1.x had this feature. You could set an
an encoding per connection. This was because most of pysqlite 1.x was
adapted from the PostgreSQL adapter I contribued to named pyPgSQL.

pysqlite2 was designed with Unicode strings in mind as the preferred
text object. Later a few escape methods were added that made it possible
to customize encoding behaviour if you need it for compatibility reasons
or whatever.

>>> I'm alo confused on the libs supposed to be called in PySqlite; probably
>>> one is nested into the other:
>>> pysqlite2
>>> Sqlite3 [...]
>>>     =

>>
>> pysqlite2 is the Python wrapper for the SQLite 3 library, which is the
>> embedded database engine. If you use Python 2.5 or later, a version of
>> pysqlite2 is available under the module name "sqlite3" as well.
>>   =

> *4 Libs*
> _*4a.*_ Do you mean that C:\Python25\Lib\sqlite3 is in the Python
> distribution (not added at pySQLite istall), self-sufficient as engine?
> and that C:\Python25\Lib\site-packages\pysqlite2 is making use of
> sqLiter3 lib ?

No. SQLite is written in C and normally available as a shared library.
On Windows, you have a SQLITE3.DLL somewhere. In the Python DLLs folder
IIRC. Never versions of pysqlite2 link statically on Windows, so there
is no DLL any longer.

Now, as I said before: Since Python version 2.5.0, a pysqlite2 is
included within Python itself. But the module isn't called
"pysqlite2.dbapi2", but more simply "sqlite3" instead. This was done on
purpose so that both the externally maintained and the version included
in Python itself can be installed alongside. Without nastay and brittle
hacks like what was once done with the xml module in Python.

The best way to use pysqlite is thus:

try:
    from pysqlite2 import dbapi2 as sqlite3
except ImportError:
    import sqlite3

This will import the externally maintained version, and if not found,
fall back to the one included with Python.
> =

> *_4b._* ? Is there a difference between
>         import sqlite3
>         from pysqlite2 import dbapi2 as Sqlite =

>         from pysqlite2 import _sqlite as Sqlite =

> What is the best way of coding?  =


Don't use modules with leading underscores unless you know exactly what
you're doing. See recipe above for my suggested way of importing it.

>>   =

>>> How should I do? How to handle the encoding problem at:
>>>     - Python module level
>>>     - SQL string level
>>>     - DATA in SqLite file (I was advised to go back to UTF-8, by people
>>> of DbManager  tools)
>>>     ?
>>>     =

>>
>> I'd recommend to use UTF8 only. If you don't, you'll need to use
>> bytestrings in your encoding as parameters to execute(). And set a
>> text_factory that converts them back. If you just want the same
>> bytestrings back (instead of, say, unicode string objects), you can just
>> use text_factory=3Dstr.
>>   =

> *5. Global comprehension of coding in global processing flow*
> This is my flow:
> see STEP 4 : there is the main question, I think.
> I give all my steps as to make sure you get the context/goal.
> So that I won't miss interferences (or no interference ...) between steps.
> =

> *
> * 	*Step- Context - File concerned
> * 	*coding
> * 	*How controlled
> * 	*RMQ, question to support
> *
> 1
> 	SQLite 3
> 	UTF-8 	Option at Db creation time

There is no option here for controlling encodings. SQLite (the C engine)
expects UTF8 strings when the UTF8 API is used, like pysqlite does. It
doesn't check the strings against encodings though. So you *may* use
something else if you know what you're doing.

I'll try to answer the following questions, but my recommendation is
that you don't "fight the system", but just use the defaults. Which are
well-chosen ;-)
 	=

> 2
> 	_Python code_ for  accessing Db,
> creating a Python 'table' as a 2Dim List
> (code in my /FileLib.py/)
> 	cp1252 	header =3D
> #-*- coding: cp1252 -*-
> 	cp1252 is oftrn dised for workng on Windows Europe.
> Should I change this here?

Doesn't really matter for SQLite usage.
> 3
> 	SqlStatementString  in Python code
> 	? cp1252 	? inherits from step 2 ?
> 	Should I encode  u'SqlString', or use text-Factory?

These are two different things. Please read the chapter
http://oss.itsystementwicklung.de/download/pysqlite/doc/sqlite3.html#conver=
ting-sqlite-values-to-custom-python-types

text_factory is for converting SQLite TEXT data to your preferred string
object.

> I think I may just leave palin stering?
> Or should I encode  the SqlStatementString to UTF-8?
> *4
> * 	*Datas as accessed/retrieved by pySqlite
> * 	*? UTF-8
> or
> Unicode
> * 	*Controlled by step1 above
> * 	*The list /tuple items are displayed with the u'XXX' so I understand
> at this stage I got a Unicode encoded string but from a wrongly
> interpreted character.
> I didn't explicitely ask for u'XXX'.
> =3D=3D> What to do?
>  . ask for u'XXX', or not?
>  . ask for u'XXX', but after decoding the Slite3 data in a proper way?
> =

> Please keep in mind the final format ISO-8859-1 (see step 8 below)
> *
> 5
> 	Datas as stored in lists by pySqlite 	? UTF-8 	Influenced by ?
> 	- Influenced by step1 (the Db)?
> - Influenced by step2 (the Python header)?
> - Influenced by ... ?
> result is with such problems:
> /u'Ar100810EV_$G-O_Ma?nPrmnt' /    instead of
> /u'Ar100810EV_$G-O_Ma_=E7on_nPrmnt'/
> /u'Ar20----__-$PAREMENT FA?DES ET ?ANCH?T/
> instead of /...FADES ET =E9ANCH=E9iT=E9s.../



> 6
> 	Cheetah template :
> /FileTPL.tmpl/
> 	cp1252 	# -*- coding: cp1252 -*-
> 	coherent with step 2 (Python moduled called by Template)

I'd fill the template with Unicode strings. Then the rest doesn't matter.

> 7
> 	=

> Cheetah compiled/filled servlet or generator :
>  /FileTPL.py/
> this code is calling upon / FileLib.py/
> (step 2 above)
> 	=

> 	=

> 	N.B :
> 8
> 	XHTML generated page
> 	ISO-8859-1
> 	- xml declaration
> - meta content
> 	- global poption for a big static WebSite
> - I would hardly imagine to move from this option.
> - Should I?

I don't know. I personally try to use UTF8 everywhere, as this is the
best way to get a roundtrip from and to Unicode. In any case it's a
matter of configuring the template engine.

I suggest again to send Unicode stirngs to the template engine.

As a rule of tumb:

- use Unicode throughout your application
- only when it comes to representation, decide upon a encoding and
encode to it; to this as late as possible (in the template engine)

With the roundtrip of data from and to the browser via forms and to the
database and back UTF8 is the most sensible choice, because you only
need to configure the encoding once then. Everywhere else it's the
default. At least it worked like this with Django for me.

-- Gerhard