Python CSV для SQLite

Я "конвертирую" большой (~ 1,6 ГБ) CSV файл и вставляя определенные поля CSV в базу данных SQLite. По сути мой код выглядит следующим образом:

import csv, sqlite3

conn = sqlite3.connect( "path/to/file.db" )
conn.text_factory = str  #bugger 8-bit bytestrings
cur = conn.cur()
cur.execute('CREATE TABLE IF NOT EXISTS mytable (field2 VARCHAR, field4 VARCHAR)')

reader = csv.reader(open(filecsv.txt, "rb"))
for field1, field2, field3, field4, field5 in reader:
  cur.execute('INSERT OR IGNORE INTO mytable (field2, field4) VALUES (?,?)', (field2, field4))

Все работает так, как я ожидаю, за исключением... IT ПРИНИМАЕТ НЕВЕРОЯТНОЕ ВРЕМЯ ПРОЦЕССА. Я неправильно его кодирую? Есть ли лучший способ достичь более высокой производительности и выполнить то, что мне нужно (просто преобразовать несколько полей CSV в таблицу SQLite)?

** EDIT - я попытался напрямую импортировать csv в sqlite, как было предложено, но оказалось, что у моего файла есть запятые в полях (например, "My title, comma"). Это создает ошибки при импорте. Похоже, что слишком много из тех случаев, чтобы вручную редактировать файл...

любые другие мысли?? **

Ответ 1

Можно напрямую импортировать CSV:

sqlite> .separator ","
sqlite> .import filecsv.txt mytable

http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

Ответ 2

Крис - это операции с правом использования; разделите данные на куски, а затем сохраните их.

"... Если в транзакции уже не было транзакций, каждый оператор SQL имеет для него новую транзакцию, что очень дорого, поскольку для каждого оператора требуется повторное открытие, запись и закрытие файла журнала. Этого можно избежать путем объединения последовательностей операторов SQL с инструкциями BEGIN TRANSACTION и END TRANSACTION. Это ускорение также получается для операторов, которые не изменяют базу данных". - Источник: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

"... есть еще один трюк, который вы можете использовать для ускорения SQLite: транзакций. Всякий раз, когда вам приходится делать несколько записей в базе данных, поместите их внутри транзакции. Вместо того, чтобы записывать (и блокировать) файл каждый раз выдается запрос на запись, запись будет происходить только один раз, когда транзакция завершится". - Источник: Насколько масштабируемым является SQLite?

import csv, sqlite3, time

def chunks(data, rows=10000):
    """ Divides the data into 10000 rows each """

    for i in xrange(0, len(data), rows):
        yield data[i:i+rows]


if __name__ == "__main__":

    t = time.time()

    conn = sqlite3.connect( "path/to/file.db" )
    conn.text_factory = str  #bugger 8-bit bytestrings
    cur = conn.cur()
    cur.execute('CREATE TABLE IF NOT EXISTS mytable (field2 VARCHAR, field4 VARCHAR)')

    csvData = csv.reader(open(filecsv.txt, "rb"))

    divData = chunks(csvData) # divide into 10000 rows each

    for chunk in divData:
        cur.execute('BEGIN TRANSACTION')

        for field1, field2, field3, field4, field5 in chunk:
            cur.execute('INSERT OR IGNORE INTO mytable (field2, field4) VALUES (?,?)', (field2, field4))

        cur.execute('COMMIT')

    print "\n Time Taken: %.3f sec" % (time.time()-t)

Ответ 3

Как было сказано (Крис и Сэм), транзакции действительно улучшают производительность вставки вставки.

Пожалуйста, дайте мне порекомендовать другой вариант, чтобы использовать набор утилит Python для работы с CSV, csvkit.

Для установки:

pip install csvkit

Чтобы решить вашу проблему

csvsql --db sqlite:///path/to/file.db --insert --table mytable filecsv.txt

Ответ 4

Попробуйте использовать транзакции.

begin    
insert 50,000 rows    
commit

Это будет фиксировать данные периодически, а не один раз в строке.