时间戳

首页 > 解决方案 > 有没有办法将熊猫数据框存储到 Teradata 表中

python - 有没有办法将熊猫数据框存储到 Teradata 表中

问题描述

我创建了一个 pandas 数据框“df”，并尝试使用 Teradata-SQL 助手将其存储在“表”中。

连接字符串 -

conn = pyodbc.connect(
         "DRIVER=Teradata;DBCNAME=tdprod;Authentication=LDAP;UID=" + username + ";PWD=" + password + ";QUIETMODE=YES",
        autocommit=True, unicode_results=True)

cursor = conn.cursor().execute(sql)

尝试使用： df.to_sql('table', con =conn)

这行不通。

有没有更简单的方法将数据框存储到表中。

任何帮助表示赞赏。

谢谢。

Traceback (most recent call last):

 File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\sqlalchemy\engine\base.py", line 2158, in _wrap_pool_connect
return fn()
 File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\sqlalchemy\pool.py", line 410, in connect
return _ConnectionFairy._checkout(self, self._threadconns)
 File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\sqlalchemy\pool.py", line 788, in _checkout
fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\sqlalchemy\pool.py", line 529, in checkout
rec = pool._do_get()
  File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\sqlalchemy\pool.py", line 1096, in _do_get
c = self._create_connection()
  File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\sqlalchemy\pool.py", line 347, in _create_connection
return _ConnectionRecord(self)
  File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\sqlalchemy\pool.py", line 474, in __init__
self.__connect(first_connect_check=True)
 File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\sqlalchemy\pool.py", line 671, in __connect
connection = pool._invoke_creator(self)
 File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\sqlalchemy\engine\strategies.py", line 106, in connect
 return dialect.connect(*cargs, **cparams)
 File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\sqlalchemy\engine\default.py", line 412, in connect
return self.dbapi.connect(*cargs, **cparams)
  File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\teradata\tdodbc.py", line 454, in __init__
checkStatus(rc, hDbc=self.hDbc, method="SQLDriverConnectW")
 File "C:\Users\tripata\PycharmProjects\NLP\venv\lib\site-packages\teradata\tdodbc.py", line 231, in checkStatus
raise DatabaseError(i[2], u"[{}] {}".format(i[0], msg), i[0])
teradata.api.DatabaseError: (8017, '[28000] [Teradata][ODBC Teradata Driver][Teradata Database] The UserId, Password or Account is invalid. , [Teradata][ODBC Teradata Driver][Teradata Database] The UserId, Password or Account is invalid. ')

标签： pythondataframeteradatateradata-sql-assistant

解决方案

从文档中to_sql：

Parameters
----------
name : string
    Name of SQL table.
con : sqlalchemy.engine.Engine or sqlite3.Connection
    Using SQLAlchemy makes it possible to use any DB supported by that
    library. Legacy support is provided for sqlite3.Connection objects.

您可以看到您需要 sqlalchemy或sqlite3，但不需要 pyodbc。

您需要以下内容来为 Teradata 创建引擎：

from sqlalchemy import create_engine

engine = create_engine(f'teradata://{username}:{password}@tdprod:22/')

然后你会像使用它一样

df.to_sql('table', engine)

推荐阅读