Connecting to SQL Server using Python

Updating record from CSV file to SQL Server using Python

In this post, I will be showing you how to read a CSV file. And then insert the records to SQL Server.

Starting with reading the CSV file from my desktop. And adding a few transformations to the data. Before we do that make sure the below libraries are available on your IDE.

• pandas
• pyodbc
• datetime

Pandas: Using it for adding the read data to a dataframe and apply a few changes like filling the blanks, setting the datatypes of the columns, etc.

Pyodbc: To easily set a connection to data sources with an ODBC driver.

Datetime:  To calculate the time taken to complete the task of inserting the records into the database.

Below is the snapshot of the CSV data saved as "Transaction_File"

Transaction_File

Below is the snapshot of the python code to insert each record to the server:

In the above snap, the data is read and loaded to a data frame called df1, and the blank values are filled with "NA" using the .fillna() function. And setting the datatype of the entire data frame to string to match the data type of the existing or created table.

Then providing the required credentials details to the connection variable "cnxn" and checking if the table exists or not in the schema. If the table doesn't exist then create the table.

Using for loop to insert each record to the database, before doing do checking if the "AccountId (Primary Key)" exists in the server table or not. If exists then move to the next iteration else insert the records. Once all the above lines of code are executed then printing the time taken to finish the task.

Updating bulk records to the server using executemany function

Executemany is a function used in python to update multiple or bulk records to the server, before which the data should be store in a list to update the records.

Below is the snapshot of the code with executemany function

Here the data from dataframe "df1" is converted to a list using the .tolist() function and then passing the list of values to the insert query to update the database. 

The above code can also be used in Power BI in the editor to export the data from query to server post few transformations.