There is more than one way to import a CSV file to SQL Server using Python. In this guide, you’ll see a simple technique to import your data using the following 2 Python libraries:
- Pandas – used to import the CSV file into Python and create a DataFrame
- Pyodbc – used to connect Python to SQL Server
Steps to Import a CSV file to SQL Server using Python
Step 1: Prepare the CSV File
To begin, prepare the CSV file that you’d like to import to SQL Server.
For example, let’s assume that a CSV file was prepared, where:
- The CSV file name is ‘products’
- The CSV file is stored under the following path: C:\Users\Ron\Desktop\Test\products.csv
In addition, the CSV file contains the following data:
product_id | product_name | price |
1 | Laptop | 1200 |
2 | Printer | 200 |
3 | Tablet | 350 |
4 | Keyboard | 80 |
5 | Monitor | 400 |
Step 2: Import the CSV File into a DataFrame
You may use the Pandas library to import the CSV file into a DataFrame.
Here is the code to import the CSV file for our example (note that you’ll need to change the path to reflect the location where the CSV file is stored on your computer):
import pandas as pd data = pd.read_csv (r'C:\Users\Ron\Desktop\Test\products.csv') df = pd.DataFrame(data) print(df)
This is how the DataFrame would look like in Python:
product_id product_name price
0 1 Laptop 1200
1 2 Printer 200
2 3 Tablet 350
3 4 Keyboard 80
4 5 Monitor 400
Step 3: Connect Python to SQL Server
To connect Python to SQL server, you’ll need the:
- Server Name. For demonstration purposes, let’s assume that the server name is: RON\SQLEXPRESS
- Database Name. The database name for our example would be: test_database
Here is the code to connect Python to SQL for our example:
import pyodbc conn = pyodbc.connect('Driver={SQL Server};' 'Server=RON\SQLEXPRESS;' 'Database=test_database;' 'Trusted_Connection=yes;') cursor = conn.cursor()
You may wish to check the following guide that explains the full steps to connect Python to SQL Server using pyodbc.
Step 4: Create a Table in SQL Server using Python
Next, add the syntax to create the table in SQL Server. This table will be used to store the imported data from the CSV file.
For our example, you can add the following syntax to create the ‘products‘ table:
cursor.execute(''' CREATE TABLE products ( product_id int primary key, product_name nvarchar(50), price int ) ''')
Note that whenever you run the code to create a table. You should only use that piece of the code once. Otherwise, you’ll get the error below:
ProgrammingError: (’42S01′, “[42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named ‘products’ in the database. (2714) (SQLExecDirectW)”)
Step 5: Insert the DataFrame Data into the Table
Here is the syntax to insert the DataFrame data (from step-2) into the products table:
for row in df.itertuples(): cursor.execute(''' INSERT INTO products (product_id, product_name, price) VALUES (?,?,?) ''', row.product_id, row.product_name, row.price ) conn.commit()
And here is the entire code to import the CSV file into SQL Server using Python:
import pandas as pd import pyodbc # Import CSV data = pd.read_csv (r'C:\Users\Ron\Desktop\Test\products.csv') df = pd.DataFrame(data) # Connect to SQL Server conn = pyodbc.connect('Driver={SQL Server};' 'Server=RON\SQLEXPRESS;' 'Database=test_database;' 'Trusted_Connection=yes;') cursor = conn.cursor() # Create Table cursor.execute(''' CREATE TABLE products ( product_id int primary key, product_name nvarchar(50), price int ) ''') # Insert DataFrame to Table for row in df.itertuples(): cursor.execute(''' INSERT INTO products (product_id, product_name, price) VALUES (?,?,?) ''', row.product_id, row.product_name, row.price ) conn.commit()
Run the code in Python (after making the adjustment to the path where your CSV file is stored, as well as making the change to your database connection info).
Step 6: Perform a Test
Let’s run a simple query to check that the values from the CSV file got imported into SQL Server:
select * from products
Here is the result:
product_id | product_name | price |
1 | Laptop | 1200 |
2 | Printer | 200 |
3 | Tablet | 350 |
4 | Keyboard | 80 |
5 | Monitor | 400 |