python - 如何使用 Transpose 和 pandas 将 xlsx excel 表中的数据推送到 sqlalchemy 数据库

Question

我正在尝试通过使用to_sql函数并在转置后使用数据框来推送 excel 文件dataframe...

这是我的代码看起来像

import pandas as pd
import os
import sqlalchemy

# MySQL Connection
MYSQL_USER = 'xxxxx'
MYSQL_PASSWORD = 'xxxxxxxx'
MYSQL_HOST_IP = '127.0.0.1'
MYSQL_PORT = 3306
MYSQL_DATABASE = 'xlsx_test_db'

# connect db
engine = sqlalchemy.create_engine('mysql+mysqlconnector://' + MYSQL_USER + ':' + MYSQL_PASSWORD + '@' + MYSQL_HOST_IP + ':' + str(
    MYSQL_PORT) + '/' + MYSQL_DATABASE, echo=False)
engine.connect()


mydir = (os.getcwd()).replace('\\', '/') + '/'

raw_lte = pd.read_excel(r'' + mydir + 'MNM_Rotterdam_5_Daily_Details-20191216081027.xlsx', sheet_name='raw_4G')

dft = raw_lte.T
dft.columns = dft.iloc[0]
dft = dft.iloc[1:]

# reading and insert one file at a time
for file in os.listdir('.'):
    # only process excels files
    file_basename, extension = file.split('.')
    if extension == 'xlsx':
        dft.to_sql(file_basename.lower(), con=engine, if_exists='replace')

这是错误

追溯

Traceback (most recent call last):
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1170 (42000): BLOB/TEXT column 'index' used in key specification without a key length

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:/Users/DELL/PycharmProjects/automateDB/swap.py", line 36, in <module>
    dft.to_sql(file_basename.lower(), con=engine, if_exists='replace')
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\core\generic.py", line 2532, in to_sql
    dtype=dtype, method=method)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 460, in to_sql
    chunksize=chunksize, dtype=dtype, method=method)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 1173, in to_sql
    table.create()
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 585, in create
    self._execute_create()
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 569, in _execute_create
    self.table.create()
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 778, in create
    checkfirst=checkfirst)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1940, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1549, in _run_visitor
    **kwargs).traverse_single(element)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\sql\ddl.py", line 796, in visit_table
    self.traverse_single(index)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\sql\ddl.py", line 823, in visit_index
    self.connection.execute(CreateIndex(index))
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\sql\ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1009, in _execute_ddl
    compiled
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1170 (42000): BLOB/TEXT column 'index' used in key specification without a key length [SQL: 'CREATE INDEX `ix_mnm_rotterdam_5_daily_details-20191216081027_index` ON `mnm_rotterdam_5_daily_details-20191216081027` (`index`)'] (Background on this error at: http://sqlalche.me/e/f405)

我认为excel格式有问题，但我不知道如何解决这个问题

注意：我尝试使用这个

raw_4G.to_sql(file_basename.lower(), con=engine, if_exists='replace')

安装的

dft.to_sql(file_basename.lower(), con=engine, if_exists='replace')

它有效，但它在运行时给了我错误

Traceback (most recent call last):
  File "C:/Users/DELL/PycharmProjects/automateDB/swap.py", line 34, in <module>
    file_basename, extension = file.split('.')
ValueError: not enough values to unpack (expected 2, got 1)

标签： pythonmysqlpandas