Ggtcf

I know there are some other posts out there, but I was not able to find the specific question I had in mind.
I'm using US_baby_names csv file. and want to import this csv file line by line into sqlite3 as a table.

I'm able to create the table called storage.
I'm then trying to read lines in the csv file and put it into that table, but I must be doing something wrong.

import sqlite3 as sql
from sqlite3 import Error
import csv

def CreateConnection ( dbFileName ):
 try:
 conn = sql.connect(dbFileName)
 return conn
 except Error as e:
 print(e)

 return None

def CreateNew( dbConnection, new):
 sql = """INSERT INTO storage (dat, Id, Name, Year, group, subgroup, Count)
 VALUES (?,?,?,?,?,?,?)"""

 try:
 cursor = dbConnection.cursor()
 cursor.execute(sql, new)
 return cursor.lastrowid
 except Error as e:
 print(e)

def Main():
 database = "storage.db"
 dbConnection = CreateConnection(database)

 with open('storage.csv', 'rb') as fin:
 dr = csv.DictReader(fin)
 to_db = [(i['dat'], i['Id'], i['Name'], i['Year'], i['group'], i['subgroup'], i['Count']) 
 for i in dr]

 cursor.executemany(CreateNew(sql, to_db))

 dbConnection.close()

if __name__ == "__main__":
 Main()

I believe my cursor.executemany is wrong, but I'm not able to figure out what else to do..

Thanks

You are almost right with much of your code, but:

• in cursor.execute(sql, new) you are passing an iterable, new, to sqlite3.execute() (which requires a simple SQL statement), instead of sqlite3.executemany().


• Moreover, the result of CreateNew() is an integer, lastrowid, and you pass that result to executemany().


• You must use Connection.commit() to save the changes to the database, and Connection.rollback() to discard them.


• You must open the file for the csv.DictReader class as a text file, in r or rt mode.


• Finally, remember that sqlite3.Connection is a context manager, so you can use it in a with statement.

This should be your desired outcome:

import sqlite3 as sql
from sqlite3 import Error
import csv


def create_table(conn):
 sql = "CREATE TABLE IF NOT EXISTS baby_names("
 "dat TEXT,"
 "Id INTEGER PRIMARY KEY,"
 "Name TEXT NOT NULL,"
 "Year INTEGER NOT NULL,"
 "Gender TEXT NOT NULL,"
 "State TEXT NOT NULL,"
 "Count INTEGER)"

 conn.execute(sql)
 conn.execute("DELETE FROM baby_names")

def select_all(conn):
 for r in conn.execute("SELECT * FROM baby_names").fetchall():
 print(r)


def execute_sql_statement(conn, data):
 sql = "INSERT INTO baby_names "
 "(dat, Id, Name, Year, Gender, State, Count) "
 "VALUES (?,?,?,?,?,?,?)"

 try:
 cursor = conn.executemany(sql, data)
 except Error as e:
 print(e)
 conn.rollback()
 return None
 else:
 conn.commit()
 return cursor.lastrowid

def main(): 
 with sql.connect('baby_names.db') as conn, open('US_Baby_Names_right.csv', 'r') as fin:
 create_table(conn)

 dr = csv.DictReader(fin)
 data = [(i['dat'], i['Id'], i['Name'], i['Year'], i['Gender'], i['State'], i['Count']) for i in dr ]

 lastrowid = execute_sql_statement(conn, data)

 select_all(conn)


main()

I added a create_table() function just to test my code. I also made up a sample test file as follows:

dat,Id,Name,Year,Gender,State,Count
1,1,John,1998,M,Washington,2
2,2,Luke,2000,M,Arkansas,10
3,3,Carrie,1999,F,Texas,3

The output of the select_all() function is:

('1',1,'John',1998,'M','Washington',2)
('2',2,'Luke',2000,'M','Arkansas',10)
('3',3,'Carrie',1999,'F','Texas',3)