ошибка импорта из MSSQL в MySQL

Пытаюсь сделать импорт из базы MSSQL в базу MySQL. Соединился ровно, вижу все поля, но возникает ошибка:

  Traceback (most recent call last):
  File "src\pymssql.pyx", line 450, in pymssql.Cursor.execute
  File "src\_mssql.pyx", line 1070, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1101, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1234, in _mssql.MSSQLConnection.format_and_run_que
ry
  File "src\_mssql.pyx", line 1646, in _mssql.check_cancel_and_raise
  File "src\_mssql.pyx", line 1690, in _mssql.maybe_raise_MSSQLDatabaseException

_mssql.MSSQLDatabaseException: (207, b"Invalid column name 'store_id_ukm'.DB-Lib
 error message 20018, severity 16:\nGeneral SQL Server error: Check messages fro
m the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server e
rror: Check messages from the SQL Server\nDB-Lib error message 20018, severity 1
6:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error m
essage 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQ
L Server\n")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "manage.py", line 24, in <module>
    cur_ms.execute(q, tmp)
  File "src\pymssql.pyx", line 465, in pymssql.Cursor.execute
pymssql.ProgrammingError: (207, b"Invalid column name 'store_id_ukm'.DB-Lib erro
r message 20018, severity 16:\nGeneral SQL Server error: Check messages from the
 SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error:
 Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nG
eneral SQL Server error: Check messages from the SQL Server\nDB-Lib error messag
e 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Ser
ver\n")

Использую pymysql и pymssql. Такой код

cur_my = conn_my.cursor()
cur_ms = conn_ms.cursor()
cur_my.execute("SELECT * FROM `trm_in_users`")
for row in cur_my.fetchall():
    tmp = {'store_id_ukm':row['store_id'],'cashier_id':row['id'],'cashier_name':row['name'],'cashier_role':row['role_id'] }
    q = "INSERT INTO cashier_receipt_copy1 (store_id_ukm,cashier_id,cashier_name,cashier_role) VALUES (store_id_ukm,cashier_id,cashier_name,cashier_role)"
    cur_ms.execute(q, tmp)
conn_ms.commit()

когда делаю так

'store_id_ukm':row['`store_id`']  

выходит ошибка Key error

CREATE TABLE `trm_in_users` (
  `store_id` int(11) NOT NULL DEFAULT '0',
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(40) NOT NULL DEFAULT '',
  `user_inn` varchar(20) DEFAULT NULL,
  `password` varchar(48) NOT NULL DEFAULT '',
  `role_id` bigint(20) unsigned DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`store_id`,`id`),
  KEY `version` (`store_id`,`version`,`deleted`),
  KEY `role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

UPD Немного в другую сторону ушел, но выходит теперь другая ошибка

cur_my.execute("SELECT * FROM `trm_in_users`")
for row in cur_my.fetchall():
    cur_ms.execute("""INSERT INTO cashier_receipt_copy1 (store_id_ukm, cashier_id, cashier_name, cashier_role) VALUES (%d, %d, %s, %d) """, ('store_id', 'id', 'name', 'role_id'))
conn_ms.commit()

Ошибка

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    cur_ms.execute("""INSERT INTO cashier_receipt_copy1 (store_id_ukm, cashier_i
d, cashier_name, cashier_role) VALUES (%d, %d, %s, %d) """, ('store_id', 'id', '
name', 'role_id'))
  File "src\pymssql.pyx", line 468, in pymssql.Cursor.execute
pymssql.OperationalError: (245, b"Conversion failed when converting the nvarchar
 value 'store_id' to data type int.DB-Lib error message 20018, severity 16:\nGen
eral SQL Server error: Check messages from the SQL Server\n")

Ответы (1 шт):

Автор решения: reddyk

в общем через череду ошибок и советов на данном портале сделал)

cur_my.execute("SELECT * FROM `trm_in_users`")
for row in cur_my.fetchall():
    cur_ms.execute("""INSERT INTO cashier_receipt_copy1 (store_id_ukm, cashier_id, cashier_name, cashier_role) VALUES (%s, %s, %s, %s) """, (row['store_id'], row['id'], row['name'], row['role_id']))
conn_ms.commit()

может кому полезно будет

→ Ссылка