[pysqlite] [APSW 3.5.9-r1] Building INSERT from regex?

Gilles <[email protected]> Sat, 21 Jun 2008 12:00:20 +0200
Newsgroups gmane.comp.python.db.pysqlite.user
Message-ID <[email protected]>
Hello

	I need to parse a tab-delimited text file, extract each column, and 
put the result into an SQLite database. Python's CVS module doesn't 
seem to support Unicode, so I have to do this using regex.

The problem I'm having, is how to build the INSERT by iterating 
through the array built after running the regex on each line, knowing 
that the second column can be empty, ie. must be set to NULL:

#Some text and digits 123<TAB>(This column can be empty)<TAB>123<CRLF>
p = re.compile('^(.+?)\t(.*?)\t(\d+)$')

for line in textlines:
	m = p.search(line)
	if m:
		sql = "INSERT INTO mytable (col1,col2,col3) VALUES (?,?,?)"
		#TypeError: 2nd parameter must be iterable
		#cursor.executemany(sql, m.groups)

Someone mentioned this trick but doesn't this turn NULL columns into 
"NULL" or "" instead?

		sql = "INSERT INTO mytable (col1, col2) VALUES ('%s','%s')"%tuple(
			(c, "NULL")[c == ''] for c in m.groups()
			)

Thank you for any tip.