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.
It requires three things primarily ODBC Driver, Connection String, and business logic ( dataframe, etc ). Let’s explore.
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.
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}
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.
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