transfer textfile contents into database python

Question

I'm a little new to python/sqlite. I'm trying to transfer the contents of a textile into a database. The textile is formatted like this:

hello hello hello
hello hello hello hello hello
hello hello hello hello

I parse the textile into a 2d list with each line as its own tuple i.e. [hello, hello, hello]. I would like to transfer this list into a database where each element is an attribute and each tuple is its own row - with a few blank attributes since each line is of different length.

I keep the error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied

I'm not sure why this is. I thought I addressed this with my query_string variable. If anyone could help me, that would be great. My code is below:

import sqlite3
ins = open( "foo.txt", "r" ) 
parseTable = []
for line in ins:
 row = line.rstrip().split(',') 
 parseTable.append(row)
#print parseTable
conn = sqlite3.connect('sample.db')
c = conn.cursor()
c.execute('''CREATE TABLE sample (Item_0 TEXT, Item_1 TEXT, Item_2 TEXT, Item_3 TEXT, Item_4 TEXT)''')
query_string = 'INSERT INTO sample VALUES (?, ?, ?, ?, ?)'
c.executemany(query_string, parseTable)
conn.commit()

• Martijn Pieters: Please don't just insert the solution into your question; it didn't clarify the problem and makes my answer look incorrect to boot.
• Martijn Pieters: And I understood your question; that's what my answer below does.
• David: @Martijn Pieters I didn't intend that to be a solution. Sorry I screwed it up.
• Martijn Pieters: You also have a mistake in generating var_string variable, but luckily you don't actually use that string. You want to use len(parseTable[0]) there, the length of a row, not the number of rows.

Answer 1

You need to add in some extra None values to pad the rows:

for line in ins:
 row = line.split()[:5]
 row.extend([None] * (5 - len(row)))
 parseTable.append(row)

This splits your sample data on spaces (not commas), caps rows to at most 5 elements, then adds None values if the length is below 4 elements.

The None values are translated into NULLs for the missing columns for those rows.

Demo:

>>> import sqlite3
>>> ins = '''\
... hello hello hello
... hello hello hello hello hello
... hello hello hello hello
... '''.splitlines()
>>> parseTable = []
>>> for line in ins:
... row = line.split()[:5]
... row.extend([None] * (5 - len(row)))
... parseTable.append(row)
... 
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
>>> c.execute('''CREATE TABLE sample (Item_0 TEXT, Item_1 TEXT, Item_2 TEXT, Item_3 TEXT, Item_4 TEXT)''')
<sqlite3.Cursor object at 0x1083d8960>
>>> query_string = 'INSERT INTO sample VALUES (?, ?, ?, ?, ?)'
>>> c.executemany(query_string, parseTable)
<sqlite3.Cursor object at 0x1083d8960>
>>> conn.commit()
>>> c.execute('SELECT * FROM sample')
<sqlite3.Cursor object at 0x1083d8960>
>>> for row in c:
... print row
... 
(u'hello', u'hello', u'hello', None, None)
(u'hello', u'hello', u'hello', u'hello', u'hello')
(u'hello', u'hello', u'hello', u'hello', None)

• David: Thanks. I made the change but now every line is a single attribute. I want to make each individual element a different attribute. So how do I do this?
• Martijn Pieters: I'm not sure that I follow you; each row contains the values from your file's lines, plus enough None values to pad each row to 5 values. The term 'attribute' usually refers to something different in Python, those are values on objects instead (someobject.attribute).
• David: Ok let me make an edit above. I mean 'attribute' as in an individual field in any database.
• Martijn Pieters: @dave: those are generally called 'columns'
• David: Ok, Sorry about my bad communication. What I mean is every line is one single column now. I'd like multiple columns.