How to skip over columns in SQLite using Python?

Question

I'm very new to python and I have a basic python script where I need to insert values from a CSV file into an SQLite database using Python. I've done that part successfully, however, I need to not include columns 26, 27 and 28 out of 29. Whenever I try to do this, it just skips over the entire row, however I only want it to skip that specific column in general. I tried to not include the columns when creating the table originally however, I would get an error each time when I ran the script

This is the code I'm working with with regards to this process:

import sys, sqlite, csv

  try: 
    cx = sqlite.connect("database")

except sqlite.Error, errmsg:
    print "Could not open the database file: " + str(errmsg)
    sys.exit()

#create the table        
try:
    cu = cx.cursor()
    cu.execute('DROP TABLE IF EXISTS table_name')

    sql = """ CREATE TABLE table_name(col1 TEXT, col2 TEXT, col3 TEXT, col4 TEXT, col5 TEXT, col6 TEXT, col7 TEXT, col8 TEXT, col9 TEXT, col10 TEXT, col11 TEXT, col12 TEXT, col13 TEXT, col14 TEXT, col15 TEXT, col16 TEXT, col17 TEXT, col18 TEXT, col19 TEXT, col20 TEXT, col21 TEXT, col22 TEXT, col23 TEXT, col24 TEXT, col25 TEXT, col26 TEXT, col27 TEXT, col28 TEXT, col29 TEXT); """

    cu.execute(sql)
    cx.commit()
except sqlite.Error, errmsg:
    print "Could not execute the query: " + str(errmsg)
    sys.exit()

#Load the CSV file into the csv reader


fin = open("test.csv", "rb")
creader = csv.reader(fin, delimiter=',')

# Interate through the CSV Reader, inserting each value into the database
    # NEW REVISION
  sql_insert = "INSERT INTO table_name VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,,,,%s);"    


      for row in creader:
         cu.execute(sql_insert, row)
         print row

fin.close()
cx.commit()
cx.close()

senderle · Accepted Answer · 2012-02-02 22:26:45Z

2

The way csv.reader() works is that it returns lists, each of which represents a row. So if you have a csv file that looks like this:

col1, col2, col3, col4
a, b, c, d
e, f, g, h
i, j, k, l

The corresponding contents of csv.reader() look like this:

>>> with open('blah.csv', 'rb') as csv_file:
...     cr = csv.reader(csv_file)
...     for row in cr:
...         print row
... 
['col1', ' col2', ' col3', ' col4']
['a', ' b', ' c', ' d']
['e', ' f', ' g', ' h']
['i', ' j', ' k', ' l']

What's nice about lists is that you can slice them.

>>> with open('blah.csv', 'rb') as csv_file:
...     cr = csv.reader(csv_file)
...     for row in cr:
...         print row[0:3]
... 
['col1', ' col2', ' col3']
['a', ' b', ' c']
['e', ' f', ' g']
['i', ' j', ' k']

That's the most obvious way to skip the last few columns of a csv table. You can also concatenate slices of lists:

>>> with open('blah.csv', 'rb') as csv_file:
...     cr = csv.reader(csv_file)
...     for row in cr:
...         print row[0:2] + row[3:]
... 
['col1', ' col2', ' col4']
['a', ' b', ' d']
['e', ' f', ' h']
['i', ' j', ' l']

Putting it all together:

>>> with open('blah.csv', 'rb') as csv_file:
...     sql_insert = 'INSERT INTO table_name VALUES(?, ?, ?)'
...     for row in csv.reader(csv_file):
...         cu.execute(sql_insert, row[0:2] + row[3:])
... 
<sqlite3.Cursor object at 0x100535298>
<sqlite3.Cursor object at 0x100535298>
<sqlite3.Cursor object at 0x100535298>
<sqlite3.Cursor object at 0x100535298>
>>> cu.execute('SELECT * FROM table_name').fetchall()
[(u'col1', u' col2', u' col4'), (u'a', u' b', u' d'), (u'e', u' f', u' h'), (u'i', u' j', u' l')]

edited Feb 2, 2012 at 22:26

answered Feb 2, 2012 at 21:58

senderle

152k36 gold badges218 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dan Goldsmith Over a year ago

Slicing and Concatenating, Much more elegant than my approach.

user1186173 Over a year ago

Thanks for the reply, I've tried that but I need to get to skip the rows being inserted into the database, so i tried to do your suggestion but it did not work unfortunately.

senderle Over a year ago

@user1186173, I don't really understand this comment. Do you need to skip rows or do you need to skip columns? Your question is only about skipping columns. But skipping rows is also easy; before inserting a row into the table, test the row for some condition and if it's true, continue -- as in if condition: continue. This will cause the for loop to skip to the next row.

user1186173 Over a year ago

I'm sorry I mean't columns, that was my fault. Thanks for your suggestion anyways

user1186173 · Accepted Answer · 2012-02-03 00:26:49Z

1

You are iterating over the output of csv.reader() as if they are columns. That is incorrect. They are in fact whole rows. It should be more like:

for row in reader:
    # Now to skip the mentioned columns just don't use those in your sql insert statement
    # for example column 25 is row[24]
    # Instead, put a blank in your SQL
    row[25]=""
    row[26]=""
    row[27]=""

    sql_insert = "INSERT INTO table_name   VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"  
    cursor.execute(sql_insert , row)

insert instead of %s for those columns

edited Feb 3, 2012 at 0:26

user1186173

5654 gold badges7 silver badges27 bronze badges

answered Feb 2, 2012 at 21:51

Sid

7,6412 gold badges31 silver badges42 bronze badges

10 Comments

user1186173 Over a year ago

When I try to do that, I get an Error, "TypeError: not all arguments converted during string formatting" Thanks for responding so quickly

Sid Over a year ago

Instead of the empty commas can you put spaces like ...%s,"","","","",%s... i.e. spaces for strings and maybe 0 for ints or floats?

Sid Over a year ago

Problem is also with your sql insertion. You need to use questionmarks instead of %s. See this: stackoverflow.com/questions/2092757/…

user1186173 Over a year ago

I've tried that before as well and it would give me the same error

Sid Over a year ago

If this doesn't work the only thing I can think is that cursor.execute() requires a tuple for values with the insert rather than a list. Although I'd expect it would be any iterable.

|

Dan Goldsmith · Accepted Answer · 2012-02-02 22:06:29Z

1

If I remember correctly csv.reader returns lines, rather than columns.

Anyway a quick and dirty way (probably inefficient) of doing it would be to use the built in pop function. This should remove the items from the list returned. To keep it simple, do the pop in reverse order (highest array index first) otherwise it can get confusing as the array gets smaller and elements positions move

for line in creader:
   line.pop(28)
   line.pop(27)
   line.pop(26)

cu.execute(sql_insert, line)

edited Feb 2, 2012 at 22:06

answered Feb 2, 2012 at 21:56

Dan Goldsmith

4242 silver badges5 bronze badges

2 Comments

user1186173 Over a year ago

Thanks, but I need to remove columns 26,27,28 and keep 29, so that would remove one too many columns..

user1186173 Over a year ago

it almost worked! But now i'm getting an error that there's not enough parameters

Collectives™ on Stack Overflow

How to skip over columns in SQLite using Python?

3 Answers 3

4 Comments

10 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related