Use output of one SQL query in another SQL query in Python

Question

I am working with a SQL Database on Python. After making the connection, I want to use the output of one query in another query.

Example: query1 gives me a list of all tables in a schema. I want to use each table name from query1 in my query2.

query2 = "SELECT TOP 200 * FROM db.schema.table ORDER BY ID"

I want to use this query for each of the table in the output of query1. Can someone help me with the Python code for it?

score 0 · Accepted Answer · answered Jul 24 '18 at 13:18

Here is a working example on how to do what you are looking to do. I didn't look up the schemes for the tablelist, but you can simply substitute the SQL code to do so. I just 'faked it' by unioning a statement of 2 tables. There are plenty of other answer on that SQL code and I don't want to clutter this answer: How do I get list of all tables in a database using TSQL?

It looks like the key part you may have been missing was the join step to build the second SQL statement. This should be enough of a starting point to craft exactly what you are looking for.

import pypyodbc

def main():
    table_list = get_table_list()
    for table in table_list:
        print_table(table)

def print_table(table):
    thesql = " ".join(["SELECT TOP 10 businessentityid FROM", table])
    connection = get_connection()
    cursor = connection.cursor()
    cursor.execute(thesql)
    for row in cursor:
        print (row["businessentityid"])
    cursor.close()
    connection.close()

def get_table_list():
    table_list = []
    thesql = ("""
    SELECT 'Sales.SalesPerson' AS thetable
    UNION
    SELECT 'Person.BusinessEntity' thetable
    """)
    connection = get_connection()
    cursor = connection.cursor()
    cursor.execute(thesql)
    for row in cursor:
        table_list.append(row["thetable"])
    cursor.close()
    connection.close()
    return table_list


def get_connection():
    '''setup connection depending on which db we are going to write to in which environment'''
    connection = pypyodbc.connect(
        "Driver={SQL Server};"
        "Server=YOURSERVER;"
        "Database=AdventureWorks2014;"
        "Trusted_Connection=yes"
        )
    return connection

main ()

It gives me an error that row indices must be integers, not str for this code: for row in cursor: table_list.append(row["thetable"]) — MasterShifu, Jul 24 '18 at 14:40
I'm using Python 3.6 but that shouldn't matter. Try printing out `row["thetable"]` and see what it says. THe code should all work as is. — sniperd, Jul 24 '18 at 14:52
Hey! That worked with a few changes as per my database. I have one more question. The ID that I am using to sort each table will be different for all. So is there any way I can sort all tables using their primary key or even the first column using a query on Python? — MasterShifu, Jul 24 '18 at 19:37
Yep, you'll want to stuff results into dictionaries and you can sort of keys and values. Perhaps a list of dictionaries.... :) If you search around on here there is a lot of advice on doing different sorting. If you've found my answer helpful, please accept it :) — sniperd, Jul 24 '18 at 19:45
Can something like this be done? def column(table, i): return [row[0] for row in table] col= column(table,1) query= "SELECT TOP 10 * FROM db.schema.[{}] order by [{}]".format(table,col) — MasterShifu, Jul 26 '18 at 14:58
I _think_ so..? I don't think you parameterized the table names though. Maybe make a series of SQL statements that each have one table name that return the table and it's order_id. Put those results into python and sort them there? — sniperd, Jul 26 '18 at 15:17

Use output of one SQL query in another SQL query in Python

1 Answers1