How to access information schema using a pypika query?-CodePudding

I'm trying to get the names of the columns from a table in an Azure SQL database using a PyPika SQL query, but keep running into trouble. Here's the code I'm using to generate the query:

def dbView(table):
    infoSchema = ppk.Table("INFORMATION_SCHEMA.COLUMNS")
    return ppk.MSSQLQuery.from_(infoSchema).select(infoSchema.COLUMN_NAME).where(infoSchema.TABLE_NAME == table)

I created another function that uses the PyODBC library to get the SQL from the query, execute it against the database, and return all the rows:

def getData(query: ppk.Query):
    '''
    Execute a query against the Azure db and return 
    every row in the results list.
    '''

    print("QUERY: ", query.get_sql())
    conn = getConnection()
    with conn.cursor() as cursor:
        cursor.execute(query.get_sql())
        return cursor.fetchall()

I know the getData() function works because when I pass it a simple select query, everything works correctly. However, when I try to use the query generated by pypika above, I get the following error:

pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'INFORMATION_SCHEMA.COLUMNS'. (208) (SQLExecDirectW)")

To make sure this wasn't just some kind of permissions error, I wrote the following query by hand and executed it using the getData() function and it worked just fine:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Validation'

I also printed out the query that pypika generated to the console. The only difference appears to be the addition of some double quotes:

SELECT "COLUMN_NAME" FROM "INFORMATION_SCHEMA.COLUMNS" WHERE "TABLE_NAME"='Validation'

What am I doing wrong? For some reason, this error appears to be limited to specifically the information schema table, because I have used similar queries several other times in my code without issue. I know I can just use the query I wrote by hand, but the point of using PyPika was to make all my SQL queries more readable and reusable - it'd be nice to understand why it doesn't work in this very specific situation.

Thanks!

CodePudding user response：

It apparently has an API to schema-qualify tables.

from pypika import Table, Query, Schema

views = Schema('views')

q = Query.from_(views.customers).select(customers.id, customers.phone)

https://pypika.readthedocs.io/en/latest/2_tutorial.html#tables-columns-schemas-and-databases