Created
December 9, 2018 08:29
-
-
Save theasder/469a9597b6f489447a03acb49018d69c to your computer and use it in GitHub Desktop.
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 sqlite3 | |
import csv | |
import time | |
import sys | |
import logging | |
class CSV_to_SQL: | |
def write_to_database(self): | |
logging.info("Устанавливаю соединение с базой данных...") | |
start = time.time() | |
conn = sqlite3.connect(self.db_file) | |
logging.info("Соединение установлено успешно.") | |
cursor = conn.cursor() | |
cursor.execute("SELECT * FROM sqlite_master WHERE type='table' AND name='crime_calls';") | |
if not cursor.fetchall(): | |
query = open(self.schema_file, "r").read() | |
cursor.execute(query) | |
line_counter = 0 | |
try: | |
with open(self.csv_file, 'r') as csvfile: | |
logging.info("Начинаю запись в базу данных.") | |
for row in csv.reader(csvfile): | |
if row[0] != 'Crime Id': | |
row[0] = int(row[0]) | |
row[2] = row[2][:-9] | |
row[3] = row[3][:-9] | |
row[4] = row[4][:-9] | |
row[-3] = int(row[-3]) | |
cursor.execute("INSERT INTO crime_calls VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", row) | |
line_counter += 1 | |
conn.commit() | |
except IOError: | |
logging.error("Ошибка при открытии csv файла") | |
sys.exit() | |
end = time.time() | |
logging.info("Запись окончена:\n{num_lines} записей в базе данных\nВремя выполнения {t}с" | |
.format(num_lines=line_counter, t=end-start)) | |
conn.close() | |
logging.info("Закрываю базу данных.") | |
def __init__(self, csv_file, db_file, schema_file): | |
self.csv_file = csv_file | |
self.db_file = db_file | |
self.schema_file = schema_file | |
if __name__ == "__main__": | |
logging.basicConfig(filename="db.log", level=logging.INFO, format=f'{time.asctime()[4:]}: %(levelname)s: %(message)s') | |
CSV_to_SQL("police-department-calls-for-service.csv", "db.sqlite3", "schema.sql").write_to_database() |
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
Dec 9 11:15:58 2018: INFO: Устанавливаю соединение с базой данных... | |
Dec 9 11:15:58 2018: INFO: Соединение установлено успешно. | |
Dec 9 11:15:58 2018: INFO: Начинаю запись в базу данных. | |
Dec 9 11:15:58 2018: INFO: Запись окончена: | |
2025348 записей в базе данных | |
Время выполнения 40.51765012741089с | |
Dec 9 11:15:58 2018: INFO: Закрываю базу данных. |
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
Все использованные библиотеки являются встроенными в язык программированию Python. |
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
CREATE TABLE crime_calls( | |
'Crime_Id' INT UNIQUE, | |
'Original_Crime_Type_Name' VARCHAR, | |
'Report_Date' DATE, | |
'Call_Date' DATE, | |
'Offense_Date' DATE, | |
'Call_Time' TIME, | |
'Call_Date_Time' DATETIME, | |
'Disposition' VARCHAR, | |
'Address' VARCHAR(50), | |
'City' VARCHAR(30), | |
'State' VARCHAR(2), | |
'Agency_Id' INT, | |
'Address_Type' VARCHAR(30), | |
'Common_Location' VARCHAR(60) | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment