Last active
August 29, 2015 14:01
-
-
Save richard-to/42ba55487a1d9f2a924a to your computer and use it in GitHub Desktop.
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
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 | |
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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment