CODEWITHSUNDEEP
pythonsqlite
Dave
Dave

Reputation: 11

Python: Populate SQLite-Table using data from a text file - probably unsupported type

The problem is that I can't get the data from the text file to write to the database.
The text file is organized line by line ex:

John Doe 012-345-6789

As you can see, I've split the text and have all the categories straightened out.
But keep getting errors like InterfaceError: Error binding parameter 0 - probably un supported type or something about writing 47 entries to one field.

Thank you very much in advance.

import sqlite3

conn = sqlite3.connect('phoneNumbers.db')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS People ')
cur.execute('CREATE TABLE People (firstname TEXT, lastname TEXT, phonenumber TEXT)')


########

file = open('phoneNumbers.txt', 'r')

try:
    file = open('phoneNumbers.txt', 'r')
except:
    print "File not found"

data = file.read()
data = data.split()

for line in data:
    first = data[0::3]
    last = data [1::3]
    num = data [2::3]


cur.execute('INSERT INTO People (firstname, lastname, phonenumber) VALUES (?, ?, ?)', (first, last, num))
conn.commit()

cur.execute('SELECT firstname, lastname, phonenumber FROM People')
for row in cur:
    print row

conn.close()

Upvotes: 1

Views: 3691

Answers (1)

JDurstberger
JDurstberger

Reputation: 4255

There are a few problems with your code:

  1. you are using the name file for your file, this is a built in name and you should rename it
  2. you are opening the file twice, once before the try once in the try
  3. Your actual problem:

first, last and num are lists and execute cannot use lists as parameters. There is an executeManybut I do not like it in this case.

EDIT
Even slicker is reading line by line and using the with command.

with open('phoneNumbers.txt', 'r') as f:
    for line in f:
        data = line.split()
        cur.execute('INSERT INTO People (firstname, lastname, phonenumber) VALUES (?, ?, ?)', (data[0], data[1], data[2]))
        if 'str' in line:
            break

Original I would use a for with an index and then execute the INSERT in the loop like that.

try:
    numbersFile = open('phoneNumbers.txt', 'r')
except:
    print "File not found"

data = numbersFile.read()
data = data.split()

for i in range(0, len(data), 3):
    first = data[i]
    last = data[i+1]
    num = data[i+2]
    cur.execute('INSERT INTO People (firstname, lastname, phonenumber) VALUES (?, ?, ?)', (first, last, num))

conn.commit()

Upvotes: 3

Related Questions