0

I am trying to insert my dataframe into a newly created table in Teradata. My connection and creating the table using SQLAchmey works, but I am unable to insert the data. I keep getting the same error that the schemy columns do not exist.

Here is my code:

username = '..'
password= '..'
server ='...'
database ='..'
driver = 'Aster ODBC Driver'
engine_stmt = ("mssql+pyodbc://%s:%s@%s/%s?driver=%s" % (username, password, server, database, driver ))
engine = sqlalchemy.create_engine(engine_stmt)
conn = engine.raw_connection()

#create tble function
def create_sql_tbl_schema(conn):
    #tbl_cols_sql = gen_tbl_cols_sql(df)
    sql = "CREATE TABLE so_sandbox.mn_testCreation3  (A INTEGER NULL,B  INTEGER NULL,C INTEGER NULL,D  INTEGER NULL) DISTRIBUTE BY HASH (A) STORAGE ROW COMPRESS LOW;"
    cur = conn2.cursor()
    cur.execute('rollback')
    cur.execute(sql)
    cur.close()
    conn.commit()

create_mysql_tbl_schema(conn) #this works and the table is created
df = pd.DataFrame(np.random.randint(0,100,size=(100, 4)), columns=list('abcd'))
df.to_sql('mn_testCreation3', con=engine,
              schema='so_sandbox', index=False, if_exists='append') #this is giving me problems

Error message returned is:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [AsterData][nCluster] (34) ERROR: relation "INFORMATION_SCHEMA"."COLUMNS" does not exist.  (34) (SQLPrepare)') [SQL: 'SELECT [INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA], [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME], [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME], [INFORMATION_SCHEMA].[COLUMNS].[IS_NULLABLE], [INFORMATION_SCHEMA].[COLUMNS].[DATA_TYPE], [INFORMATION_SCHEMA].[COLUMNS].[ORDINAL_POSITION], [INFORMATION_SCHEMA].[COLUMNS].[CHARACTER_MAXIMUM_LENGTH], [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_PRECISION], [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_SCALE], [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_DEFAULT], [INFORMATION_SCHEMA].[COLUMNS].[COLLATION_NAME] \nFROM [INFORMATION_SCHEMA].[COLUMNS] \nWHERE [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME] = ? AND [INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] = ?'] [parameters: ('mn_testCreation3', 'so_sandbox')] (Background on this error at: http://sqlalche.me/e/f405)
dnoeth
  • 59,503
  • 4
  • 39
  • 56
Maria Nazari
  • 660
  • 1
  • 9
  • 27
  • You connect to Aster, but in your `engine_stmt` you state `mssql`. – dnoeth Dec 25 '18 at 22:22
  • @dnoeth I couldn't find the syntax for aster but it works when creating the table in the aster database using 'mssql' – Maria Nazari Dec 25 '18 at 22:54
  • 2
    There's no `information _schema` in Aster. Searching for *Aster sqlalchemy* shows https://github.com/kpn-advanced-analytics/sqlalchemy_mf_aster – dnoeth Dec 25 '18 at 23:06
  • I'm still a bit lost, I am new to sqlalchemy, I'm not sure how to use Aster with it. Since I am able to create a table in aster using my code above, I just don't know why I can insert data into that table. – Maria Nazari Dec 25 '18 at 23:30
  • 1
    sqlalchemy tries to get metadata using SQL Server syntax, of course this fails. Did you download that Aster driver (plus Aster JDBC), then it should be `engine_stmt = ("aster://...`? – dnoeth Dec 26 '18 at 09:52

0 Answers0