automating azure pipelines: bicep tabular translation in python

autoflex.py

import pyodbc
import json

# Connect to your database - don't hardcode the creds!
conn = pydobc.connect(database="your_database", user="your_username", password="your_password", host="your_host", port="your_port")
cur = conn.cursor()

# List of your tables
tables = ["table1", "table2", "table3", "table4", "table5", "table6", "table7", "table8", "table9"]

mappings = []

# For each table
for table in tables:
    # Query the schema
    cur.execute(f"SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '{table}';")
    columns = cur.fetchall()

    # For each column
    for column in columns:
        # Generate the mapping
        mapping = {
            "source": {
                "name": column[0],
                "type": "String",
                "physicalType": column[1]
            },
            "sink": {
                "name": column[0],
                "type": "String",
                "physicalType": column[1]
            }
        }
        mappings.append(mapping)

# Output the mappings
print(json.dumps(mappings))