Python 3, Unicode column types and MetaData.reflect()

[Bao Niu]

I have a couple of questions regarding the Unicode/UnicodeText column type here.

1) I'm using Python 3, do I still need to explicitly use Unicode/UnicodeText type to define my columns? I thought Python 3 inherently supports unicode?

2) If I use MetaData.reflect(), it seems to me that all the columns are reflected to be NullType(), regardless how I set it in the database. How can I overcome this?
What I did is like this:

import sqlalchemy


eng = sqlalchemy.create_engine('sqlite:///foobar.db')
meta2 = sqlalchemy.MetaData(eng)
meta2.reflect()

meta2.tables['entries'].c['foo'].type

and I got NullType here even though I'm pretty sure I defined it to unicode in my database.

[Michael Bayer]

Bao Niu <niub...@gmail.com> wrote:

>
> I have a couple of questions regarding the Unicode/UnicodeText column type here.
>
> 1) I'm using Python 3, do I still need to explicitly use Unicode/UnicodeText type to define my columns? I thought Python 3 inherently supports unicode?

it does, but your database (often) does not. the unicode-ness defines the storage of the type on the DB side, not as much the conversion on the Python side which you can get with convert_unicode=True. Sqlite has native unicode on both py2k and py3k so not a big deal there.

>
> 2) If I use MetaData.reflect(), it seems to me that all the columns are reflected to be NullType(), regardless how I set it in the database. How can I overcome this?

SQLite doesn’t have real “types”, so up until version 0.9.2 we only reflect whats given for a fixed set of names. as of 0.9.3 we always resolve to an affinity so you should never get null type:

http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html#type-reflection

> What I did is like this:
> import sqlalchemy
>
>
> eng = sqlalchemy.create_engine('sqlite:///foobar.db')
> meta2 = sqlalchemy.MetaData(eng)
> meta2.reflect()
>
> meta2.tables['entries'].c['foo'].type
>
>
> and I got NullType here even though I'm pretty sure I defined it to unicode in my database.
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Bao Niu]

Thank you Michael.

I've read the documentation that you quoted very carefully but still not very sure in my case. You said that since 0.9.3 this issue has been resolved, but I'm using 0.9.8 on Ubuntu and still get NullTypes. If I'm using sqlalchemy MetaData.reflect() or automap extensions, together with SQLite database, I do not need to worry about these Unicode issues? Is there a newbie-friendly configuration step-by-step tutorial on this issue? Thanks.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/BrFN-qjkrqY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Michael Bayer]

the two cases where NullType() is still returned are if the type is defined as BLOB or as NULL in the SQLAlchemy database. change those and you won’t get any NullType. post your table defs here using:

sqlite> select sql from sqlite_master where name=‘<tablename>’;

[Bao Niu]

Here is what I got from sqlite> select sql from sqlite_master where name=‘<tablename>’;

CREATE TABLE "persons" ("ID" ,"名" ,"中间名" ,"姓" ,"类别" ,"生日" ,"主要电话" ,"住宅传真" ,"住宅地址国家地区" ,"住宅地址市县" ,"住宅地址街道" ,"住宅地址邮政编码" ,"住宅电话" ,"住宅电话2" ,"其他传真" ,"其他地址国家地区" ,"其他地址市县" ,"其他地址省市自治区" ,"其他地址街道" ,"其他地址邮政编码" ,"其他电话" ,"办公地点" ,"单位" ,"单位主要电话" ,"商务传真" ,"商务地址国家地区" ,"商务地址市县" ,"商务地址省市自治区" ,"商务地址街道" ,"商务地址邮政编码" ,"商务电话" ,"商务电话2" ,"寻呼机" ,"无绳电话" ,"电子邮件2地址" ,"电子邮件3地址" ,"电子邮件地址" ,"移动电话" ,"经理姓名" ,"网页" ,"职务" ,"部门" ,"配偶" ,"附注" )

Sorry for all non-ASCII characters, those are my native language

[Bao Niu]

Hi Michael, I don't have any problem having them all as NullType, but I just want to make sure the SQLite will deal with smoothly even when they are actually not NullType but Unicode/UnicodeText types. May I just go ahead and use NullType here instead of explicitly re-define all those columns as Unicode/UnicodeText? Please give a little hint here. Thanks.

[Michael Bayer]

Hi Bao -

Why don’t you just try it? As I’ve said, pysqlite returns unicode for strings already so the SQLAlchemy type isn’t really important except in some in-Python expression situations (like getting the + operator to do string concatenation).

Your table is returning everything as NullType because that table def doesn’t actually have any types in it.

Bao Niu <niub...@gmail.com> wrote:

> Hi Michael, I don't have any problem having them all as NullType, but I just want to make sure the SQLite will deal with smoothly even when they are actually not NullType but Unicode/UnicodeText types. May I just go ahead and use NullType here instead of explicitly re-define all those columns as Unicode/UnicodeText? Please give a little hint here. Thanks.
>
> On Wed, Jan 7, 2015 at 1:06 PM, Bao Niu <niub...@gmail.com> wrote:
> Here is what I got from sqlite> select sql from sqlite_master where name=‘<tablename>’;
>
> CREATE TABLE "persons" ("ID" ,"名" ,"中间名" ,"姓" ,"类别" ,"生日" ,"主要电话" ,"住宅传真" ,"住宅地址国家地区" ,"住宅地址市县" ,"住宅地址街道" ,"住宅地址邮政编码" ,"住宅电话" ,"住宅电话2" ,"其他传真" ,"其他地址国家地区" ,"其他地址市县" ,"其他地址省市自治区" ,"其他地址街道" ,"其他地址邮政编码" ,"其他电话" ,"办公地点" ,"单位" ,"单位主要电话" ,"商务传真" ,"商务地址国家地区" ,"商务地址市县" ,"商务地址省市自治区" ,"商务地址街道" ,"商务地址邮政编码" ,"商务电话" ,"商务电话2" ,"寻呼机" ,"无绳电话" ,"电子邮件2地址" ,"电子邮件3地址" ,"电子邮件地址" ,"移动电话" ,"经理姓名" ,"网页" ,"职务" ,"部门" ,"配偶" ,"附注" )
>
>

> Sorry for all non-ASCII characters, those are my native language<32B.gif>

[Bao Niu]

Thanks Michael. I should form a habit of trying it. It wasn't because I didn't want to, it was because I always fear that if I'm not careful with data I could ruin the precious data at one keystroke. So I kind of always ask before doing, lol.