richard-to
5/23/2014 - 7:10 AM

Random snippet to insert csv into sqlite3. Mainly just want to remember date time conversion to unix timestamp and associated issues with ti

Random snippet to insert csv into sqlite3. Mainly just want to remember date time conversion to unix timestamp and associated issues with timezones, ie strptime seems to use local timezone and then mktime will be utc

csv_to_sqlite3.py
content_copyfile_download
import csv
import time
import sqlite3
from datetime import datetime

readings = []
with open('data.csv', 'r') as csvfile:
    data = csv.reader(csvfile)
    next(data)
    for line in data:
        dt = datetime.strptime(line[0], "%m/%d/%Y %H:%M:%S")
        timestamp = int(time.mktime(dt.timetuple()))
        readings.append((int(line[1]), int(line[2]), line[3], timestamp))

conn = sqlite3.connect('db.sqlite3')
cursor = conn.cursor()
cursor.executemany(
    'INSERT INTO sensor_data (d, t, s, r) VALUES (?,?,?,?)', readings
)
conn.commit()
conn.close()