Python import csv to sql

Import a CSV File to SQL Server using Python

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=;' '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=;' '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:

product_id	product_name	price
1	Laptop	1200
2	Printer	200
3	Tablet	350
4	Keyboard	80
5	Monitor	400

Источник

How to import data from CSV to SQL Server in Python ?

In this tutorial, we will learn how to import data from CSV files into SQL Server using only Python scripts. By the end of all the steps, you will know how to connect to SQL Server and read data from flat files using Python.

1. Import the necessary Python libraries

First, we need to import the necessary libraries. We will use pandas to read the data from the CSV file and pyodbc to connect to the SQL Server database and execute SQL commands.

import pandas as pd import pyodbc

2. Define the path to the CSV source file and create it

Next, we need to define the path to the CSV file that we want to import. In this example, we will assume that the CSV file is located on the same Windows machine as our Python development environment.

csv_file = 'c:\data\employees.csv'

For example, you can use a very simple file like the one below. It was used in the previous tutorial on how to export data into CSV format using Python. The data can look like this, i.e. with 4 columns:

id,name,department,salary 1,John Smith,Sales,50000 2,Jane Doe,Marketing,60000 3,Bob Johnson,IT,70000 4,Alice Wong,HR,55000

3. Connect to the SQL Server database

Now, we need to connect to the SQL Server database using the pyodbc Python module. We will need to provide the connection details, such as the server name, database name, username, and password. Or in my case I’m using a Windows authentication, like in the script below.

# declare some variables to store the connections details driver = 'SQL Server' server = 'localhost' database = 'Expert-Only' # connect to the local SQL Server database connection = pyodbc.connect(f'DRIVER=;' f'SERVER=;' f'DATABASE=;' f'Trusted_Connection=yes;')

4. Create the SQL Server target table

If the SQL Server table does not exist yet, of course we need to create it. In this example, we will assume that the SQL Server table has the same structure as the CSV file, and we will create it using the following SQL command.

The goal is to avoid errors by checking if the table already exists or not in the database. So we are sure to drop the table and create the proper structure without any errors. This specific code below first attempts to drop the table employees if it exists, and then it creates the table with the specified schema.

drop_table_query = """ DROP TABLE IF EXISTS employees """ create_table_query = """ CREATE TABLE employees ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(50), hire_date DATE ) """ cursor.execute(drop_table_query) cursor.execute(create_table_query)

5. Insert data from the CSV file into SQL Server

Next, we need to read the data from the CSV file using “pandas”, and then insert it into the SQL Server table using SQL commands. We will assume that the first row of the CSV file contains the column names. Then the last line of code simply commits the changes to the database. It is a best practice to always commit the code in the database and close connections.

df = pd.read_csv(csv_file) for index, row in df.iterrows(): insert_query = f""" INSERT INTO employees (id, first_name, last_name, email, hire_date) VALUES (, '', '', '', '') """ cursor.execute(insert_query) # Close the connection cursor.close() conn.close()

And that’s it! You should know all the steps now. The next paragraph is about a wrap up script to do all steps at once. Indeed, every step by itself will not work as you need to declare the connection before connecting to the MS SQL table for exemple.

Wrap up all the steps into one functional script

import pandas as pd import pyodbc # Define the path to the CSV file csv_file = 'c:\data\employees.csv' # import the pyodbc module to manage the odbc conection import pyodbc # declare some variables to store the connections details driver = 'SQL Server' server = 'localhost' database = 'Expert-Only' # connect to the local SQL Server database connection = pyodbc.connect(f'DRIVER=;' f'SERVER=;' f'DATABASE=;' f'Trusted_Connection=yes;') cursor = connection.cursor() # Create the SQL Server table (if it does not exist) create_table_query = """ CREATE TABLE employees ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(50), hire_date DATE ) """ cursor.execute(create_table_query) # Insert the data from the CSV file into the SQL Server table df = pd.read_csv(csv_file) for index, row in df.iterrows(): insert_query = f""" INSERT INTO employees (id, first_name, last_name, email, hire_date) VALUES (, '', '', '', '') """ cursor.execute(insert_query) # Commit the changes to the database connection.commit() # Close the connection cursor.close() connection.close()

