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

write dataframe to teradata table
dataframe insert into table
python teradata fastload
pandas to_sql
sqlalchemy-teradata
pandas to table
insert dataframe into sql python
pandas create table from dataframe

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

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)

Save Python data-frame as Table in Teradata, One option is to use fastterdata , specifically the load_table function: load_table(abs_path, df, table_name, env, db, connector = "teradata", clear_table=True) Loads a pandas dataframe from memory into teradata via the optimized fastload functionality. Hi all, I am using the Teradata python module to read transaction data from Teradata into a Pandas data frame for analysis. Now that I have done my analysis, I need to load the new data frame (rules) back into Teradata. The new data frame does not

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

Python: Save Pandas DataFrame to Teradata, With this approach, we don't need to create the table in advance. We can use to_sql function of Pandas dataframe to save the data to Teradata. save it into another storage such as RDBMS (Teradata, SQL Server, MySQL)  The first line is imports the Teradata and pandas library that is used to fetch/store the data from the Teradata database. UdaExec is a framework that handles the configuration and logging the Teradata application. Since we mentioned the logConsole=False, it will not log to the console so that our print statement is easier to read.

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

Insert Pandas Data Frame into Teradata DB, The new data frame does not need to be altered further and only needs to be inserted into Teradata the way it is. I appreciate any help you can  updated use DataFrame.to_feather() and pd.read_feather() to store data in the R-compatible feather binary format that is super fast (in my hands, slightly faster than pandas.to_pickle() on numeric data and much faster on string data).

How can I write a pandas dataframe to a database table using , Iterating over rows and doing "INSERT INTO MYTABLE " every time is very slow​. Pandas dataframes have a to_sql() method, but it requires the use of a  The cars table will be used to store the cars information from the DataFrame. Step 3: Get from Pandas DataFrame to SQL. You can use the following syntax to get from pandas DataFrame to SQL: df.to_sql('CARS', conn, if_exists='replace', index = False) Where CARS is the table name created in step 2. Here is the full Python code to get from pandas

saving a python DataFrame to Table, So if I have a dataframe from any source - forget that it is from an Excel file, maybe it could be a .CSV file, how do I have it to a table in Teradata  I'm trying to scrape the data from the coins catalog. There is one of the pages. I need to scrape this data into Dataframe So far I have this code: import bs4 as bs import urllib.request import

Tutorial: Inserting Records and DataFrames Into a SQL Database, Let's dive into how we can actually use SQL to insert data into a database. In SQL, we use the INSERT command to add records/rows into table data. we're often using pandas , and we've often got our data stored as a pandas DataFrame. Return Pandas dataframe from PostgreSQL query with sqlalchemy Hot Network Questions Is it bad etiquette to try to make an opponent lose on time when a position is clearly drawn?

Comments
  • 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.
  • Could you update the question with the full stack trace? It's hard to debug with only the error name.
  • Looks like the username or password is wrong. Just play with the sqlalchemy connect string until it works.
  • 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.
  • How do you add the datalab and table name together using df.to_sql?