0

I can't figure out the right ODBC string I need to pass to the create engine statement.

This works

import pyodbc
import pandas as pd

cnxn=pyodbc.connect('DRIVER=/opt/teradata/client/ODBC_64/lib/tdata.so;DBCName=Server;UID=UN;PWD=PW;Database=myDB')

query = "select top 10 * from TABLE"
df = pd.read_sql(query,cnxn)

This does not work

import urllib
import sqlalchemy
params = urllib.parse.quote_plus('DRIVER=/opt/teradata/client/ODBC_64/lib/tdata.so;DBCName=Server;UID=UN;PWD=PW;Database=myDB')

engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

query = "select top 10 * from TABLE"

df = pd.read_sql_query(query, engine)

I can get the pyodbc connection to work but not the sqlalchemy connection. Any help would be appreciated.

I get this error:

InterfaceError: (pyodbc.InterfaceError) ('IM001', '[IM001] [unixODBC][Driver Manager]Driver does not support this function (0) (SQLGetInfo)')
Community
  • 1
  • 1
Nrwparadise
  • 1
  • 1
  • 1
  • 1
    [SQLAlchemy](https://docs.sqlalchemy.org/en/13/dialects/index.html) is limited to a few RDBMS's (Oracle, SQL Server, Postgres, MySQL, SQLite, etc.). You are attempting an SQL Server dialect on Teradata connection: `mssql+pyodbc`. You will need to incorporate an [external dialect for Teradata](https://www.google.com/search?q=Sqlalchemy+teradata). But do note: recommendations on StackOverflow is off-topic. – Parfait Jun 12 '19 at 20:21
  • 1
    You may also be interested in [this question](https://stackoverflow.com/q/35938320/2144390). – Gord Thompson Jun 12 '19 at 23:53
  • 2
    Check the Teradata-SQLAlchemy: https://github.com/Teradata/sqlalchemy-teradata https://downloads.teradata.com/tools/articles/teradata-sqlalchemy-introduction – hhoeck Jun 13 '19 at 11:49
  • 1
    Or the newer [teradatasqlalchemy](https://pypi.org/project/teradatasqlalchemy) dialect which no longer requires ODBC – Fred Jun 13 '19 at 23:03

1 Answers1

0
#found the answer here. https://downloads.teradata.com/tools/articles/teradata-sqlalchemy-introduction

from sqlalchemy import create_engine
user = 'sqlalc_user'
pasw=user
host = 'hostname'

port = '1025'
# connect
td_engine = create_engine('teradata://'+ user +':' + pasw + '@'+ host + ':22/')

# execute sql
sql = 'select * from dbc.usersV'
result = td_engine.execute(sql)
Omar Hussein
  • 1