Last active
August 21, 2018 11:55
-
-
Save ChrisTaylorDeveloper/8983d2f7113fc7733412a09ce2f766c5 to your computer and use it in GitHub Desktop.
Use Python to read csv to sqlite then write a new csv
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import csv, sqlite3, os | |
# input csv file something like this | |
""" | |
1,maths,smith,sally | |
9,history,blogs,john | |
4,art,roberts,mike | |
""" | |
# a handy function to drop and recreate a database | |
def newDatabase(): | |
if os.path.exists('db.sqlite'): | |
os.remove('db.sqlite') | |
try: | |
conn = sqlite3.connect('db.sqlite') | |
return conn | |
except Error as e: | |
print(e) | |
conn = newDatabase() | |
cur = conn.cursor() | |
sql = '''CREATE TABLE students ( | |
id INTEGER PRIMAY KEY, | |
first_name TEXT NOT NULL, | |
last_name TEXT NOT NULL | |
);''' | |
# create a table in our database | |
cur.execute(sql) | |
# populate table with a few of the columns in the csv file | |
with open('students_raw.csv') as csvfile: | |
readCSV = csv.reader(csvfile, delimiter=',') | |
for row in readCSV: | |
cur.execute('INSERT INTO students(id, first_name, last_name) VALUES(?,?,?)', [row[0], row[3], row[2]]) | |
conn.commit() | |
cur.execute('SELECT id, first_name, last_name FROM students') | |
rows = cur.fetchall() | |
conn.close() | |
# write the csv file | |
with open('out_file.csv', mode='w') as out_file: | |
out_writer = csv.writer(out_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL) | |
for r in rows: | |
out_writer.writerow( [r[0], r[1]] ) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment