I am going to execute multiple SQL queries in python but I think since they are in array there are some extra characters like [" "] which read_sql_query function cannot execute them, or maybe there is another problem. Do anyone know how can I solve this problem?

My array:

array([['F_TABLE1'],
   ['F_TABLE2'],
   ['F_TABLE3'],
   ['F_TABLE4'],
   ['F_TABLE5'],
   ['F_TABLE6'],
   ['F_TABLE1'],
   ['F_TABLE8']], dtype=object)

My python code:

SQL_Query = []
for row in range(len(array)):
      SQL_Query.append('SELECT '   "'"   array[row]   "'"   ', COUNT(*) FROM '   array[row]) 
SQL = []
for row in range(len(SQL_Query)):
      SQL = pd.read_sql_query(SQL_Query[row], conn) 

PS: I separated them in two for to see what is wrong with my code.

Also I print one of the arrays to see what is the output of my array.

print(SQL_Query[0])

The output:

["SELECT 'F_CLINICCPARTY_HIDDEN', COUNT(*) FROM F_TABLE1"]

Because of the above output I think the problem is extra characters.

It gives me this error:

Execution failed on sql '["SELECT 'F_TABLE1', COUNT(*) FROM F_TABLE1"]': expecting string or bytes object

CodePudding user response：

Hi reason for this kind of an issue is when getting a numpy array value like array[row] will resultant in a numpy object. Even though in basic array terms it looks like the way numpy behaves differently. use item method to overcome the issue.

Based on your array I have written a sample code in there I'm only referring to the same array object instead of columns and tables.

import numpy as np
array1 = np.array([['F_TABLE1'],
   ['F_TABLE2'],
   ['F_TABLE3'],
   ['F_TABLE4'],
   ['F_TABLE5'],
   ['F_TABLE6'],
   ['F_TABLE1'],
   ['F_TABLE8']], dtype=object)

SQL_Query = []
for row in range(len(array1)):
      SQL_Query.append("SELECT  \'{0}\',COUNT(*) FROM {1}".format(str(array1.item(row)),str(array1.item(row))) ) 
print(SQL_Query)

feel free to use two array objects for selecting SQL columns and tables.

And using '' while selecting a column name in a query is not recommended. I have included that in this answer because I haven't got any idea about the destination database type.

CodePudding user response：

I change my query to this(putting * helped me to omit other characters):

SQL_Query = []
for row in range(len(array)):
     SQL_Query.append('SELECT '   "'"   array[row]   "'" AS tableName   ', COUNT(*) AS Count FROM '   array[row]) 
SQL = []
for row in range(len(SQL_Query)):
     SQL.append(pd.read_sql_query(*SQL_Query[row], conn))  
result = []
for item in SQL:
     result.append(item)
result = pd.concat(result)
result  

My output: