Python Loop Help

In SQL, if I want to run multiple values in a where condition, in our example above I can do IN ('value1', 'value2', 'etc..'). I assume I would need this here.

You can construct a comma separated list if uin values and pass the list to the functions.  The SQL can be changed from  store_UIN = ? to store_UIN in (?)

@aikimark, could I also create an array in the code and call upon that and create a for loop instead to iterate through the list/array to execute the functions and pass the variable name in the function so that SQL code absorbs it?

for loop and array example:

uinList = ['value1', 'value2,'...'nth value']
for i in range(len(uinList)):
    functions(uinList[])

Select allOpen in new window

Why you are not using the array solution....your code is "solid" in the concept that you as far as you provide an input it will "run"...
Just put all the "uin" values in an array and let it iterate the array and perform the work....

While that is possible, it might require more coding changes.  Is your problem the time it takes?  If so, my earlier comment should be faster than multiple SQL calls.

@aikimark i think you are talking the opposite.....the array "solution" is the fastest to implement....it will just change the first two lines of his code with the array iteration and off he goes...with the SQL it should run much much faster but he will have to  make the SQL change to all the queries..

Gents, I am trying to go with whatever is the most feasible and least maintenance solution.

Could you provide me an example of how your CSV option would look like, @aikimark?

@tsgiannis: when you say it looks solid and to go with the array solution, how do you mean?

I guess the easiest implementation is :

def massiveuin():
uinList = ['value1', 'value2,'...'nth value']
for i in range(len(uinList)):
    executeuin(i)

Select allOpen in new window

def executeuin(uin) : 
    startTime = datetime.now()
    selectCustomer(uin)
    selectVehicle(uin)
    selectHRO(uin)
    selectHLABOR(uin)
    selectHPARTS(uin)
    selectINV(uin)
    selectSUPPLIER(uin)
    selectLABOR_OP(uin)
    selectSOURCE(uin)
    selectRO(uin)
    selectLABOR(uin)
    selectPARTS(uin)
    selectEHRO(uin)
    selectEHLABOR(uin)
    selectEHPARTS(uin)
    endTime = datetime.now() - startTime
    print('It took', endTime, ' seconds to run...')

Select allOpen in new window

In concept, like this, @tsgiannis?

def sqlCode(uin):
    sqlQuery =("SELECT STORE_UIN, RO_NO, PARTNO, PARTDESC, ITEM_NO, MANUFACTURER, PARTCOST, PARTPRICE, "
                   "QUANITY, INVOICE, SUPPLIER, ST  from EHPARTS "
                   "where "
                   "store_UIN in (?)", uin)
    print(sqlQuery)               


uinList = ['TX001','AZ001','CO001']

for i in range(len(uinList)):
    uin = uinList[i]
    sqlCode(uin)

Select allOpen in new window

@sj77 ...this is the aikimark solution...in my way you keep all your code as it minus the input and you just iterating the list to feed the methods...

So assuming with your solution the i in the execution function gets passed to the below function that is doing all my function calls?

depicted below:

def massiveuin():
uinList = ['value1', 'value2,'...'nth value']
for i in range(len(uinList)):
    executeuin(i)

Select allOpen in new window

Forgive me if I am a novice, but, how does the i pass to the def execute(uin): code?

correct

Hmmmm, I did not get it to run properly, @tsgiannis.

This solution does work though:

def sqlCode(uin):
    sqlQuery =("SELECT STORE_UIN, RO_NO, PARTNO, PARTDESC, ITEM_NO, MANUFACTURER, PARTCOST, PARTPRICE, "
                   "QUANITY, INVOICE, SUPPLIER, ST  from EHPARTS "
                   "where "
                   "store_UIN in (?)", uin)
    print(sqlQuery)               


uinList = ['TX001','AZ001','CO001']

for i in range(len(uinList)):
    uin = uinList[i]
    sqlCode(uin)

Select allOpen in new window

Well this should work :

def massiveuin():
    uinList = ['TX001', 'AZ001', 'CO001']
    for uin in uinList:
            executeuin(uin)

