How to Connect Python to SQL Server Using pyodbc.

1. Installing the ODBC Driver for SQL Server

• One of the most convenient methods to connect to an external database or access cloud.

• Data from Python is via ODBC. has developed a range of ODBC Drivers for Python to work with databases and cloud services.

• 
  

• If you don't have Python installed on your machine, go to the Python website, download the appropriate installer and run it. You will also need to install the pyodbc module — the easiest way to do that is by using the pip install pyodbc command in the Python interactive mode.

• Next, you need to download the ODBC driver for SQL Server. To use the ODBC driver as a translation layer between the application and the database, you need to configure it by following the installation Instructions.

Connecting to SQL Server from Python using ODBC Driver for SQL Server .

Here’s an example to show you how to Connect to SQL Server in Python. First we import the pyodbc module, then create a connection to the database, insert a new row and read the contents of the Employee table while printing each row to the Python interactive console. To execute the script, you can type the code directly in the interactive console or add the code to a file with the .py extension and run the file from the command prompt.

Let’s review a simple example, where:

The server name is: RON\SQLEXPRESS

The database name is: test_database

The table name is: Employee

Step 1: Connect

import pyodbc conn = pyodbc.connect('Driver={SQL Server};' 'Server= RON\SQLEXPRESS;' 'Database= test_database;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute('SELECT * FROM Employee') for i in cursor:

print(i)

Step 2: Insert a row

Here's a simple example of how to execute an insert statement to test the connection to the database. Code for new record to the Employee table.

cursor = conn.cursor() cursor.execute("INSERT INTO Employee (EMPNO, ENAME, JOB, MGR) VALUES (500, 'Scott', 'Manager', 545)")

Step 3: Execute query

The cursor.execute() function retrieves rows from the select query on a dataset. The cursor.fetchone() function iterates over the result set returned by cursor.execute() while the print() function prints out all records from the table to the console.

• cursor = conn.cursor() cursor.execute("SELECT * FROM Employee”) row = cursor.fetchone() while row: print (row) row = cursor.fetchone()

From SQL to Pandas DataFrame

• You can take things further by going from SQL to Pandas DataFrame using pd.read_sql_query:

• import pandas as pd import pyodbc conn = pyodbc.connect('Driver={SQL Server};' 'Server=RON\SQLEXPRESS;' 'Database=test_database;' 'Trusted_Connection=yes;') df = pd.read_sql_query('SELECT * FROM Employee', conn) print(df) print(type(df))

• When applying pd.read_sql_query, don’t forget to place the connection string variable at the end. In our case, the connection string variable is conn.

• Conclusion and Additional Resources

• You have seen how to connect Python to SQL Server. Once you established this connection, you may start using SQL in Python to manage your data.

• You may also use Python to insert values into SQL Server table.