Mixing
python problem - running sql query from list
-2
I am trying to read queries in Excel to a Python list, which I was able to achieve.
As a next step, I want to execute all the queries from the list in Teradata (can be any database)
I am getting an error, below is the sample code. Any help is appreciated.
#Working Code:#
import teradata
import xlrd
udaExec = teradata.UdaExec (appName="Hello World", ersion="1.0", logConsole=False)
session = udaExec.connect(method="odbc", system="tddev",
username="abc", password="abc!1",authentication="LDAP",driver="Teradata");
loc = ("C:\Users\abc\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
query_list=
for row1 in range(1,total_rows):
exceldata = wbsheet.row(row1)
query_list.append(exceldata)
# getting an error after adding session execute code to above code #
for row2 in query_list:
session.execute(row2)
Error:
Traceback (most recent call last):
File "C:/Users/prasnaya/Desktop/Terada_worksheet.py", line 31, in <module>
session.execute(row2)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 675, in execute
self.internalCursor.execute(query, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 748, in execute
self._execute(self.cursor.execute, q, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 790, in _execute
func(query, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatatdodbc.py", line 613, in execute
self.hStmt, _inputStr(_convertLineFeeds(query)), SQL_NTS)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatatdodbc.py", line 881, in _convertLineFeeds
return "r".join(util.linesplit(query))
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatautil.py", line 357, in linesplit
sql if isString(sql) else newline.join(sql))
TypeError: can only join an iterable
Final Working Code :
import Teradata
import xlrd
udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
logConsole=False)
session = udaExec.connect(method="odbc", system="tdprod",
username="xyz", password="abc!1",authentication="LDAP",
driver="Teradata");
loc = ("C:\Users\prasnaya\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
print(total_rows)
query_list=
for row1 in range(0,total_rows):
exceldata = wbsheet.cell_value(row1,0)
query_list.append(exceldata)
print(len(query_list))
for row2 in query_list:
print(row2)
for row in session.execute(row2):
print(row)
session.close()
python list teradata
asked Nov 16 '18 at 18:25
Prashant NayakPrashant Nayak
113
|
show 1 more comment
-2
I am trying to read queries in Excel to a Python list, which I was able to achieve.
As a next step, I want to execute all the queries from the list in Teradata (can be any database)
I am getting an error, below is the sample code. Any help is appreciated.
#Working Code:#
import teradata
import xlrd
udaExec = teradata.UdaExec (appName="Hello World", ersion="1.0", logConsole=False)
session = udaExec.connect(method="odbc", system="tddev",
username="abc", password="abc!1",authentication="LDAP",driver="Teradata");
loc = ("C:\Users\abc\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
query_list=
for row1 in range(1,total_rows):
exceldata = wbsheet.row(row1)
query_list.append(exceldata)
# getting an error after adding session execute code to above code #
for row2 in query_list:
session.execute(row2)
Error:
Traceback (most recent call last):
File "C:/Users/prasnaya/Desktop/Terada_worksheet.py", line 31, in <module>
session.execute(row2)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 675, in execute
self.internalCursor.execute(query, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 748, in execute
self._execute(self.cursor.execute, q, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 790, in _execute
func(query, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatatdodbc.py", line 613, in execute
self.hStmt, _inputStr(_convertLineFeeds(query)), SQL_NTS)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatatdodbc.py", line 881, in _convertLineFeeds
return "r".join(util.linesplit(query))
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatautil.py", line 357, in linesplit
sql if isString(sql) else newline.join(sql))
TypeError: can only join an iterable
Final Working Code :
import Teradata
import xlrd
udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
logConsole=False)
session = udaExec.connect(method="odbc", system="tdprod",
username="xyz", password="abc!1",authentication="LDAP",
driver="Teradata");
loc = ("C:\Users\prasnaya\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
print(total_rows)
query_list=
for row1 in range(0,total_rows):
exceldata = wbsheet.cell_value(row1,0)
query_list.append(exceldata)
print(len(query_list))
for row2 in query_list:
print(row2)
for row in session.execute(row2):
print(row)
session.close()
python list teradata
asked Nov 16 '18 at 18:25
Prashant NayakPrashant Nayak
113
1
Hi and welcome to the community! What is the error you are getting?
– Per Lundberg
Nov 16 '18 at 21:32
Thanks, Per for extending help, please find error : I think it is aborting because "text:" & '/n" is getting appended to sql query Traceback (most recent call last): File "C:UsersPrashantAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 790, in _execute func(query, params, **kwargs) TypeError: can only join an iterable
– Prashant Nayak
Nov 16 '18 at 23:32
Thanks. I suggest: edit your question to include the full version of the program that worked (i.e. just executed a fixed SQL query towards Excel), then add the full exception details to the end (after the version of the program that didn't work). That way, it will be easier to analyze this and hopefully help you find a solution.
– Per Lundberg
Nov 17 '18 at 18:47
I have provided the working code & code which was causing error after being added to program
– Prashant Nayak
Nov 18 '18 at 19:50
1
haha , I will take that as positive feedback, please find Traceback added ..
– Prashant Nayak
Nov 18 '18 at 23:53
|
show 1 more comment
-2
-2
-2
1
I am trying to read queries in Excel to a Python list, which I was able to achieve.
As a next step, I want to execute all the queries from the list in Teradata (can be any database)
I am getting an error, below is the sample code. Any help is appreciated.
#Working Code:#
import teradata
import xlrd
udaExec = teradata.UdaExec (appName="Hello World", ersion="1.0", logConsole=False)
session = udaExec.connect(method="odbc", system="tddev",
username="abc", password="abc!1",authentication="LDAP",driver="Teradata");
loc = ("C:\Users\abc\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
query_list=
for row1 in range(1,total_rows):
exceldata = wbsheet.row(row1)
query_list.append(exceldata)
# getting an error after adding session execute code to above code #
for row2 in query_list:
session.execute(row2)
Error:
Traceback (most recent call last):
File "C:/Users/prasnaya/Desktop/Terada_worksheet.py", line 31, in <module>
session.execute(row2)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 675, in execute
self.internalCursor.execute(query, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 748, in execute
self._execute(self.cursor.execute, q, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 790, in _execute
func(query, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatatdodbc.py", line 613, in execute
self.hStmt, _inputStr(_convertLineFeeds(query)), SQL_NTS)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatatdodbc.py", line 881, in _convertLineFeeds
return "r".join(util.linesplit(query))
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatautil.py", line 357, in linesplit
sql if isString(sql) else newline.join(sql))
TypeError: can only join an iterable
Final Working Code :
import Teradata
import xlrd
udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
logConsole=False)
session = udaExec.connect(method="odbc", system="tdprod",
username="xyz", password="abc!1",authentication="LDAP",
driver="Teradata");
loc = ("C:\Users\prasnaya\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
print(total_rows)
query_list=
for row1 in range(0,total_rows):
exceldata = wbsheet.cell_value(row1,0)
query_list.append(exceldata)
print(len(query_list))
for row2 in query_list:
print(row2)
for row in session.execute(row2):
print(row)
session.close()
python list teradata
asked Nov 16 '18 at 18:25
Prashant NayakPrashant Nayak
113
I am trying to read queries in Excel to a Python list, which I was able to achieve.
As a next step, I want to execute all the queries from the list in Teradata (can be any database)
I am getting an error, below is the sample code. Any help is appreciated.
#Working Code:#
import teradata
import xlrd
udaExec = teradata.UdaExec (appName="Hello World", ersion="1.0", logConsole=False)
session = udaExec.connect(method="odbc", system="tddev",
username="abc", password="abc!1",authentication="LDAP",driver="Teradata");
loc = ("C:\Users\abc\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
query_list=
for row1 in range(1,total_rows):
exceldata = wbsheet.row(row1)
query_list.append(exceldata)
# getting an error after adding session execute code to above code #
for row2 in query_list:
session.execute(row2)
Error:
Traceback (most recent call last):
File "C:/Users/prasnaya/Desktop/Terada_worksheet.py", line 31, in <module>
session.execute(row2)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 675, in execute
self.internalCursor.execute(query, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 748, in execute
self._execute(self.cursor.execute, q, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 790, in _execute
func(query, params, **kwargs)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatatdodbc.py", line 613, in execute
self.hStmt, _inputStr(_convertLineFeeds(query)), SQL_NTS)
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatatdodbc.py", line 881, in _convertLineFeeds
return "r".join(util.linesplit(query))
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatautil.py", line 357, in linesplit
sql if isString(sql) else newline.join(sql))
TypeError: can only join an iterable
Final Working Code :
import Teradata
import xlrd
udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
logConsole=False)
session = udaExec.connect(method="odbc", system="tdprod",
username="xyz", password="abc!1",authentication="LDAP",
driver="Teradata");
loc = ("C:\Users\prasnaya\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
print(total_rows)
query_list=
for row1 in range(0,total_rows):
exceldata = wbsheet.cell_value(row1,0)
query_list.append(exceldata)
print(len(query_list))
for row2 in query_list:
print(row2)
for row in session.execute(row2):
print(row)
session.close()
python list teradata
python list teradata
asked Nov 16 '18 at 18:25
Prashant NayakPrashant Nayak
113
asked Nov 16 '18 at 18:25
Prashant NayakPrashant Nayak
113
edited Nov 20 '18 at 23:32
Prashant Nayak
asked Nov 16 '18 at 18:25
Prashant NayakPrashant Nayak
113
asked Nov 16 '18 at 18:25
Prashant NayakPrashant Nayak
113
asked Nov 16 '18 at 18:25
Prashant NayakPrashant Nayak
113
113
1
Hi and welcome to the community! What is the error you are getting?
– Per Lundberg
Nov 16 '18 at 21:32
Thanks, Per for extending help, please find error : I think it is aborting because "text:" & '/n" is getting appended to sql query Traceback (most recent call last): File "C:UsersPrashantAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 790, in _execute func(query, params, **kwargs) TypeError: can only join an iterable
– Prashant Nayak
Nov 16 '18 at 23:32
Thanks. I suggest: edit your question to include the full version of the program that worked (i.e. just executed a fixed SQL query towards Excel), then add the full exception details to the end (after the version of the program that didn't work). That way, it will be easier to analyze this and hopefully help you find a solution.
– Per Lundberg
Nov 17 '18 at 18:47
I have provided the working code & code which was causing error after being added to program
– Prashant Nayak
Nov 18 '18 at 19:50
1
haha , I will take that as positive feedback, please find Traceback added ..
– Prashant Nayak
Nov 18 '18 at 23:53
|
show 1 more comment
1
Hi and welcome to the community! What is the error you are getting?
– Per Lundberg
Nov 16 '18 at 21:32
Thanks, Per for extending help, please find error : I think it is aborting because "text:" & '/n" is getting appended to sql query Traceback (most recent call last): File "C:UsersPrashantAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 790, in _execute func(query, params, **kwargs) TypeError: can only join an iterable
– Prashant Nayak
Nov 16 '18 at 23:32
Thanks. I suggest: edit your question to include the full version of the program that worked (i.e. just executed a fixed SQL query towards Excel), then add the full exception details to the end (after the version of the program that didn't work). That way, it will be easier to analyze this and hopefully help you find a solution.
– Per Lundberg
Nov 17 '18 at 18:47
I have provided the working code & code which was causing error after being added to program
– Prashant Nayak
Nov 18 '18 at 19:50
1
haha , I will take that as positive feedback, please find Traceback added ..
– Prashant Nayak
Nov 18 '18 at 23:53
1
1
Hi and welcome to the community! What is the error you are getting?
– Per Lundberg
Nov 16 '18 at 21:32
Hi and welcome to the community! What is the error you are getting?
– Per Lundberg
Nov 16 '18 at 21:32
Thanks, Per for extending help, please find error : I think it is aborting because "text:" & '/n" is getting appended to sql query Traceback (most recent call last): File "C:UsersPrashantAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 790, in _execute func(query, params, **kwargs) TypeError: can only join an iterable
– Prashant Nayak
Nov 16 '18 at 23:32
Thanks, Per for extending help, please find error : I think it is aborting because "text:" & '/n" is getting appended to sql query Traceback (most recent call last): File "C:UsersPrashantAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 790, in _execute func(query, params, **kwargs) TypeError: can only join an iterable
– Prashant Nayak
Nov 16 '18 at 23:32
Thanks. I suggest: edit your question to include the full version of the program that worked (i.e. just executed a fixed SQL query towards Excel), then add the full exception details to the end (after the version of the program that didn't work). That way, it will be easier to analyze this and hopefully help you find a solution.
– Per Lundberg
Nov 17 '18 at 18:47
Thanks. I suggest: edit your question to include the full version of the program that worked (i.e. just executed a fixed SQL query towards Excel), then add the full exception details to the end (after the version of the program that didn't work). That way, it will be easier to analyze this and hopefully help you find a solution.
– Per Lundberg
Nov 17 '18 at 18:47
I have provided the working code & code which was causing error after being added to program
– Prashant Nayak
Nov 18 '18 at 19:50
I have provided the working code & code which was causing error after being added to program
– Prashant Nayak
Nov 18 '18 at 19:50
1
1
haha , I will take that as positive feedback, please find Traceback added ..
– Prashant Nayak
Nov 18 '18 at 23:53
haha , I will take that as positive feedback, please find Traceback added ..
– Prashant Nayak
Nov 18 '18 at 23:53
|
show 1 more comment
2 Answers
2
active
oldest
votes
0
So, the problem is that the linesplit method here (inside teradata) presumes that the sql parameter is an iterable:
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatautil.py", line 357, in linesplit
sql if isString(sql) else newline.join(sql))
...which it isn't, in your case. Interestingly enough, a Python str object is iterable. In other words, the query you are getting from your source object (the Excel sheet) is not a proper string.
Possible reasons:
- Could it be that
rowis alist, where each item in the list is a cell from the row? If so, tryrow[0], or perhapsstr(row[0])if the former doesn't work. - Could it be that it needs a type conversion to string? If so, try
str(row)(this is unlikely to work but you can always try) - Could it be some other type? Please try
print(type(row)); this should give you the type of the object you are working on to yourstdout, which might help you better understand the problem here.
Good luck, and please post back your results here so that the question can hopefully help others who run into similar problems.
answered Nov 19 '18 at 8:16
Per LundbergPer Lundberg
1,74512232
1
Thanks, Per, I will try on your recommended approach and for sure share the result
– Prashant Nayak
Nov 19 '18 at 18:42
1
Hi Per, finally the query is running and as expected. Thanks for your help.
– Prashant Nayak
Nov 20 '18 at 23:25
Great to hear! If there is more info to add here that could help others debugging similar issues, please do it. Also, feel free to upvote/mark my answer as correct if you like. :)
– Per Lundberg
Nov 21 '18 at 7:00
add a comment |
0
import Teradata
import xlrd
udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
logConsole=False)
session = udaExec.connect(method="odbc", system="tdprod",
username="abc", password="xyz!1",authentication="LDAP",
driver="Teradata");
loc = ("C:\Users\xyz\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
print(total_rows)
query_list=
for row1 in range(0,total_rows):
exceldata = wbsheet.cell_value(row1,0)
query_list.append(exceldata)
print(len(query_list))
for row2 in query_list:
print(row2)
for row in session.execute(row2):
print(row)
session.close()
answered Nov 21 '18 at 16:22
Prashant NayakPrashant Nayak
113
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53343445%2fpython-problem-running-sql-query-from-list%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
So, the problem is that the linesplit method here (inside teradata) presumes that the sql parameter is an iterable:
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatautil.py", line 357, in linesplit
sql if isString(sql) else newline.join(sql))
...which it isn't, in your case. Interestingly enough, a Python str object is iterable. In other words, the query you are getting from your source object (the Excel sheet) is not a proper string.
Possible reasons:
- Could it be that
rowis alist, where each item in the list is a cell from the row? If so, tryrow[0], or perhapsstr(row[0])if the former doesn't work. - Could it be that it needs a type conversion to string? If so, try
str(row)(this is unlikely to work but you can always try) - Could it be some other type? Please try
print(type(row)); this should give you the type of the object you are working on to yourstdout, which might help you better understand the problem here.
Good luck, and please post back your results here so that the question can hopefully help others who run into similar problems.
answered Nov 19 '18 at 8:16
Per LundbergPer Lundberg
1,74512232
1
Thanks, Per, I will try on your recommended approach and for sure share the result
– Prashant Nayak
Nov 19 '18 at 18:42
1
Hi Per, finally the query is running and as expected. Thanks for your help.
– Prashant Nayak
Nov 20 '18 at 23:25
Great to hear! If there is more info to add here that could help others debugging similar issues, please do it. Also, feel free to upvote/mark my answer as correct if you like. :)
– Per Lundberg
Nov 21 '18 at 7:00
add a comment |
0
So, the problem is that the linesplit method here (inside teradata) presumes that the sql parameter is an iterable:
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatautil.py", line 357, in linesplit
sql if isString(sql) else newline.join(sql))
...which it isn't, in your case. Interestingly enough, a Python str object is iterable. In other words, the query you are getting from your source object (the Excel sheet) is not a proper string.
Possible reasons:
- Could it be that
rowis alist, where each item in the list is a cell from the row? If so, tryrow[0], or perhapsstr(row[0])if the former doesn't work. - Could it be that it needs a type conversion to string? If so, try
str(row)(this is unlikely to work but you can always try) - Could it be some other type? Please try
print(type(row)); this should give you the type of the object you are working on to yourstdout, which might help you better understand the problem here.
Good luck, and please post back your results here so that the question can hopefully help others who run into similar problems.
answered Nov 19 '18 at 8:16
Per LundbergPer Lundberg
1,74512232
1
Thanks, Per, I will try on your recommended approach and for sure share the result
– Prashant Nayak
Nov 19 '18 at 18:42
1
Hi Per, finally the query is running and as expected. Thanks for your help.
– Prashant Nayak
Nov 20 '18 at 23:25
Great to hear! If there is more info to add here that could help others debugging similar issues, please do it. Also, feel free to upvote/mark my answer as correct if you like. :)
– Per Lundberg
Nov 21 '18 at 7:00
add a comment |
0
0
0
So, the problem is that the linesplit method here (inside teradata) presumes that the sql parameter is an iterable:
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatautil.py", line 357, in linesplit
sql if isString(sql) else newline.join(sql))
...which it isn't, in your case. Interestingly enough, a Python str object is iterable. In other words, the query you are getting from your source object (the Excel sheet) is not a proper string.
Possible reasons:
- Could it be that
rowis alist, where each item in the list is a cell from the row? If so, tryrow[0], or perhapsstr(row[0])if the former doesn't work. - Could it be that it needs a type conversion to string? If so, try
str(row)(this is unlikely to work but you can always try) - Could it be some other type? Please try
print(type(row)); this should give you the type of the object you are working on to yourstdout, which might help you better understand the problem here.
Good luck, and please post back your results here so that the question can hopefully help others who run into similar problems.
answered Nov 19 '18 at 8:16
Per LundbergPer Lundberg
1,74512232
So, the problem is that the linesplit method here (inside teradata) presumes that the sql parameter is an iterable:
File "C:UsersprasnayaAppDataLocalProgramsPythonPython37-32libsite-packagesteradatautil.py", line 357, in linesplit
sql if isString(sql) else newline.join(sql))
...which it isn't, in your case. Interestingly enough, a Python str object is iterable. In other words, the query you are getting from your source object (the Excel sheet) is not a proper string.
Possible reasons:
- Could it be that
rowis alist, where each item in the list is a cell from the row? If so, tryrow[0], or perhapsstr(row[0])if the former doesn't work. - Could it be that it needs a type conversion to string? If so, try
str(row)(this is unlikely to work but you can always try) - Could it be some other type? Please try
print(type(row)); this should give you the type of the object you are working on to yourstdout, which might help you better understand the problem here.
Good luck, and please post back your results here so that the question can hopefully help others who run into similar problems.
answered Nov 19 '18 at 8:16
Per LundbergPer Lundberg
1,74512232
answered Nov 19 '18 at 8:16
Per LundbergPer Lundberg
1,74512232
answered Nov 19 '18 at 8:16
Per LundbergPer Lundberg
1,74512232
answered Nov 19 '18 at 8:16
Per LundbergPer Lundberg
1,74512232
1,74512232
1
Thanks, Per, I will try on your recommended approach and for sure share the result
– Prashant Nayak
Nov 19 '18 at 18:42
1
Hi Per, finally the query is running and as expected. Thanks for your help.
– Prashant Nayak
Nov 20 '18 at 23:25
Great to hear! If there is more info to add here that could help others debugging similar issues, please do it. Also, feel free to upvote/mark my answer as correct if you like. :)
– Per Lundberg
Nov 21 '18 at 7:00
add a comment |
1
Thanks, Per, I will try on your recommended approach and for sure share the result
– Prashant Nayak
Nov 19 '18 at 18:42
1
Hi Per, finally the query is running and as expected. Thanks for your help.
– Prashant Nayak
Nov 20 '18 at 23:25
Great to hear! If there is more info to add here that could help others debugging similar issues, please do it. Also, feel free to upvote/mark my answer as correct if you like. :)
– Per Lundberg
Nov 21 '18 at 7:00
1
1
Thanks, Per, I will try on your recommended approach and for sure share the result
– Prashant Nayak
Nov 19 '18 at 18:42
Thanks, Per, I will try on your recommended approach and for sure share the result
– Prashant Nayak
Nov 19 '18 at 18:42
1
1
Hi Per, finally the query is running and as expected. Thanks for your help.
– Prashant Nayak
Nov 20 '18 at 23:25
Hi Per, finally the query is running and as expected. Thanks for your help.
– Prashant Nayak
Nov 20 '18 at 23:25
Great to hear! If there is more info to add here that could help others debugging similar issues, please do it. Also, feel free to upvote/mark my answer as correct if you like. :)
– Per Lundberg
Nov 21 '18 at 7:00
Great to hear! If there is more info to add here that could help others debugging similar issues, please do it. Also, feel free to upvote/mark my answer as correct if you like. :)
– Per Lundberg
Nov 21 '18 at 7:00
add a comment |
0
import Teradata
import xlrd
udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
logConsole=False)
session = udaExec.connect(method="odbc", system="tdprod",
username="abc", password="xyz!1",authentication="LDAP",
driver="Teradata");
loc = ("C:\Users\xyz\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
print(total_rows)
query_list=
for row1 in range(0,total_rows):
exceldata = wbsheet.cell_value(row1,0)
query_list.append(exceldata)
print(len(query_list))
for row2 in query_list:
print(row2)
for row in session.execute(row2):
print(row)
session.close()
answered Nov 21 '18 at 16:22
Prashant NayakPrashant Nayak
113
add a comment |
0
import Teradata
import xlrd
udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
logConsole=False)
session = udaExec.connect(method="odbc", system="tdprod",
username="abc", password="xyz!1",authentication="LDAP",
driver="Teradata");
loc = ("C:\Users\xyz\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
print(total_rows)
query_list=
for row1 in range(0,total_rows):
exceldata = wbsheet.cell_value(row1,0)
query_list.append(exceldata)
print(len(query_list))
for row2 in query_list:
print(row2)
for row in session.execute(row2):
print(row)
session.close()
answered Nov 21 '18 at 16:22
Prashant NayakPrashant Nayak
113
add a comment |
0
0
0
import Teradata
import xlrd
udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
logConsole=False)
session = udaExec.connect(method="odbc", system="tdprod",
username="abc", password="xyz!1",authentication="LDAP",
driver="Teradata");
loc = ("C:\Users\xyz\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
print(total_rows)
query_list=
for row1 in range(0,total_rows):
exceldata = wbsheet.cell_value(row1,0)
query_list.append(exceldata)
print(len(query_list))
for row2 in query_list:
print(row2)
for row in session.execute(row2):
print(row)
session.close()
answered Nov 21 '18 at 16:22
Prashant NayakPrashant Nayak
113
import Teradata
import xlrd
udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
logConsole=False)
session = udaExec.connect(method="odbc", system="tdprod",
username="abc", password="xyz!1",authentication="LDAP",
driver="Teradata");
loc = ("C:\Users\xyz\Desktop\Queries.xlsx")
wb = xlrd.open_workbook(loc)
wbsheet=wb.sheet_by_name("Queries")
total_rows=wbsheet.nrows
print(total_rows)
query_list=
for row1 in range(0,total_rows):
exceldata = wbsheet.cell_value(row1,0)
query_list.append(exceldata)
print(len(query_list))
for row2 in query_list:
print(row2)
for row in session.execute(row2):
print(row)
session.close()
answered Nov 21 '18 at 16:22
Prashant NayakPrashant Nayak
113
answered Nov 21 '18 at 16:22
Prashant NayakPrashant Nayak
113
answered Nov 21 '18 at 16:22
Prashant NayakPrashant Nayak
113
answered Nov 21 '18 at 16:22
Prashant NayakPrashant Nayak
113
113
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53343445%2fpython-problem-running-sql-query-from-list%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Hi and welcome to the community! What is the error you are getting?
– Per Lundberg
Nov 16 '18 at 21:32
Thanks, Per for extending help, please find error : I think it is aborting because "text:" & '/n" is getting appended to sql query Traceback (most recent call last): File "C:UsersPrashantAppDataLocalProgramsPythonPython37-32libsite-packagesteradataudaexec.py", line 790, in _execute func(query, params, **kwargs) TypeError: can only join an iterable
– Prashant Nayak
Nov 16 '18 at 23:32
Thanks. I suggest: edit your question to include the full version of the program that worked (i.e. just executed a fixed SQL query towards Excel), then add the full exception details to the end (after the version of the program that didn't work). That way, it will be easier to analyze this and hopefully help you find a solution.
– Per Lundberg
Nov 17 '18 at 18:47
I have provided the working code & code which was causing error after being added to program
– Prashant Nayak
Nov 18 '18 at 19:50
1
haha , I will take that as positive feedback, please find Traceback added ..
– Prashant Nayak
Nov 18 '18 at 23:53