Import CSV into Sqlite3 using Python.(ProgrammingError: The current statement uses 26, and there are 0 supplied.)

Question

it's my first time working with sqlite3 and python together so I apologize for my noobness.

I'm a bit confused with how to write python code to correctly load certain columns from a csv file into SQLite3 database. There's a lot of columns in my file.

This is my code:

import sqlite3
import csv
connection = sqlite3.connect('revenue.db')
cursor = connection.cursor()

cursor.execute('DROP TABLE IF EXISTS TeamGameLogAllRegularSeason')
cursor.execute('CREATE TABLE TeamGameLogAllRegularSeason (Team_ID text, Game_ID text, GAME_DATE date, YEAR date, MATCHUP text, WL text, MIN integer, FGM integer, FGA integer, FG_PCT integer, FG3M integer,    FG3A integer, FG3_PCT integer, FTM integer, FTA integer, FT_PCT integer, OREB integer, DREB integer, REB integer, AST integer, STL integer, BLK integer, TOV integer, PF integer, PTS integer, VIDEO_AVAILABLE integer)')
connection.commit()

csvfile = open('C:\\Python27\\nbadata_script\\1_TeamGameLogAllRegularSeason.csv','rb')
creader = csv.reader(csvfile, delimiter=',',quotechar='"')

for t in creader:
    cursor.execute('INSERT INTO TeamGameLogAllRegularSeason VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',t)

csvfile.close()
connection.commit()
connection.close()

It works,table gets created but the error msg:

Traceback (most recent call last):
 File "C:\Python27\nbadata_script\csvsqlite_exe.py", line 16, in <module>
 cursor.execute('INSERT INTO TeamGameLogAllRegularSeason VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',t)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 26, and there are 0 supplied.

If anyone could help me, I would really appreciate it.

Have you created same number of columns in your table as much in your csv file, since your error message suggests this — Zubair Alam, Jun 29 '14 at 08:08
What is a line in the csv looking like? Are there empty lines? — pandita, Jun 29 '14 at 08:15
@ZubairAlam Yes, i created same number of columns in my table.I have checked a lot of time. Thanks for your respond. :) — Hsin Pang, Jun 29 '14 at 08:41
@pandita oh...yes, there are empty lines in my csv file. thanks for your respond that let me find out where the problem is. thanks you ! — Hsin Pang, Jun 29 '14 at 08:47

Martijn Pieters · Accepted Answer · 2014-06-29T08:46:14.997

0

You have an empty line in your CSV, and t in an empty list. Skip these:

for t in creader:
    if len(t) < 26:
        print "Skipping too short row {!r}".format(t)
        continue
    cursor.execute('INSERT INTO TeamGameLogAllRegularSeason VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',t)

This skips any row shorter than 26 elements, including empty rows, and prints out that it did so.

edited Jun 29 '14 at 08:46

answered Jun 29 '14 at 08:33

Martijn Pieters

1,048,767
296
4,058
3,343

Firstly let me say thank you for taking the time to solve my noobness question. It is a basic knowledge that i didn't figure out this. Thanks again ! :) – Hsin Pang Jun 29 '14 at 08:51

Import CSV into Sqlite3 using Python.(ProgrammingError: The current statement uses 26, and there are 0 supplied.)

1 Answers1