def executeuin(uin):   
    startTime = datetime.now()
    selectCustomer(uin)
    selectVehicle(uin)
    selectHRO(uin)
    selectHLABOR(uin)
    selectHPARTS(uin)
    selectINV(uin)
    selectSUPPLIER(uin)
    selectLABOR_OP(uin)
    selectSOURCE(uin)
    selectRO(uin)
    selectLABOR(uin)
    selectPARTS(uin)
    selectEHRO(uin)
    selectEHLABOR(uin)
    selectEHPARTS(uin)
    endTime = datetime.now() - startTime
    print('It took', endTime, ' seconds to run...')
    
    
if __name__ == "__main__":
    massiveuin()

Select allOpen in new window

John's solution works, but isn't the same as my solution.

In this sample code, I assume you have gathered all the uin inputs from your user or other source.  The uin values are appended to a list (uinList).  You need to convert the list into a list of string values for the query.  I used selectEHPARTS in this example, because it was the one you posted.

uinList = ['TX001','AZ001','CO001']
uinQueryList = "'" + "','".join(uinList) + "'"
selectEHPARTS(uinQueryList)

Select allOpen in new window

In each of these selectxxxxxx functions, you would change the SQL as I already mentioned:

    cursor.execute("SELECT STORE_UIN, RO_NO, PARTNO, PARTDESC, ITEM_NO, MANUFACTURER, PARTCOST, PARTPRICE, "
                   "QUANITY, INVOICE, SUPPLIER, ST  from EHPARTS "
                   "where "
                   "store_UIN In (?)", uin)

Select allOpen in new window

As long as there is at least one uin value in the list, your queries will work.

so I don't want each query to combine the data for each UIN.

sorry should have mentioned that. essentially when each function runs it generates a json file for the UIN and the table.

does that make sense?

With a bit refactoring the solution proposed by aikimark should "provide" better performance...For example for 10 uin(s) instead of calling and fetching records from the SQL server 10 times (here there is a small catch..the performance gain is not like x10...it can be close...it can also be something negligible like 1.015x ......depending on the complexity of the query/indexes...design of your tables..optimization...the list goes on)...you will only call it once (1)...you will fetch all the records altogether....
On the other hand you need to rewrite your methods so that you pass an array as argument instead of single value...rewrite the queries to change from  store_UIN = ? to store_UIN in (?) ...and also change the logic of json creation as instead of having to deal with what was delivered by a single value as criteria...you have to slice the records so that everything is dumped accordingly.
On the other hand my solution essentially changes nothing..instead of "you" providing a uin and waiting for the execution for the next uin to input...you have a list...a list of uin(s)..it can be 1,2...10...10000...at some time ...based on your needs...you just prepare the uin(s) you need and you populate the list....you then hit "run"...the program will do its job and you can go on with what you like...no need for someone to input a uin.

Maybe using asynchronous tasks help you. Try this:

import asyncio
from datetime import datetime

"""
  your functions
  
"""

async def ProcessUIN(uin):
	await asyncio.sleep(1)
	try:
		startTime = datetime.now()
		selectCustomer(uin)
		selectVehicle(uin)
		selectHRO(uin)
		selectHLABOR(uin)
		selectHPARTS(uin)
		selectINV(uin)
		selectSUPPLIER(uin)
		selectLABOR_OP(uin)
		selectSOURCE(uin)
		selectRO(uin)
		selectLABOR(uin)
		selectPARTS(uin)
		selectEHRO(uin)
		selectEHLABOR(uin)
		selectEHPARTS(uin)
		endTime = datetime.now() - startTime
		print('It took', endTime, ' seconds to run...')
	except asyncio.CancelledError:
		raise
	except Exception:
		print(f"error {uin}")
	return

async def read():
	uin = input("Please enter a uin or 'enter' to quit: ")
	await asyncio.sleep(1)
	return uin
	
	
async def start():
	tasks= []
	while True:
		uin = await read()
		#print(f"uin {uin}")
		if not uin:
			for t in tasks:
				await t	
			break
		tasks.append(asyncio.create_task(ProcessUIN(uin)))

