notebook » Blog Archive » Cedict Sqlite Database

Cedict Sqlite Database

April 27th, 2008 by benny

Since my last post (3 hours ago), I felt like the data in Unihan was great, but basic. Most Chinese “words” consist of more than one character. For example, the word “adult” is “大人”. And what do you know, there’s a database for that too! It’s called CEDict (wikipedia entry) and surprise surprise, its a flat file like the Unihan database is. This time, it’s a bit nicer to use:
Traditional Simplified [pin1 yin1] /English equivalent 1/equivalent 2/ 中國中国 [Zhong1 guo2] /China/Middle Kingdom/

I’ve written a(nother) quick python script that’ll convert this file into a(nother) sqlite database. Enjoy!

#! /usr/bin/python
#
#  A script to parse the CEDICT file into a sqlite
#  database.
#
#	Author:	Benny Wong <bwong.net>
#	Date:	2008.04.27
 
import re 
from pysqlite2 import dbapi2 as sqlite
 
columns = ['Traditional', 'Simplified', 'Pinyin', 'Definition']
 
f = open('cedict_ts.u8', 'r')
p = re.compile('(.*) (.*) \[(.*)\] /(.*)/')
 
conn = sqlite.connect('Cedict.sqlite')
cursor = conn.cursor()
 
cursor.execute("DROP TABLE IF EXISTS Cedict")
cursor.execute("CREATE TABLE Cedict (" + ", ".join([i + " TEXT" for i in columns]) + ")")
 
for line in f:
	if not line.startswith('#'):
		tokens = p.match(line)
		traditional = tokens.group(1)
		simplified = tokens.group(2)
		pinyin = tokens.group(3)
		definition = tokens.group(4).replace('/', '|')
 
		cursor.execute("INSERT INTO Cedict (" + ', '.join(columns) + \
			") VALUES (?, ?, ?, ?)", \
			[traditional, simplified, pinyin, definition]);
 
f.close()
 
conn.commit()

PS: <3 Regex

This entry was posted on Sunday, April 27th, 2008 at 2:03 pm and is filed under chinese, nerd. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Viewing 2 Comments

David Rutkowski 6 months ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

Hi Benny,

One point: There's a constraint on CC_CEDIT, where the combination of traditional characters, simplified characters, and pronunciation for a given record is guaranteed to be unique. This is because Chinese words used as common nouns my also be used as proper nouns. The pinyin for proper nouns has the appropriate letters capitalized, and allows one to lookup proper nouns, as well as count the number of proper nouns in the database, a metric which could come in handy if one does much reading about local events in China or Taiwan, or even personalities on the world stage.

I have also developed a Unihan.txt to SQLlite conversion system. I use the tag as the table name, the data point as the key, and the third field as the content. Since the data point is the primary key in all of the 88 tables, the indexing is done automatically when the table is populated.

To keep things simple, my system only depends on gawk and the sqlite3 interpretor, so all the necessary development files could exist in a single directory. I just started my project on Google Code, but it can be accessed at http://code.google.com/p/unihan-sqlite-3-database. I plan to have more documentation and SQL scripts available as I find the time to develop and post.

The lack of comments in your Python Unihan script makes it a bit hard to follow.It looks like your are creating a single table, unihan, which has a columns for various values associated with a datapoint. This works if you know which records contain which values, but is not very versatile, and doesn't follow the principles of database design.

Since some columns hold as few as a tens of values, and others hold many thousands, it seem to make better sense to "unflatten" the database, and store each set of values in its own table.The SQL is a bit more complex, but avoids constructions like "select character, definition from unihan where definition != '';" If a number of columns are involved, the amount of logic to test for (sets) of empty values associated with (sets) of real values can grow exponentially. That is the logic behind the normalization of databases.

So far, my testing has shown good response when joining a number of tables. I hope to write some scripts to produce the SQL for joining any number of tables. Of course, views could be used to simplify things. I just need to determine which views would be most useful for the average user.

If you can explain what your Python program is doing, I'd be interested in knowing. Stay in touch. I'm interested Xul as well, and may be able to by a better than average beta tester for your project. Take Care.

David Rutkowski

reply edit reblog flag

Jean-Philippe Valois 2 months ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

What to say for such a nice gift? You are my newest hero. Not only does your script fulfill my need, it also allows me to learn a bit of Python with something that is a current interest (learning Chinese and building software tools to help out). If ever I make any such tool public, you'll be on my "admirers" list. :)

Thanks so much.
Jean-Philippe Valois

reply edit reblog flag

Trackbacks

(Trackback URL)

notebook » Blog Archive » On Missed Opporunities
May 7, 2008 at 8:38 pm

[...] What I’m referring to is some of the side projects I’ve been working on (exhibit a and exhibit b). ...

Cedict Sqlite Database

Add New Comment

Viewing 2 Comments

Add New Comment

Trackbacks