Python: CSV Directory to Sqlite3 Database

Question

I am trying to convert a folder of CSV files into a Sqlite3 Database with python. Initially I was able to convert the data inside a single csv file I had made local to the folder with database and python script files. I have now made a copy of the entire CSV folder local and I am now trying to iterate through each file individually with a for loop, obviously to save myself from the trouble of copy and pasting each file name('xxxxx.csv') 400-500 times. I am finding however that to be the most difficult part of this process, and I am not having any luck finding anyone who has done this before even though I cannot imagine that is true, maybe I am just asking the question incorrectly. Here is what I have as of now. I have tried a few different attempts with the for loop in different places or with different syntax. Any advice is much appreciated, I feel like the answer is so simple but I am at a loss at this point.

import os
import csv
import sqlite3 


connection = sqlite3.connect('Frame_Data.db')
c = connection.cursor()


for part_file in os.listdir('C:\Frame_DataBase\Frame_Data_TEST_DB'):
  if part_file.endswith(".csv"):
   with open(part_file) as file:
    records = 0
    for row in file:
      c.execute("""INSERT INTO    FrmTbl(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI) 
              VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"""
             ,row.split(","))
      connection.commit()
      records += 1 
      connection.close()

You should close the connection after the for-loops, not in one of them. — Michael Butscher, Mar 29 '23 at 20:24
Also, if the csv file has headers, probably you want to skip the header row when iterating over the files — buran, Mar 29 '23 at 20:33
Right now you should be getting an error - elaborate what is the exact problem — buran, Mar 29 '23 at 20:34
The error is on line 11, I have tried as "with open(part_file) as file:" and "with open(part_file,'r') as file:" as well it is throwing a FileNotFoundError:[Errno 2] No such file or directory: "xxxxxxx.csv" and this is the first file in the folder. — Jonathan Susman, Mar 29 '23 at 20:54

score 0 · Answer 1 · answered Mar 29 '23 at 21:31

import os
import csv
import sqlite3 


connection = sqlite3.connect('Frame_Data.db')
c = connection.cursor()

**dir = 'Frame_Data_TEST_DB'
for part_file in os.listdir(dir):
 if part_file.endswith(".csv"):
  with open(os.path.join(dir,part_file),'r') as file:**
    records = 0
    for row in file:
     c.execute("""INSERT INTO FrmTbl(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI) 
              VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"""
             ,row.split(","))
     connection.commit()
     records += 1 
connection.close()

This was my main problem, I was initially calling a file path instead of the local directory, I still don't know how to connect to a file path that isn't local, in the event you aren't able to copy one and make it local. — Jonathan Susman, Mar 29 '23 at 21:36

Python: CSV Directory to Sqlite3 Database

1 Answers1