#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    12
    Rep Power
    0

    Sqlite3, csv, and python help


    Hi guys, 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 but I only need 10 of them.

    Right now, this is my code:
    Code:
    vehicles = csv.reader(open("vehicles.csv"))
    
    con = None
    
    try:
        con = sqlite.connect('vehicles.db')
        
        cur = con.cursor()   
        cur.execute("DROP TABLE IF EXISTS Vehicles") 
        cur.execute("CREATE TABLE Vehicles(Year INT, Make TEXT, Model TEXT, VClass TEXT, Cylinders INT, Displ REAL, Trany TEXT, City08 INT, Highway08 INT, Combo08 INT)")
        cur.executemany("INSERT INTO Customer VALUES(?,?,?,?,?,?,?,?,?,?)", vehicles)
        con.commit()
    
    finally:
        if con:
            con.close()
    If anyone could help me, I would really appreciate it.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    138
    Rep Power
    2
    Here you go. I haven't verified the results though.

    Code:
    import sqlite3 as sqlite
    import sys
    import csv
    
    vehicles = csv.DictReader(open("vehicles.csv"))
    con = None
    
    try:
        con = sqlite.connect('vehicles.db')
    
        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS Vehicles")
        cur.execute("CREATE TABLE Vehicles(Year INT, Make TEXT, Model TEXT, VClass TEXT, Cylinders INT, Displ REAL, Trany TEXT, City08 \
    INT, Highway08 INT, Combo08 INT)")
        con.commit()
    
        for row in vehicles:
            cur.execute("INSERT INTO Vehicles VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (row.get('year'),
                                                                                                            row.get('make'),
                                                                                                            row.get('model'),
                                                                                                            row.get('VClass'),
                                                                                                            row.get('cylinders'),
                                                                                                            row.get('displ'),
                                                                                                            row.get('trany'),
                                                                                                            row.get('city08'),
                                                                                                            row.get('highway08'),
                                                                                                            row.get('comb08')))
    
        con.commit()
    
    except sqlite.Error as e:
        raise
        sys.exit(1)
    
    finally:
        if con:
            con.close()
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    12
    Rep Power
    0
    wow, thanks so much for your help.
    Two questions though so please bare with my noobness again...

    1) There are rows in my file that have missing 'cylinders' data and some rows where 'displ' is 0. I need to remove those rows and not load them into the database, but I'm not quite sure how to go about doing that. I've googled but some of the code are really hard to understand. How should I go about doing this?

    2) I noticed in the code you provided that there's 2 con.commit(), one before the for statement and one after. Will it make a difference if I take out the one before? I don't think I need that one right?

    Thanks again for your help and fast reply.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    138
    Rep Power
    2
    Originally Posted by jellyworms
    wow, thanks so much for your help.
    Two questions though so please bare with my noobness again...

    1) There are rows in my file that have missing 'cylinders' data and some rows where 'displ' is 0. I need to remove those rows and not load them into the database, but I'm not quite sure how to go about doing that. I've googled but some of the code are really hard to understand. How should I go about doing this?
    I would load all the data into the database first, then remove the unwanted rows by using SQL commands. Read up on how to remove items in sqlite, there should be plenty of documentation.

    Originally Posted by jellyworms
    2) I noticed in the code you provided that there's 2 con.commit(), one before the for statement and one after. Will it make a difference if I take out the one before? I don't think I need that one right?
    I don't know, but it seemed a good idea to commit the database creation before adding data into it. Try removing one and see what happens!
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    12
    Rep Power
    0
    Okay, so I followed your advice and think I got it to work correctly but I'm not 100% sure.

    So I don't know exactly how to check the output to make sure that there was a difference between the code without this part and the code with this part added.

    I tried printing it out in command line with the sqlite.exe, but b/c the printout is so long/large, I can't see the whole thing.

    Any suggestions on how I can check? Or if you're able to tell me if my code is right?

    Thanks again and sorry for the trouble.
  10. #6
  11. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,837
    Rep Power
    480
    Make a test case for which you know both input and output. Run the test frequently as you modify the program. Install more tests as you enhance the program. Use those tests and keep it all up to date.
    [code]Code tags[/code] are essential for python code and Makefiles!
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    12
    Rep Power
    0
    Hi, I checked it individually and it's still showing all those rows...
    What am I doing wrong in my code?

    Also, if I were to just use DELETE in sqlite, for columns where I want to delete different things...would I restate DELETE multiple times or can it all go on one line?
    Does that make sense?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    138
    Rep Power
    2
    Originally Posted by jellyworms
    Also, if I were to just use DELETE in sqlite, for columns where I want to delete different things like empty cells, -, na's, etc. Would I restate DELETE multiple times or can it all go on one line?
    To delete all rows where cylinder has no value or where displ is 0, I would use two sql queries.

    Here's an example page:
    http://zetcode.com/db/sqlite/datamanipulation/

IMN logo majestic logo threadwatch logo seochat tools logo