Из csv в mysql таблицу PYTHON?

Question

[mr-ZA]

Всех приветствую, возникла проблема при импорте строк из .csv файла с записями в mysql с помощью python скрипта. Объясните мне пожалуйста что не так, исправьте!

Таблица mysql:

CREATE TABLE crimes (
 `Crime id` INT PRIMARY KEY,
 `Original Crime Type Name` VARCHAR(30),
 `Call Date` DATETIME,
 `Offense Date` DATETIME,
 `Call Time` TIME,
 `Call Date Time` DATETIME,
 Disposition VARCHAR(10),
 Address VARCHAR(40),
 City VARCHAR(13),
 State VARCHAR(2),
 `Agency Id` INT,
 `Address Type` VARCHAR(16),
 `Common Location` VARCHAR(20));

Скрипт питон

import MySQLdb
import csv

db = MySQLdb.connect(host="192.168.1.2",
                     port=3306,
                     user="non-root-boy",
                     passwd="123456",
                     db="sfpd")
print ("\nConnection to DataBase established..\n")
cur = db.cursor()

f = open('/home/decoy/Downloads/police-department-calls-for-service.csv')
csv_f = csv.reader(f)                               #parsed -> return in csv_f

for row in csv_f:
    cur.execute('INSERT INTO sfpd(`Crime Id`, `Original Crime Type Name`, \
                   `Report Date`, `Call Date`, `Offense Date`, `Call Time`, \
                   `Call Date Time`, `Disposition`, `Address`, `City`, `State`, `Agency Id`, \
                   `Address Type`, `Common Location`)' 'VALUES("%d", "%s", "%s", "%s", "%s", "%s", \
                   "%s", "%s", "%s", "%s", "%d", "%s")', row)

    # close the connection to the database.
    cur.close()

    print ("Done")

db.close()

Ошибка

(Connection to DataBase established..

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 238, in execute
    query = query % args
TypeError: %d format: a number is required, not str

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/decoy/Projects/Python/sfpdBASE_insert.py", line 21, in <module>
    "%s", "%s", "%s", "%s", "%d", "%s")', row)
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 240, in execute
    self.errorhandler(self, ProgrammingError, str(m))
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 52, in defaulterrorhandler
    raise errorclass(errorvalue)
_mysql_exceptions.ProgrammingError: %d format: a number is required, not str

Process finished with exit code 1)

.csv строка с которой ошибки нет:

160922294	Passing Call	2016-04-01T00:00:00	2016-04-01T00:00:00	2016-04-01T00:00:00	15:41	2016-04-01T15:41:00	HAN	Haight St Corridor		CA	1	Geo-Override

.CSV строка номер 238 на которую он ругается

160913103	Encampment	2016-03-31T00:00:00	2016-03-31T00:00:00	2016-03-31T00:00:00	18:57	2016-03-31T18:57:00	ADV	2700 Block Of Folsom St	San Francisco	CA	1	Premise Address

Answer 1

[Сергей Горностаев]

Во-первых, вы закрываете курсор после первой же итерации. Поэтому у вас вообще не должно обрабатываться больше одной строки. Во-вторых, можно завернуть проблемный код в обработчик исключений и убедиться, что числовое поле действительно содержит ожидаемое значение и не содержит нечисловых символов:

import MySQLdb
import csv

csv_file = '/home/decoy/Downloads/police-department-calls-for-service.csv'
db_props = {
    'host': '192.168.1.2',
    'port': 3306,
    'user': 'non-root-boy',
    'passwd': '123456',
    'db': 'sfpd'
}
query = ''''INSERT INTO sfpd(`Crime Id`, `Original Crime Type Name`,
                   `Report Date`, `Call Date`, `Offense Date`, `Call Time`,
                   `Call Date Time`, `Disposition`, `Address`, `City`, `State`, `Agency Id`,
                   `Address Type`, `Common Location`) VALUES("%d", "%s", "%s", "%s", "%s", "%s",
                   "%s", "%s", "%s", "%s", "%d", "%s")'''

with open(csv_file) as fh:
    csv_f = csv.reader(fh)
    with MySQLdb.connect(**db_props) as cur:
        for row in csv_f:
            try:
                cur.execute(query, row)
            except TypeError as exc:
                field_val = row[-2]
                if isinstance(field_val, str):
                    print(''.join(r'\x{0:02x}'.format(ord(c)) if c.isspace() or not c.isprintable() else c for c in field_val))
                else:
                    raise
print ("Done")

И, в-третьих, непонятно почему предпоследнее поле таблицы БД имеет тип varchar, а вставить вы пытаетесь число.

Comments

[mr-ZA]

Спасибо все поправил, появились вопросы:

Почему '''INSERT ... ''' в тройных кавычках?

with MySQLdb.connect(**db_props) as cur: -- две здездочки что это?

