How to create python program to fetch and display the data from sql server database

In this tutorial ,we will learn how to create python program to fetch and display the data from sql server database.

 

 

 

SQL Server is the most popular and widely used in many different platforms without failure, and in this tutorial, we will show python provides support to work with SQL server databases. python program is simple code and efficient compared to other languages and easy to use and understand.

 

In the day-to-day life needs to reduce the code and be efficient to work is main the programming and python should give the set of useful libraries and packages to minimize the use of code. 

 

Python need a sql server driver to access the database. Here I have sql server management studio 2018. And just you need to install the driver to add on python in your system environment.

pip install pyodbc

And install a more readable format to better understand the data. we need to install Tabulate function in python. it's easy to transform well-formatted text tables.

pip install tabulate

In the python file, we need to import both modules at the top of the script.

import pyodbc
from tabulate import tabulate

Now we need to connect the database in the program .use the connect() constructor to create the connection to the SQL server. 

connection = pyodbc.connect("""DRIVER={SQL Server};
SERVER=.\;DATABASE=school;Trusted_Connection=yes;""")

In the above, server name (local) so, use ./ to connect and database name, Here I named my database school and create the trusted connection.

classtable = "select * from class"
cursor=connection.cursor()
cursor.execute(classtable)

And Now, need to create a query execution function and going to take a SQL query in python as a string to use the pass them to the cursor to execute the statement and fetch the data from the database server. and create an array to print in the tabulate module in the grid to view the data in python.

import pyodbc
from tabulate import tabulate
connection = pyodbc.connect("""DRIVER={SQL Server};
SERVER=.\;DATABASE=school;Trusted_Connection=yes;""")
classtable = "select * from class"
cursor=connection.cursor()
cursor.execute(classtable)
result= cursor.fetchall()
insert=[result]
print(tabulate(insert, tablefmt='grid'))

import pyodbc
from tabulate import tabulate
connection = pyodbc.connect("""DRIVER={SQL Server};
SERVER=.\;DATABASE=school;Trusted_Connection=yes;""")
classtable = "select * from class"
cursor=connection.cursor()
cursor.execute(classtable)
result= cursor.fetchall()
insert=[result]
print(tabulate(insert, tablefmt='grid'))
print(tabulate(result, tablefmt='html'))

Fetch data from the sql server database

Here, the use of the connection strings and the cursor execute query data are fetched and print using of for loop in python separate rows data.

import pyodbc
from tabulate import tabulate
connection = pyodbc.connect("""DRIVER={SQL Server};
SERVER=.\;DATABASE=school;Trusted_Connection=yes;""")
classtable = "select * from class"
cursor=connection.cursor()
cursor.execute(classtable)
result= cursor.fetchall()
for row in result:
    print("id:", row[0])
    print("name:", row[1])
    print("class:", row[2])

Read also: PHP Connect to MYSQL Database