Python is a used programming language for data analysis and machine learning applications. By connecting Python with the SQL Server using the “pyodbc” library, we can easily manipulate the data in the SQL Server database from Python.
In this article, we will cover the basics of connecting to the SQL Server database using Python and “pyodbc” library, including setting up an SQL Server instance and performing basic CRUD operations on a table.
Creating a Table in SQL Server
Before we can connect to the SQL Server using Python, we need to create a table in the SQL Server that we can work with. Let us create a simple table called SHOP which contains two columns: ID (integer) and ITEMS (string).
ID INT NULL,
ITEMS NCHAR(10) NULL
);
GO
INSERT INTO SHOP(ID,ITEMS) VALUES(1, 'FootBall')
INSERT INTO SHOP(ID,ITEMS) VALUES(2, 'GelPen')
INSERT INTO SHOP(ID,ITEMS) VALUES(3, 'Copy')
INSERT INTO SHOP(ID,ITEMS) VALUES(4, 'Book')
INSERT INTO SHOP(ID,ITEMS) VALUES(5, 'Mask')
GO
SELECT * FROM SHOP
This code creates the SHOP table, inserts some sample data, and selects all the rows from the SHOP table. We can execute this code in SSMS to create a table.
Output:
1 1 FootBall
2 2 GelPen
3 3 Copy
4 4 Book
5 5 Mask
Using Python to Connect to SQL Server
Once we create the SHOP table in SQL Server, we can connect to it using Python and the pyodbc module. The pyodbc module is a Python module which allows us to connect to the SQL Server and execute the SQL queries.
We can install “pyodbc” by running the following command in the command prompt or terminal:
Once we install the pyodbc, we can use the following Python code to connect to the SQL Server:
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=DESKTOP-0NHNTAB;DATABASE=school;Trusted_Connection=yes;UID=username;PWD=password')
This code creates a connection to the SQL Server using the pyodbc.connect() function. The database name, server name, and authentication information are required to connect to the SQL Server.
Performing Basic CRUD Operations Using Python
1. Reading the Data from the SQL Server
Once we establish a connection to the SQL Server, we can perform the CRUD (Create, Read, Update, Delete) operations using Python and the pyodbc module.
To read the data from the SHOP table, use the following Python code:
print ("read")
cursor = conn.cursor()
cursor.execute("select * from dbo.SHOP")
for row in cursor:
print(f'row = {row}')
print()
2. Creating a Data in the SQL Server
We can also use Python to create a data in the SQL Server database. In this example, we use the “INSERT INTO” function to add a new row into the SHOP table.
print ("create")
cursor = conn.cursor()
cursor.execute(
'insert into dbo.SHOP(ID,ITEMS) values(?,?);',
(123, 'cat')
)
conn.commit()
read(conn)
3. Updating the Data in the SQL Server
We can use Python to update an existing data in the SQL Server database. In this example, we use the UPDATE statement to update the ITEMS column for a row with an ID which is equal to 123.
print("update")
cursor = conn.cursor()
cursor.execute(
'update dbo.SHOP set ITEMS = ? where ID= ?;',
('dog', 123)
)
conn.commit()
read(conn)
4. Deleting the Data from the SQL Server
We can also use Python to delete the data from the SQL Server database. The “DELETE” statement is required to delete all the rows from the SHOP table where the ID is greater than 5.
print("delete")
cursor = conn.cursor()
cursor.execute(
'delete from dbo.SHOP where ID > 5;'
)
conn.commit()
read(conn)
We can call the previous functions to perform the desired operations on the SQL Server database. Here is the complete Python code that connects to the SQL Server database, reads the data from the SHOP table, creates new data, updates an existing data, deletes the data, and closes the connection to the database.
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=DESKTOP-0NHNTAB;DATABASE=school;Trusted_Connection=yes;UID=username;PWD=password')
def read(conn):
print ("read")
cursor = conn.cursor()
cursor.execute("select * from dbo.SHOP")
for row in cursor:
print(f'row = {row}')
print()
def create(conn):
print ("create")
cursor = conn.cursor()
cursor.execute(
'insert into dbo.SHOP(ID,ITEMS) values(?,?);',
(123, 'cat')
)
conn.commit()
read(conn)
def update(conn):
print("update")
cursor = conn.cursor()
cursor.execute(
'update dbo.SHOP set ITEMS = ? where ID= ?;',
('dog', 123)
)
conn.commit()
read(conn)
def delete(conn):
print("delete")
cursor = conn.cursor()
cursor.execute(
'delete from dbo.SHOP where ID > 5;'
)
conn.commit()
read(conn)
read(conn)
create(conn)
update(conn)
delete(conn)
conn.close()
Output:
row = (1, 'FootBall ')
row = (2, 'GelPen ')
row = (3, 'Copy ')
row = (4, 'Book ')
row = (5, 'Mask ')
create
row = (1, 'FootBall ')
row = (2, 'GelPen ')
row = (3, 'Copy ')
row = (4, 'Book ')
row = (5, 'Mask ')
row = (123, 'cat ')
update
row = (1, 'FootBall ')
row = (2, 'GelPen ')
row = (3, 'Copy ')
row = (4, 'Book ')
row = (5, 'Mask ')
row = (123, 'dog ')
delete
row = (1, 'FootBall ')
row = (2, 'GelPen ')
row = (3, 'Copy ')
row = (4, 'Book ')
row = (5, 'Mask ')
Conclusion
Python scripting language can be used to connect to an SQL Server database. Using “pyodbc”, we can easily connect to and manipulate the data in an SQL Server database from Python. We covered the basics of setting up an SQL Server instance, creating a table in SSMS, and performing the CRUD operations on the table using Python. With the knowledge gained from this article, you should now have a foundation to build a more advanced data management application using Python and SQL Server.
