FLASK _SQLALCHAMY_Microsot SQL Server - Executing Multiline Query

Heya @adorableceruleaneel,

To connect to a Microsoft SQL Server database from a Flask web application and execute SQL queries, including those for creating tables, altering procedures, and running stored procedures, you’ll need to use the appropriate Python libraries and handle database connections securely. Here’s a guide to set this up using pyodbc, a popular Python SQL Server database interface:

Step 1: Install Necessary Packages

First, you need to install Flask and pyodbc. If you haven’t already installed these, you can do so using pip:

pip install Flask pyodbc

Step 2: Configure Database Connection

To connect to your SQL Server, you’ll need to configure the connection string. Ensure that you have the necessary drivers installed on your system (e.g., ODBC Driver for SQL Server).

Here is a sample Flask application that sets up a connection to SQL Server:

from flask import Flask, jsonify, request
import pyodbc

app = Flask(__name__)

# Database configuration
server = 'your_server_address'
database = 'TESTDB'
username = 'your_username'
password = 'your_password'
driver = '{ODBC Driver 17 for SQL Server}'  # Adjust as per your driver version

# Connection string
conn_str = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'

# Function to execute a query
def execute_query(query):
    with pyodbc.connect(conn_str) as conn:
        with conn.cursor() as cursor:
            cursor.execute(query)
            if cursor.description:  # Checking if it's a SELECT query
                columns = [column[0] for column in cursor.description]
                results = [dict(zip(columns, row)) for row in cursor.fetchall()]
                return results
            else:
                conn.commit()  # Commit for INSERT, UPDATE, DELETE
                return None

@app.route('/create-table')
def create_table():
    query = """
    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    CREATE TABLE TestTable (
        Id int IDENTITY(1,1) NOT NULL,
        col1 varchar(10) NULL,
        col2 varchar(10) NULL
    ) ON [PRIMARY];
    """
    execute_query(query)
    return jsonify({"message": "Table created successfully"})

@app.route('/alter-procedure')
def alter_procedure():
    query = """
    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    ALTER PROCEDURE dbo.sp_TestSP
        @p1 VARCHAR(10),
        @p2 VARCHAR(10)
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT * FROM TestTable WHERE col1 = @p1 AND col2 = @p2;
        SELECT * FROM TestTable WHERE col1 = @p1;
    END;
    """
    execute_query(query)
    return jsonify({"message": "Procedure altered successfully"})

@app.route('/execute-procedure')
def execute_procedure():
    p1 = request.args.get('p1')
    p2 = request.args.get('p2')
    query = f"EXEC sp_TestSP @p1 = '{p1}', @p2 = '{p2}'"
    result = execute_query(query)
    return jsonify(result)

if __name__ == '__main__':
    app.run(debug=True)

Step 3: Adjust Queries and Use

• Remove GO Statements: Python doesn’t understand SQL’s GO command. It’s a batch separator used in SQL Server Management Studio, not part of the SQL language. Separate commands should be executed separately if needed.
• Security Considerations: Avoid SQL injection by using parameterized queries, especially for data manipulation based on user input.
• Multi-Statements Handling: For handling multiple statements or more complex transaction controls, you may need to manage transactions explicitly using pyodbc’s transaction control features.

Step 4: Running Your Flask Application

Run your Flask app by executing the Python script. Make sure to replace placeholders in the connection string with actual values.

python your_flask_app.py

You can then access endpoints defined in your Flask app to perform SQL operations. This setup provides a basic framework, and you might need to expand or adjust it based on your specific requirements, such as handling complex transactions or optimizing for performance.