In this Python tutorial, we are going to learn how to import records from a CSV file to Microsoft SQL Server.

The script can also be adapted to import dataset from an Excel file, a text file, or any data file as we will be reading the dataset into a pandas dataframe first. And the database system is not limited to just SQL Server, it can be MySQL, Oracle, or any other database systems. I am using SQL Server because this is the database system I know the best.

Timestamp:
00:00 - Tutorial info
00:45 - Data source used in this video
02:15 - Table Creation SQL Statement
02:35 - Writing automation script in Python
03:20 - How to find out what drivers are available on your PC
03:45 - Get SQL Server Server Name
04:45 - Import data to DataFrame
07:45 - Data clean up
14:52 - Convert DataFrame object to List object
16:00 - Create connection string
19:45 - Create connection object
22:00 - Create cursor object
25:35 - Run the script for testing.

Source Code:

import pypyodbc as odbc # pip install pypyodbc
import pandas as pd # pip install pandas

"""
Step 1. Importing dataset from CSV
"""
df = pd.read_csv('Real-Time_Traffic_Incident_Reports.csv')

"""
Step 2.1 Data clean up
"""
df['Published Date'] = pd.to_datetime(df['Published Date']).dt.strftime('%Y-%m-%d %H:%M:%S')
df['Status Date'] = pd.to_datetime(df['Published Date']).dt.strftime('%Y-%m-%d %H:%M:%S')

df.drop(df.query('Location.isnull() | Status.isnull()').index, inplace=True)


"""
Step 2.2 Specify columns we want to import
"""
columns = ['Traffic Report ID', 'Published Date', 'Issue Reported', 'Location', 
            'Address', 'Status', 'Status Date']

df_data = df[columns]
records = df_data.values.tolist()


"""
Step 3.1 Create SQL Servre Connection String
"""
DRIVER = 'SQL Server'
SERVER_NAME = '<Server Name>'
DATABASE_NAME = '<Database Name>'

def connection_string(driver, server_name, database_name):
    conn_string = f"""
        DRIVER={{{driver}}};
        SERVER={server_name};
        DATABASE={database_name};
        Trust_Connection=yes;        
    """
    return conn_string

"""
Step 3.2 Create database connection instance
"""
try:
    conn = odbc.connect(connection_string(DRIVER, SERVER_NAME, DATABASE_NAME))
except odbc.DatabaseError as e:
    print('Database Error:')    
    print(str(e.value[1]))
except odbc.Error as e:
    print('Connection Error:')
    print(str(e.value[1]))


"""
Step 3.3 Create a cursor connection and insert records
"""

sql_insert = '''
    INSERT INTO Austin_Traffic_Incident 
    VALUES (?, ?, ?, ?, ?, ?, ?, GETDATE())
'''

try:
    cursor = conn.cursor()
    cursor.executemany(sql_insert, records)
    cursor.commit();    
except Exception as e:
    cursor.rollback()
    print(str(e[1]))
finally:
    print('Task is complete.')
    cursor.close()
    conn.close()

#python #sql