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.

we can extract this information into the code like below.

server_name = 'your_server_name.database.windows.net'
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.

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)
)
'''
cursor.execute(create_table_query)
connection.commit()

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

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)
    connection.commit()
cursor.close()
connection.close()

Thanks
Data Science Learner Team