field_val = row[-2] -- тут совсем мрак), объясните этот обработчик?
if isinstance(field_val, str):
print(''.join(r'\x{0:02x}'.format(ord(c)) if c.isspace() or not c.isprintable() else c for c in field_val))
else:

[Сергей Горностаев]

mr-ZA,

Почему '''INSERT ... ''' в тройных кавычках?

В тройные кавычки заключается строковый литерал, который можно писать в несколько строк.

with MySQLdb.connect(**db_props) as cur: -- две здездочки что это?

Передача в вызов функции словаря как именованные параметры.

field_val = row[-2]

Получение предпоследнего элемента списка.

if isinstance(field_val, str):

Проверка, что он имеет строковый тип.

print(''.join(r'\x{0:02x}'.format(ord(c)) if c.isspace() or not c.isprintable() else c for c in field_val))

Вывод field_val с отображением непечатных и пробельных символов в виде их hex-кодов. Чтобы было проще заметить лишние символы в поле.

[mr-ZA]

Сергей Горностаев, Проблема с передачей данных сохранилась после исправления

query = ''''INSERT INTO sfpd(`Crime Id`, `Original Crime Type Name`,
                   `Call Date`, `Offense Date`, `Call Time`,
                   `Call Date Time`, `Disposition`, `Address`, `City`, `State`, `Agency Id`,
                   `Address Type`, `Common Location`)' 'VALUES("%d", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s",
                   "%d", "%s", "%s")'''

Traceback (most recent call last):
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 238, in execute
query = query % args
TypeError: not all arguments converted during string formatting

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/home/decoy/Projects/Python/sfpdBASE_insert.py", line 23, in
cur.execute(query, row)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 240, in execute
self.errorhandler(self, ProgrammingError, str(m))
File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 52, in defaulterrorhandler
raise errorclass(errorvalue)
_mysql_exceptions.ProgrammingError: not all arguments converted during string formatting

[Сергей Горностаев]

mr-ZA, это уже другая проблема. Ошибка "not all arguments converted during string formatting" означает, что подстановочных символов больше, чем передано аргументов. Может в какой-то из строк есть не все поля?

[mr-ZA]

да, местами пробелы во всех полях, как это поправить?

Если кстати сократить так, ведь id то везде есть, то ошибка та-же

query = ''''INSERT INTO sfpd(`Crime Id`)' 'VALUES("%d")'''

[Сергей Горностаев]

mr-ZA, теперь заметил в вашем запросе лишние кавычки. Исправил ответ, проверьте.

[mr-ZA]

так тоже не работает

query = ''''INSERT INTO sfpd(`Crime Id`) VALUES("%d")'''

TypeError: %d format: a number is required, not str

[Сергей Горностаев]

mr-ZA, замените %d на %s

[mr-ZA]

Traceback (most recent call last):
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 238, in execute
query = query % args
TypeError: not all arguments converted during string formatting

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/home/decoy/Projects/Python/sfpdBASE_insert.py", line 23, in
cur.execute(query, row)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 240, in execute
self.errorhandler(self, ProgrammingError, str(m))
File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 52, in defaulterrorhandler
raise errorclass(errorvalue)
_mysql_exceptions.ProgrammingError: not all arguments converted during string formatting

Process finished with exit code 1

[Сергей Горностаев]

mr-ZA, не знаю, что подсказать по проблеме разного количества колонок в разных строках. Разве что пропускать неполноценные строки:

for row in csv_f:
    if len(row) < 12:
        continue
    ...

[mr-ZA]

Это тоже не работает..

query = 'INSERT INTO sfpd(`Crime Id`) VALUES("%s")'

with open(csv_file) as fh:
    csv_f = csv.reader(fh)
    with MySQLdb.connect(**db) as cur:
        for row in csv_f:
            try:
                cur.execute(query, row)
                if len(row) < 12:
                    continue

query = 'INSERT INTO sfpd(`Crime Id`) VALUES("%s")'

with open(csv_file) as fh:
csv_f = csv.reader(fh)
with MySQLdb.connect(**db) as cur:
for row in csv_f:
try:
cur.execute(query, row)
if len(row) < 12:
continue

[Сергей Горностаев]

mr-ZA, проверка должна быть в самом начале итерации. Именно так, как я показал в своём комментарии.

[mr-ZA]

Поправил, Все равно висит ошибка добавления в базу

Connection to DataBase established..

Traceback (most recent call last):
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 238, in execute
query = query % args
TypeError: not all arguments converted during string formatting

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/home/decoy/Projects/Python/sfpdBASE_insert.py", line 25, in
cur.execute(query, row)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 240, in execute
self.errorhandler(self, ProgrammingError, str(m))
File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 52, in defaulterrorhandler
raise errorclass(errorvalue)
_mysql_exceptions.ProgrammingError: not all arguments converted during string formatting

Answer 2

[vitovt]

Их CSV можно напрямую в MySQL без каких либо скриптов, одним запросом

Comments

[Denis]

Каким?