Saving Data from CSV File to SQL Database Table

 

In this article, how to save the data in the CSV file to the table in the sql database?  Let's do an example of this in Python.

Initially, type the following code in the command line to install Pandas and install Pandas.

py -3.9 -m pip install pandas

 

 

 

 

 

 

 

 

 

 

 

Figure 1

 

 

 

 

 

 

 

 

Figure2

 

 

import pandas as pd

import pyodbc

 

# Import CSV.

data = pd.read_csv (r'C:\Data\export_person.csv')  

dataFrame = pd.DataFrame(data)

 

# Connect to SQL Server

con1 = pyodbc.connect('Driver={SQL Server};'

                      'Server=SIRIUS\SQLEXPRESS02;'

                      'Database=master;'

                      'Trusted_Connection=yes;')

cursor = con1.cursor()

 

# Create table.

cursor.execute('''

            CREATE TABLE customers (

                  Id int primary key,

                  firstName nvarchar(50),

            lastName nvarchar(50),

            mail nvarchar(50),

                  )

               ''')

 

# Insert dataframe to table.

for row in dataFrame.itertuples():

    cursor.execute('''

                INSERT INTO customers (Id,firstName,lastName,mail)

                VALUES (?,?,?,?)

                ''',

                row.Id,

                row.FirstName,

                row.LastName,

                row.Mail

                )

con1.commit()

 

Hope to see you in the next article. Happy coding