I'm trying to clean a list that is essentially a Table definition I pulled from Teradata DB using SQLAlchemy. I'm essentially trying to replace the \r character from the string

I've tried using the replace function in a for loop as shown below

import teradata
import sqlalchemy
import string


eng = sqlalchemy.create_engine('teradatasql:///?user=xxxx&       

# execute sql
query = 'SHOW TABLE DBADMIN_BKP.LIKP_BKP'
result = eng.execute(query)
results = result.fetchall()
results = [items.replace("\n", "") for items in results]

print (results)


Traceback (most recent call last):
File "SQL_ALCHEMY.py", line 12, in <module>
results = [items.str_replace("\n", "") for items in results]
File "SQL_ALCHEMY.py", line 12, in <listcomp>
results = [items.str_replace("\n", "") for items in results]
AttributeError: Could not locate column in row for column 'replace'

2 Answers

1
Arnav Poddar On

There is a simple fix to your code that should make it work. I'm pretty sure you just misused the .replace() function in your code. Here is the correct code:

import teradata
import sqlalchemy
import string


eng = sqlalchemy.create_engine('teradatasql:///?user=xxxx&')

# execute sql
query = 'SHOW TABLE DBADMIN_BKP.LIKP_BKP'
result = eng.execute(query)
results = result.fetchall()
results = results.replace("\n", "") #This is the segment of the code that was causing the error. 

print (results)
0
pickle On

The item contained in your list is a tuple.

If you print results[0] you'll get:

('CREATE MULTISET TABLE DBADMIN_BKP.LIKP_BKP ,NO FALLBACK ,\r NO BEFORE JOURNAL,\r NO AFTER JOURNAL,\r CHECKSUM = DEFAULT,\r DEFAULT ME ... (12819 characters truncated) ... HARACTER SET UNICODE CASESPECIFIC NOT NULL,\r FSH_VAS_CG CHAR(3) CHARACTER SET UNICODE CASESPECIFIC NOT NULL, \rPRIMARY KEY ( MANDT ,VBELN ))\r;',)

Now you've said that you've turned that into a string, which I assume you did by doing str(results[i]). This produces:

"('CREATE MULTISET TABLE DBADMIN_BKP.LIKP_BKP ,NO FALLBACK ,\\r NO BEFORE JOURNAL,\\r NO AFTER JOURNAL,\\r CHECKSUM = DEFAULT,\\r DEFAULT ME ... (12819 characters truncated) ... HARACTER SET UNICODE CASESPECIFIC NOT NULL,\\r FSH_VAS_CG CHAR(3) CHARACTER SET UNICODE CASESPECIFIC NOT NULL, \\rPRIMARY KEY ( MANDT ,VBELN ))\\r;',)"

When it got transformed into a string, Python wanted to ensure that "\r" would actually be printed, rather than the meaning of the escape sequence "\r", which is a carriage return. In order to do this, another backslash is added, giving the escape sequence "\\" which literally represents a single "\"

>>> print("\\r")
\r
>>> print("\r")

>>> print("\\")
\
>>> print("\")
  File "<stdin>", line 1
    print("\")
             ^
SyntaxError: EOL while scanning string literal

So in order to remove this "\\r" character, you need to identify it properly.