How to Store Pandas Dataframe as Table into Azure SQL Server?

How to Store Pandas Dataframe as Table into Azure SQL Server

We can load and store pandas dataframe as Table into Azure SQL Server using JDBC and ODBC. To achieve it we can download the odbc or jdbc driver from Microsoft space. Since ODBC is multi programming language-supportive interface hence most of the developers prefer this except for Java development. In this article, we will explore the solution step by step.

Store pandas dataframe as Table into Azure SQL Server ( Solution Step by Step )

It requires three things primarily ODBC Driver, Connection String, and business logic ( dataframe, etc ). Let’s explore.

Step 1: Install pyodbc and download the ODBC driver –

To begin with this solution we need to install pyodbc python library using the below command.

pip install pyodbc

We can download compatible drivers from here. After Downloading we can install this as a Window Application.



Step 2.  Extracting Credential from Azure SQL –

We can get this credential from AZURE Portal except the password.

connection string azure
connection string azure


we can extract this information into the code like below.

server_name = ''
database_name = 'your_database_name'
username = 'your_username'
password = 'your_password'
driver = '{ODBC Driver 18 for SQL Server}

Note : You can only make the connection if azure firewall has config for the IP of the server / your local. If not we can configure as below.

azure firewall
azure firewall

Step 3: Creating connection with Azure SQLand create table in python –

Use the below lines for cursor creation and we will run the SQL query for table creation all together in this section.

connection = pyodbc.connect(connection_string)

cursor = connection.cursor()

#table creation SQL builder
table_name = 'ssample_d_table'
create_table_query = f'''
CREATE TABLE {table_name}(
    Column1 INT,
    Column2 NVARCHAR(255)

It will create table (sample_d_table ) into AZURE SQL with two columns.

table creation in azure sql
table creation in azure sql


Step 4 : Storing dataframe into the AZURE SQL Table –

We will create a dataframe with the same scheme as the table and then iterate it row-wise row and then build a dynamic query simply as below.

data = {'Column1': [1, 2, 3, 4, 5],
        'Column2': ['A', 'B', 'C', 'D', 'E']}
df = pd.DataFrame(data)
for index, row in df.iterrows():
    insert_query = f"INSERT INTO {table_name} (Column1, Column2) VALUES (?, ?)"
    values = (row['Column1'], row['Column2'])
    cursor.execute(insert_query, values)


Data Science Learner Team

Join our list

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

Thank you for signup. A Confirmation Email has been sent to your Email Address.

Something went wrong.

Meet Abhishek ( Chief Editor) , a data scientist with major expertise in NLP and Text Analytics. He has worked on various projects involving text data and have been able to achieve great results. He is currently manages, where he and his team share knowledge and help others learn more about data science.
Thank you For sharing.We appreciate your support. Don't Forget to LIKE and FOLLOW our SITE to keep UPDATED with Data Science Learner