Read data from SQL Server in Python : Data from SQL server to Python
SQL (Structured Query Language) is a standard language for managing relational databases. It allows users to perform tasks such as querying data, inserting new records, updating existing records, and deleting records. SQL is used in a wide range of applications, including web development, business intelligence, data analysis, and more.
Python is a high-level, interpreted programming language known for its simplicity and versatility. It is widely used for web development, data analysis, artificial intelligence, scientific computing, and more. With its clear and readable syntax, Python facilitates rapid development and prototyping of various applications.
To access data from an SQL database in Python, you first need to install the pyodbc library, which allows you to establish connections with SQL servers. Once installed, you can proceed to retrieve data using either pandas or the cursor.execute function.
Read data from SQL Server in Python
Install PyODBC
pip install pyodbcImport pyodbc and pandas library
import pyodbc
import pandas as pdEstablish Database Connection
After installing PyODBC, you need to establish a connection to your SQL Server database. This involves specifying the server name, database name, username, password, etc.
server = 'Server Name'
database = 'Database Name'
username = 'User Name'
password = '#########'
#Connection String
connection = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = connection.cursor()
print("connection Established sucessfully")Reading Data with pandas
You can use the pandas library to read data directly into a DataFrame from a SQL query. This is a convenient method if you’re familiar with pandas and want to manipulate the data further.
import pandas as pd
# Example SQL query
sql_query = "SELECT * FROM your_table"
# Read data into DataFrame
df = pd.read_sql(sql_query, connection)
# Close the connection
connection.close()
# Now you can work with 'df', which contains your SQL data
You can change the SQL statement as needed. Above code display all the data from table in pandas dataframe.
Reading Data with cursor
Alternatively, you can execute SQL queries directly using the cursor’s execute method and fetch the results row by row.
sql = "select email from Tabale_Name"
cursor.execute(sql)
#fetchall fetch all the data from query
cursor.fetchall()
fetchall use to retrieve all the data from the query.
# Display the sql data using for loop.
for row in cursor:
print(row)Calling Stored Procedure Like a Select Statement in Python
You can directly call stored procedures in SQL Server from Python, treating them like regular SQL statements. Here’s how to do it:
sql ='exec procedure_name'
cursor.execute(sql)Courses :
Database Management System (DBMS)
