66d1eec875
Since we are working with an Azure SQL database, we need to fill the generated customer data in a fitting schema. The schema will be described in more detail in an updated README file later. The added script uses `pyodbc` to connect to the database and create the tables. This requires a connection string, which will not be checked out to this repo for security reasons and must be obtained separately. Additionally, a script `test_sql_connection.py` is added with this commit, which is a simple utility to test the `pyodbc` connection.
112 lines
3.0 KiB
Python
112 lines
3.0 KiB
Python
"""Script to create the SQL schema in Azure SQL Database."""
|
|
|
|
import os
|
|
|
|
import pyodbc
|
|
|
|
schema_creation = """
|
|
CREATE TABLE Addresses (
|
|
ID INT PRIMARY KEY IDENTITY(1,1),
|
|
StreetName NVARCHAR(100),
|
|
HouseNumber NVARCHAR(10),
|
|
City NVARCHAR(50),
|
|
PostalCode NVARCHAR(10),
|
|
Longitude FLOAT,
|
|
Latitude FLOAT
|
|
);
|
|
|
|
CREATE TABLE Meters (
|
|
ID INT PRIMARY KEY IDENTITY(1,1),
|
|
Signature NVARCHAR(11),
|
|
MeterType NVARCHAR(3),
|
|
AddressID INT,
|
|
FOREIGN KEY (AddressID) REFERENCES Addresses(ID)
|
|
);
|
|
|
|
CREATE TABLE Customers (
|
|
ID INT PRIMARY KEY IDENTITY(1,1),
|
|
FirstName NVARCHAR(100),
|
|
LastName NVARCHAR(100),
|
|
GasMeterID INT,
|
|
EltMeterID INT,
|
|
FOREIGN KEY (GasMeterID) REFERENCES Meters(ID),
|
|
FOREIGN KEY (EltMeterID) REFERENCES Meters(ID)
|
|
);
|
|
|
|
CREATE TABLE Readings (
|
|
ID INT PRIMARY KEY IDENTITY(1,1),
|
|
CustomerID INT,
|
|
MeterID INT,
|
|
ReadingDate DATE,
|
|
ReadingValue INT,
|
|
FOREIGN KEY (CustomerID) REFERENCES Customers(ID),
|
|
FOREIGN KEY (MeterID) REFERENCES Meters(ID)
|
|
);
|
|
"""
|
|
|
|
|
|
def create_tables(cursor: pyodbc.Cursor, schema: str) -> None:
|
|
"""Create database tables based on the provided schema.
|
|
|
|
This function takes a database schema as a string and executes each table
|
|
creation statement separately. It handles cases where tables already exist
|
|
and reports this information.
|
|
|
|
Parameters
|
|
----------
|
|
cursor : pyodbc.Cursor
|
|
A pyodbc cursor object used to execute SQL commands.
|
|
schema : str
|
|
A string containing SQL statements for table creation, separated by
|
|
semicolons.
|
|
|
|
Returns
|
|
-------
|
|
None
|
|
This function doesn't return any value.
|
|
|
|
Raises
|
|
------
|
|
pyodbc.ProgrammingError
|
|
If there's an error in executing the SQL statements, other than
|
|
"table already exists".
|
|
|
|
Notes
|
|
-----
|
|
- The function assumes the existence of a global 'conn' object for
|
|
committing changes to the database.
|
|
- It prints a message if a table already exists instead of raising an error.
|
|
"""
|
|
for table_creation in schema.split(";"):
|
|
if table_creation.strip():
|
|
try:
|
|
cursor.execute(table_creation)
|
|
conn.commit()
|
|
except pyodbc.ProgrammingError as e:
|
|
if "There is already an object named" in str(e):
|
|
print(f"Table already exists: {table_creation.split('(')[0].split()[-1]}")
|
|
else:
|
|
raise
|
|
|
|
|
|
if __name__ == "__main__":
|
|
|
|
# Connect to the database
|
|
connection_string = os.environ.get("AZURE_SQL_CONNECTION_STRING")
|
|
|
|
try:
|
|
conn = pyodbc.connect(connection_string)
|
|
cursor = conn.cursor()
|
|
print("Connected to Azure SQL Database successfully!")
|
|
|
|
# Example query
|
|
create_tables(cursor, schema_creation)
|
|
|
|
except pyodbc.Error as e:
|
|
print(f"Error connecting to Azure SQL Database: {e}")
|
|
finally:
|
|
if "cursor" in locals():
|
|
cursor.close()
|
|
if "conn" in locals():
|
|
conn.close()
|