CODEWITHSUNDEEP
pythondataframeteradatateradata-sql-assistant
Analyst17
Analyst17

Reputation: 163

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

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

Upvotes: 3

Views: 15162

Answers (3)

Analyst17
Analyst17

Reputation: 163

create_engine('teradata://' +user+':'+ password + '@'+host+':1025/'+'/'+'?authentication=LDAP')

Adding both the host name and authentication to the connection string worked for me.

Upvotes: 0

Amir F
Amir F

Reputation: 2529

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

Upvotes: 1

chthonicdaemon
chthonicdaemon

Reputation: 19810

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)

Upvotes: 2

Related Questions