asyncio.run(start())
	
print("end")

Select allOpen in new window

I was simulating your process using async / await and it's not  efficient if your functions are not asynchronous.
Therefore, i think so the best option is that proposed by aikimark using  SELECT with  IN (uin1, uin2, uin3). If you have to generate a file for each UIN, then, sort by STORE_UIN. and create a file for each change of  STORE_UIN.

It comes down to priorities and capabilities.

What are your (or your users') priorities with this application?
What is your capability to get a result from multiple uin's and separate them for subsequent processing, as needed?

my priority is to be able process more than one UIN at a time and for each UIN that passes through the query for the table to (in func) for a file to generate.

I have confidence in being able to develop it but need some guidance/direction of how to proceed.

Just copy paste my last code and you will be up an ready...have you tried it?

From the database perspective.....

You are running a bunch of function calls.  Each one opens a connection, uses it and closes it.  Not efficient.  You should open one connection, call all the functions, then close the one connection.  You are going to spend a lot of time and resources connecting and disconnecting.

The IN LIST approach should be a little more efficient, assuming the code can handle the data coming back that way.  The biggest efficiency is that column that you are using in the IN LIST is indexed.  It is faster to parse and execute one query than it is to do 30.

The caveat to all of this, is that every database has a limit on the number of items you can have in the IN LIST.  You likely aren't going to hit that in what you are describing, but you need to be aware of it.

Please post another one of your selectxxxxx functions, or all of them.

...able process more than one UIN at a time...  

That is multitasking and your code is not prepared for it. You would have to make changes to all your functions. If you want to do multitasking you would have to rethink the process under this scheme.

As I mentioned earlier, the SELECT IN proposal seems convenient. I would not worry about the limit of elements that the IN operator supports since considering that these are entered by the user, I do not think that they will enter an amount greater than the limit.

On the other hand, I would give the user the possibility to upload a file with the list of UIN values.

Finally, I have observed that you do not validate the value entered.

I suspect that the code, as currently structured/written, does too many things inside each function and violates SOLID principles.

We're coming at this problem without know design requirements and constraints.

I hope to learn a lot when the OP posts another selectxxxx function.

Here is the original code:

import pyodbc
import json
import collections
import os
import simplejson as json
import time
from datetime import datetime

def selectCustomer(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver+host+database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, CUST_NO, FNAME, LNAME, BNAME, AC1, PHONE1, AC2, PHONE2, AC3, PHONE3, AC4, "
                   "PHONE4, AC5, "
                   "PHONE5, AC6, PHONE6, EMAIL, CNOTES, TAXID, ADDRESS, CITY, STATE, ZIP, BIRTHDAY, EXT1, EXT2, EXT3, "
                   "EXT4, EXT5, EXT6  from Customer "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['CUST_NO'] = row.CUST_NO
        d['FNAME'] = row.FNAME
        d['LNAME'] = row.LNAME
        d['BNAME'] = row.BNAME
        d['AC1'] = row.AC1
        d['PHONE1'] = row.PHONE1
        d['AC2'] = row.AC2
        d['PHONE2'] = row.PHONE2
        d['AC3'] = row.AC3
        d['PHONE3'] = row.PHONE3
        d['AC4'] = row.AC4
        d['PHONE4'] = row.PHONE4
        d['AC5'] = row.AC5
        d['PHONE5'] = row.PHONE5
        d['AC6'] = row.AC6
        d['PHONE6'] = row.PHONE6
        d['EMAIL'] = row.EMAIL
        d['CNOTES'] = row.CNOTES
        d['TAXID'] = row.TAXID
        d['ADDRESS'] = row.ADDRESS
        d['CITY'] = row.CITY
        d['STATE'] = row.STATE
        d['ZIP'] = row.ZIP
        d['BIRTHDAY'] = row.BIRTHDAY
        d['EXT1'] = row.EXT1
        d['EXT2'] = row.EXT2
        d['EXT3'] = row.EXT3
        d['EXT4'] = row.EXT4
        d['EXT5'] = row.EXT5
        d['EXT6'] = row.EXT6
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "CUSTOMER"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)


    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectVehicle(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver+host+database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, CUST_NO, VEH_NO, TMAKE, TMODEL, TENGINE, YEAR, LICENSE, STATE, VIN, VNOTES, "
                   "FLEETUNITNO  from Vehicle "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['CUST_NO'] = row.CUST_NO
        d['VEH_NO'] = row.VEH_NO
        d['TMAKE'] = row.TMAKE
        d['TMODEL'] = row.TMODEL
        d['TENGINE'] = row.TENGINE
        d['YEAR'] = row.YEAR
        d['LICENSE'] = row.LICENSE
        d['STATE'] = row.STATE
        d['VIN'] = row.VIN
        d['VNOTES'] = row.VNOTES
        d['FLEETUNITNO'] = row.FLEETUNITNO
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "VEHICLE"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)


    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectHRO(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, CUST_NO, VEH_NO, STATUS, PAY_DATE, RODATE, MILAGE, TOTAL, TAX, DISP, "
                   "DISL, DISS, "
                   "SOURCE, SUPPLIES, TAX_PARTS, NTX_PARTS, TAX_LABOR, NTX_LABOR, TAX_SUBLET, NTX_SUBLET, ST, "
                   "PDTAXABLEPARTS, PDTAXABLELABOR, PDNONTAXPARTS, PDNONTAXLABOR  from HRO "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['CUST_NO'] = row.CUST_NO
        d['VEH_NO'] = row.VEH_NO
        d['STATUS'] = row.STATUS
        d['PAY_DATE'] = row.PAY_DATE
        d['RODATE'] = row.RODATE
        d['MILAGE'] = row.MILAGE
        d['TOTAL'] = row.TOTAL
        d['TAX'] = row.TAX
        d['DISP'] = row.DISP
        d['DISL'] = row.DISL
        d['DISS'] = row.DISS
        d['SOURCE'] = row.SOURCE
        d['SUPPLIES'] = row.SUPPLIES
        d['TAX_PARTS'] = row.TAX_PARTS
        d['NTX_PARTS'] = row.NTX_PARTS
        d['TAX_LABOR'] = row.TAX_LABOR
        d['NTX_LABOR'] = row.NTX_LABOR
        d['TAX_SUBLET'] = row.TAX_SUBLET
        d['NTX_SUBLET'] = row.NTX_SUBLET
        d['ST'] = row.ST
        d['PDTAXABLEPARTS'] = row.PDTAXABLEPARTS
        d['PDTAXABLELABOR'] = row.PDTAXABLELABOR
        d['PDNONTAXPARTS'] = row.PDNONTAXPARTS
        d['PDNONTAXLABOR'] = row.PDNONTAXLABOR
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "HRO"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectHLABOR(uin,output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, LABOR_OP, DESC_LINES, ITEM_NO, RATE, TIME, CHARGE, DECLINED, CATAGORY, "
                   "TECH_NO "
                   "from HLABOR "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['LABOR_OP'] = row.LABOR_OP
        d['DESC_LINES'] = row.DESC_LINES
        d['ITEM_NO'] = row.ITEM_NO
        d['RATE'] = row.RATE
        d['TIME'] = row.TIME
        d['CHARGE'] = row.CHARGE
        d['DECLINED'] = row.DECLINED
        d['CATAGORY'] = row.CATAGORY
        d['TECH_NO'] = row.TECH_NO
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "HLABOR"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectHPARTS(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, PARTNO, PARTDESC, ITEM_NO, MANUFACTURER, PARTCOST, PARTPRICE, "
                   "QUANITY, INVOICE, SUPPLIER, ST  from HPARTS "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['PARTNO'] = row.PARTNO
        d['PARTDESC'] = row.PARTDESC
        d['ITEM_NO'] = row.ITEM_NO
        d['MANUFACTURER'] = row.MANUFACTURER
        d['PARTCOST'] = row.PARTCOST
        d['PARTPRICE'] = row.PARTPRICE
        d['QUANITY'] = row.QUANITY
        d['INVOICE'] = row.INVOICE
        d['SUPPLIER'] = row.SUPPLIER
        d['ST'] = row.ST
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "HPARTS"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectINV(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, PARTNO, PARTDESC, MANUFACTURER, PARTCOST, PARTPRICE, REORDER, "
                   "ON_HAND  from INV "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['PARTNO'] = row.PARTNO
        d['PARTDESC'] = row.PARTDESC
        d['MANUFACTURER'] = row.MANUFACTURER
        d['PARTCOST'] = row.PARTCOST
        d['PARTPRICE'] = row.PARTPRICE
        d['REORDER'] = row.REORDER
        d['ON_HAND'] = row.ON_HAND
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "INV"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectSUPPLIER(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, SUP_NAME, SUPPLIER, EMAIL, ADDRESS, CITY, STATE, "
                   "ZIP, AC1, PHONE1  from SUPPLIER "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['SUP_NAME'] = row.SUP_NAME
        d['SUPPLIER'] = row.SUPPLIER
        d['EMAIL'] = row.EMAIL
        d['ADDRESS'] = row.ADDRESS
        d['CITY'] = row.CITY
        d['STATE'] = row.STATE
        d['ZIP'] = row.ZIP
        d['AC1'] = row.AC1
        d['PHONE1'] = row.PHONE1
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "SUPPLIER"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectLABOR_OP(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, LABOR_OP, CATAGORY, DESC_LINES  from LABOR_OP "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['LABOR_OP'] = row.LABOR_OP
        d['CATAGORY'] = row.CATAGORY
        d['DESC_LINES'] = row.DESC_LINES
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "LABOR_OP"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectERO(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, CUST_NO, VEH_NO, STATUS, RODATE, MILAGE, TOTAL, TAX, DISP, "
                   "DISL, DISS, "
                   "SOURCE, SUPPLIES, TAX_PARTS, NTX_PARTS, TAX_LABOR, NTX_LABOR, TAX_SUBLET, NTX_SUBLET, ST  from ERO "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['CUST_NO'] = row.CUST_NO
        d['VEH_NO'] = row.VEH_NO
        d['STATUS'] = row.STATUS
        d['RODATE'] = row.RODATE
        d['MILAGE'] = row.MILAGE
        d['TOTAL'] = row.TOTAL
        d['TAX'] = row.TAX
        d['DISP'] = row.DISP
        d['DISL'] = row.DISL
        d['DISS'] = row.DISS
        d['SOURCE'] = row.SOURCE
        d['SUPPLIES'] = row.SUPPLIES
        d['TAX_PARTS'] = row.TAX_PARTS
        d['NTX_PARTS'] = row.NTX_PARTS
        d['TAX_LABOR'] = row.TAX_LABOR
        d['NTX_LABOR'] = row.NTX_LABOR
        d['TAX_SUBLET'] = row.TAX_SUBLET
        d['NTX_SUBLET'] = row.NTX_SUBLET
        d['ST'] = row.ST
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    #fileName = "ero.json"
    tableName = "ERO"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectELABOR(uin,output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, LABOR_OP, DESC_LINES, ITEM_NO, RATE, TIME, CHARGE, DECLINED, CATAGORY  from ELABOR "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['LABOR_OP'] = row.LABOR_OP
        d['DESC_LINES'] = row.DESC_LINES
        d['ITEM_NO'] = row.ITEM_NO
        d['RATE'] = row.RATE
        d['TIME'] = row.TIME
        d['CHARGE'] = row.CHARGE
        d['DECLINED'] = row.DECLINED
        d['CATAGORY'] = row.CATAGORY
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "ELABOR"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectEPARTS(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, PARTNO, PARTDESC, ITEM_NO, MANUFACTURER, PARTCOST, PARTPRICE, "
                   "QUANITY  from EPARTS "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['PARTNO'] = row.PARTNO
        d['PARTDESC'] = row.PARTDESC
        d['ITEM_NO'] = row.ITEM_NO
        d['MANUFACTURER'] = row.MANUFACTURER
        d['PARTCOST'] = row.PARTCOST
        d['PARTPRICE'] = row.PARTPRICE
        d['QUANITY'] = row.QUANITY
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "EPARTS"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectSOURCE(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, SOURCE, SOURCE_DES from SOURCE "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['SOURCE'] = row.SOURCE
        d['SOURCE_DES'] = row.SOURCE_DES
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "SOURCE"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectRO(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, CUST_NO, VEH_NO, STATUS, RODATE, MILAGE, TOTAL, TAX, DISP, "
                   "DISL, DISS, "
                   "SOURCE, SUPPLIES, TAX_PARTS, NTX_PARTS, TAX_LABOR, NTX_LABOR, TAX_SUBLET, NTX_SUBLET, ST  from RO "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['CUST_NO'] = row.CUST_NO
        d['VEH_NO'] = row.VEH_NO
        d['STATUS'] = row.STATUS
        d['RODATE'] = row.RODATE
        d['MILAGE'] = row.MILAGE
        d['TOTAL'] = row.TOTAL
        d['TAX'] = row.TAX
        d['DISP'] = row.DISP
        d['DISL'] = row.DISL
        d['DISS'] = row.DISS
        d['SOURCE'] = row.SOURCE
        d['SUPPLIES'] = row.SUPPLIES
        d['TAX_PARTS'] = row.TAX_PARTS
        d['NTX_PARTS'] = row.NTX_PARTS
        d['TAX_LABOR'] = row.TAX_LABOR
        d['NTX_LABOR'] = row.NTX_LABOR
        d['TAX_SUBLET'] = row.TAX_SUBLET
        d['NTX_SUBLET'] = row.NTX_SUBLET
        d['ST'] = row.ST
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    #fileName = "ero.json"
    tableName = "RO"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectLABOR(uin,output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, LABOR_OP, DESC_LINES, ITEM_NO, RATE, TIME, CHARGE, DECLINED, CATAGORY  from LABOR "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['LABOR_OP'] = row.LABOR_OP
        d['DESC_LINES'] = row.DESC_LINES
        d['ITEM_NO'] = row.ITEM_NO
        d['RATE'] = row.RATE
        d['TIME'] = row.TIME
        d['CHARGE'] = row.CHARGE
        d['DECLINED'] = row.DECLINED
        d['CATAGORY'] = row.CATAGORY
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "LABOR"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectPARTS(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, PARTNO, PARTDESC, ITEM_NO, MANUFACTURER, PARTCOST, PARTPRICE, "
                   "QUANITY  from PARTS "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['PARTNO'] = row.PARTNO
        d['PARTDESC'] = row.PARTDESC
        d['ITEM_NO'] = row.ITEM_NO
        d['MANUFACTURER'] = row.MANUFACTURER
        d['PARTCOST'] = row.PARTCOST
        d['PARTPRICE'] = row.PARTPRICE
        d['QUANITY'] = row.QUANITY
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "PARTS"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectEHRO(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, CUST_NO, VEH_NO, STATUS, RODATE, MILAGE, TOTAL, TAX, DISP, "
                   "DISL, DISS, "
                   "SOURCE, SUPPLIES, TAX_PARTS, NTX_PARTS, TAX_LABOR, NTX_LABOR, TAX_SUBLET, NTX_SUBLET, ST, "
                   "PDTAXABLEPARTS, PDTAXABLELABOR  from EHRO "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['CUST_NO'] = row.CUST_NO
        d['VEH_NO'] = row.VEH_NO
        d['STATUS'] = row.STATUS
        d['RODATE'] = row.RODATE
        d['MILAGE'] = row.MILAGE
        d['TOTAL'] = row.TOTAL
        d['TAX'] = row.TAX
        d['DISP'] = row.DISP
        d['DISL'] = row.DISL
        d['DISS'] = row.DISS
        d['SOURCE'] = row.SOURCE
        d['SUPPLIES'] = row.SUPPLIES
        d['TAX_PARTS'] = row.TAX_PARTS
        d['NTX_PARTS'] = row.NTX_PARTS
        d['TAX_LABOR'] = row.TAX_LABOR
        d['NTX_LABOR'] = row.NTX_LABOR
        d['TAX_SUBLET'] = row.TAX_SUBLET
        d['NTX_SUBLET'] = row.NTX_SUBLET
        d['ST'] = row.ST
        d['PDTAXABLEPARTS'] = row.PDTAXABLEPARTS
        d['PDTAXABLELABOR'] = row.PDTAXABLELABOR
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "EHRO"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectEHLABOR(uin,output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, LABOR_OP, DESC_LINES, ITEM_NO, RATE, TIME, CHARGE, DECLINED, CATAGORY, "
                   "TECH_NO "
                   "from EHLABOR "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['LABOR_OP'] = row.LABOR_OP
        d['DESC_LINES'] = row.DESC_LINES
        d['ITEM_NO'] = row.ITEM_NO
        d['RATE'] = row.RATE
        d['TIME'] = row.TIME
        d['CHARGE'] = row.CHARGE
        d['DECLINED'] = row.DECLINED
        d['CATAGORY'] = row.CATAGORY
        d['TECH_NO'] = row.TECH_NO
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "EHLABOR"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

def selectEHPARTS(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, PARTNO, PARTDESC, ITEM_NO, MANUFACTURER, PARTCOST, PARTPRICE, "
                   "QUANITY, INVOICE, SUPPLIER, ST  from EHPARTS "
                   "where "
                   "store_UIN = ?", uin)

    rows = cursor.fetchall()

    objects_list = []
    for row in rows:
        d = collections.OrderedDict()
        d['STORE_UIN'] = row.STORE_UIN
        d['RO_NO'] = row.RO_NO
        d['PARTNO'] = row.PARTNO
        d['PARTDESC'] = row.PARTDESC
        d['ITEM_NO'] = row.ITEM_NO
        d['MANUFACTURER'] = row.MANUFACTURER
        d['PARTCOST'] = row.PARTCOST
        d['PARTPRICE'] = row.PARTPRICE
        d['QUANITY'] = row.QUANITY
        d['INVOICE'] = row.INVOICE
        d['SUPPLIER'] = row.SUPPLIER
        d['ST'] = row.ST
        objects_list.append(d)
    j = json.dumps(objects_list)

    # datestr = datetime.datetime.now().strftime("%Y-%m-%d")
    # fileName = "ero.json"
    tableName = "EHPARTS"
    fileName = "%s_%s.json" % (uin, tableName)
    objects_filename = os.path.join(output_dir, fileName)

    with open(objects_filename, 'w') as fileOut:
        parsed = json.loads(j)
        pretty = json.dumps(parsed, indent=2, sort_keys=True)
        fileOut.write(pretty)

    conn.close()

while True:
    uin = input("Please enter a uin or 'enter' to quit: ")
    if not uin:
        break
    startTime = datetime.now()
    selectCustomer(uin)
    selectVehicle(uin)
    selectHRO(uin)
    selectHLABOR(uin)
    selectHPARTS(uin)
    selectINV(uin)
    selectSUPPLIER(uin)
    selectLABOR_OP(uin)
    # selectERO(uin)
    # selectELABOR(uin)
    # selectEPARTS(uin)
    selectSOURCE(uin)
    selectRO(uin)
    selectLABOR(uin)
    selectPARTS(uin)
    selectEHRO(uin)
    selectEHLABOR(uin)
    selectEHPARTS(uin)
    endTime = datetime.now() - startTime
    print('It took', endTime, ' seconds to run...')

Select allOpen in new window

thank you everyone for your contribution. I apologize if I'm a novice. just leaning on experts for help. and I sincerely appreciate everyone's input. extremely valuable. I think John's solution is going to work best for me at least in my immediate need. this script is a part time solution and will work modified as is for my needs. thanks everyone!