Conclusion on importing a CSV file into SQL Server using Python

To conclude, it can be a very useful skill to know how to import data from CSV to SQL Server in Python. Especially for many data analysis and reporting tasks. By automating this Python process, save yourself a lot of time and effort compared to doing it manually using Excel. Indeed, Python provides a powerful set of tools for working with databases and data files. By mastering these tools, you can greatly enhance your productivity as a data analyst or scientist.

Remember, when importing data from a CSV file into SQL Server, it is important to make sure that the SQL Server table has the same structure as the CSV file, and that the data types match. It is also a good idea to check the data for errors or inconsistencies before importing it into the database.

In this tutorial, we used pandas library to read the data from the CSV file and pyodbc to connect to SQL Server and execute SQL commands. We first defined the path to the CSV file, connected to the SQL Server database, created the SQL Server table. Then we inserted the data from the CSV file into the SQL Server table, and finally committed the changes to the database. All these steps are very similar to many integration programs.

Источник

SQLite3 Python

SQLite — компактная встраиваемая СУБД с открытым кодом.

• Не использует парадигму клиент-сервер
• Хранит всю базу данных (включая определения, таблицы, индексы и данные) в единственном стандартном файле на том компьютере, на котором исполняется программа
• Перед началом исполнения транзакции записи весь файл, хранящий базу данных, блокируется

В этой статье вы узнаете о том как работать с SQLite3 в Python 3.

Импорт из .csv файла

Допустим, у вас есть файл data.csv значения в котором разделены точкой с запятой.

Нужно импортировать содержимое этого файла в базу данных SQLite3

Вы можете найти в этой статье несколько способов сделать такой импорт.

Простейший пример

Создайте файл from_csv.py и копируйте туда код из примеров.

Запустить файл можно командой python3 from_csv.py

import csv , sqlite3 con = sqlite3.connect( ‘db/my.db’ ) cur = con.cursor() cur.execute( «CREATE TABLE t (picture, price, number);» ) # picture, price, number — это названия столбцов with open ( ‘data.csv’ , ‘r’ ) as fin: # csv.DictReader по умолчанию использует первую строку под заголовки столбцов dr = csv.DictReader(fin , delimiter= «;» ) to_db = [(i[ ‘picture’ ] , i[ ‘price’ ] , i[ ‘number’ ]) for i in dr] cur.executemany( «INSERT INTO t (picture, price, number) VALUES (?, ?, ?);» , to_db) con.commit() con.close()

В директории db должен появиться файл my.db

В этой базе данных должна быть таблица t повторяющая содержание вашего .csv файла

Более реальный пример

Теперь рассмотрим более близкий к реальности пример.

В таблице должен быть столбец id. Желательно генерировать его автоматически.

Также удобнее читать код, оформленный с помощью docstring

Рассмотрим файл partners.csv со списком сайтов

В файле по три значения на строку: name, url, area. Добавим эти столбцы в базу данных.

В названиях присутствуют кириллические символы, поэтому при открытии укажем кодировку encoding=«utf8»

Также не забываем сгенерировать id

Новый, более близкий к реальной жизни, файл friends.py будет выглядеть так:

import csv , sqlite3 con = sqlite3.connect(‘db/partners.db’) cur = con.cursor() cur.execute(«»»CREATE TABLE friends ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, url TEXT , area TEXT )»»») with open ( ‘partners.csv’ , ‘r’ , encoding=«utf8») as f: dr = csv.DictReader(f, delimiter=»;») to_db = [(i[‘name’], i[‘url’], i[‘area’]) for i in dr] cur.executemany(«INSERT INTO friends (name, url, area) VALUES (?, ?, ?);», to_db) con.commit() con.close()

Источник