Is there a way to store pandas dataframe to a Teradata table

Question

I have created a pandas data-frame 'df' and I am trying to store it in a 'table' using Teradata-SQL assistant.

Connection string -

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)

Tried using: df.to_sql('table', con =conn)

This doesn't work.

Is there an easier way to store a dataframe into a table.

Any help is appreciated.

Thanks.

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. ')

score 2 · Answer 1 · answered Jan 11 '19 at 15:51

2

From the docs for 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.

You can see you need sqlalchemy or sqlite3, but not pyodbc.

You need the following to create an engine for Teradata:

from sqlalchemy import create_engine

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

You would then use it like

df.to_sql('table', engine)

answered Jan 11 '19 at 15:51

chthonicdaemon

16,668
1
39
59

Solution doesnt seem to run. It throws an error message - sqlalchemy.exc.DatabaseError: (teradata.api.DatabaseError) . For some reason, it says UserId, Password, or Account invalid. I cross checked my account and it works fine. – Analyst17 Jan 11 '19 at 17:10
Could you update the question with the full stack trace? It's hard to debug with only the error name. – chthonicdaemon Jan 11 '19 at 17:13
Looks like the username or password is wrong. Just play with the sqlalchemy connect string until it works. – chthonicdaemon Jan 11 '19 at 17:28
So, I played around a bit and this seems to be working- create_engine('teradata://' +user+':'+ password + '@'+host+':1025/'+'/'+'?authentication=LDAP'). l guess we need both the host name and authentication in the connection string. – Analyst17 Jan 11 '19 at 17:47
1

How do you add the datalab and table name together using df.to_sql? – Jonathan Lam Feb 26 '20 at 15:25

score 1 · Answer 2 · answered Apr 10 '19 at 14:19

I've done some digging and this solution does the job and does it quickly - using the python teradata module:

import teradata
import numpy as np
import pandas as pd


num_of_chunks = 100  #breaking the data into chunks is optional - use if you have many rows or would like to view status updates

query = 'insert into SomeDB.SomeTeraDataTable'
df = someDataframe

#set host, user, password params
host,username,password = 'hostName_or_IPaddress','username', 'password'

#connet to DB using UdaExec
udaExec = teradata.UdaExec (appName="IMC", version="1.0", logConsole=False)


with udaExec.connect(method="odbc",system=host, username=username,
                            password=password, driver="Teradata") as connect:


    df_chunks = np.array_split(df, num_of_chunks)

    for i,_ in enumerate(df_chunks):

        data = [tuple(x) for x in df_chunks[i].to_records(index=False)]

        connect.executemany(query, data,batch=True)

solution based on: this stackoverflow post

score 0 · Answer 3 · answered Jan 11 '19 at 17:50

0

create_engine('teradata://' +user+':'+ password + '@'+host+':1025/'+'/'+'?authentication=LDAP') Adding both the host name and authentication to the connection string worked for me.

answered Jan 11 '19 at 17:50

Analyst17

153
2
11

Is there a way to store pandas dataframe to a Teradata table

3 Answers3