Creating a connection string to a Microsoft SQL Server database in Python is a crucial step in developing applications that interact with SQL databases. In this tutorial, we will explore the process of creating a connection string and establishing a connection to a SQL Server database using Python.

Step 1: ODBC Driver Configuration

Before creating a connection with Python, we need to configure the ODBC driver on our machine. The ODBC Data Source Administrator is a tool that manages ODBC connections and data sources. We can use it to create and verify a data source for our SQL Server database.

To configure the ODBC driver, follow these steps:

1. Search for “ODBC Data Source Administrator” in your search bar and open it.
2. Select the “System DNS” tab and click “Add” to add a new data source.
3. Select “SQL Server” to create a SQL Data Source.
4. Name your data source and select a SQL instance installed on your machine.
5. Click “Next” and test your data source by clicking “Test Data Source”.
6. Take note of the “Server” value and “Data Source Name” value, as you will use these values in your Python connection string.

Step 2: Engine Configuration

The engine is the starting point of our SQLAlchemy application. It describes the connection pool and dialect for the Python Database API Specification (DBAPI), which communicates with the specified database.

To configure the engine, follow these steps:

1. Open Visual Studio Code and click “Ctrl + Shift + ` ” to open a new terminal.
2. Install the SQLAlchemy module by running the command “npm install sqlalchemy” in your terminal.
3. Create a Python file called “DBconnect.py” and import the necessary modules.
4. Use the following code to create a connection string:

import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc

conn = urllib.parse.quote_plus(
    'Data Source Name=MssqlDataSource;'
    'Driver={SQL Server};'
    'Server=POWERSERVER\POWERSERVER;'
    'Database=MSSQLDB;'
    'Trusted_connection=yes;'
)

try:
    coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
    print("Passed")
except:
    print("Failed!")

Step 3: Create a New Table in the Database

After configuring the engine, we can create a new table in the SQL Server database using the SQLAlchemy ORM for Python.

To create a new table, follow these steps:

1. Create a Python file called “CreateTable.py” and import the necessary modules.
2. Define the table structure using the SQLAlchemy Table construct.
3. Use the following code to create the table:

from DBconnect import coxn
from sqlalchemy import Table, Column, Integer, String, MetaData

meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key=True), 
   Column('name', String), 
   Column('lastname', String),
)

meta.create_all(coxn)

Step 4: Write to the SQL Table

Now that we have created a table in the SQL Server database, we can write data to it using the SQLAlchemy ORM.

To write data to the table, follow these steps:

1. Import the necessary modules and the table object from the “CreateTable.py” file.
2. Use the following code to insert data into the table:

from CreateTable import students
from DBconnect import coxn

ins = students.insert().values([
   {'name':'Bob','lastname':'Marley'},
   {'name':'Bob','lastname':'Dylan'}
])

conn = coxn.connect()
conn.execute(ins)

Step 5: Database and Security Best Practices

When working with SQL Server databases, it is important to follow best practices for database security and remote connections.

Here are some best practices to consider:

• Allow remote connections from specific IP addresses.
• Create a user with a password for the SQL Server instance.
• Consider using the contained database model for user logins.
• Regularly audit user permissions and activities.

Step 6: Creating a User

To create a user in the SQL Server database, follow these steps:

1. Open your command prompt window and run the following commands:

sqlcmd -S POWERSERVER\POWERSERVER -E
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
ALTER DATABASE [MSSQLDB] SET CONTAINMENT = PARTIAL;
GO
USE [MSSQLDB];
GO
CREATE USER Adam WITH PASSWORD = '<Your Password>';
GO

Step 7: Using the SQL Authentication Method

To connect to the SQL Server database from an external PC using SQL authentication, use the following connection string:

import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc

conn = urllib.parse.quote_plus(
    'Data Source Name=MssqlDataSource;'
    'Driver={SQL Server};'
    'Server=POWERSERVER\POWERSERVER;'
    'Database=MSSQLDB;'
    'Trusted_connection=yes;'
    'Username=Adam;'
    'Password=Pass1234word;'
)

try:
    coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
    print("Passed")
except:
    print("Failed!")

Conclusion

Creating a SQL Server connection string in Python is a straightforward process that allows you to interact with SQL databases efficiently. By following the steps outlined in this tutorial, you can establish a connection, create tables, and write data to your SQL Server database using Python.

Remember to follow best practices for database security and consider using the contained database model for user logins. Python and SQL are a powerful combination for developing applications that interact with databases, and there are many more Python libraries available for working with SQL.

Happy coding!