Python csv to mssql

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Simple Example on how to convert CSV to SQL in Python

leopck/csvSqlPython

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

csvSqlPython (Really should have named it better. It just helps transforming data from CSV format to SQL format)

Converting csv files to SQL using Python demo project

This is a simple project to demonstrate the use of Python Anaconda or Pandas for parsing or extracting csv files and converting it into SQL language.

By converting the csv files into SQL, it provides a faster means as well as a simpler way to control the datas inside the csv files. Since SQL handles all the algorithms and difficult data arrangement and joinings of datas, SQL would be one of the few good solutions to solve data management in Python.

Therefore in this project, we will be looking into Python pandas library to convert csv files into SQL to simplify the data handling.

pip install -r requirements.txt python example/test.py 

Some details for the script

function : makeFileIntoSQL(file, nameoftable, sqlengine)

Usage : converts csv files into SQL files

Example : makeFileIntoSQL(‘file1.csv’, ‘augdata’, disk_engine)

## augdata is the name of the table in the SQL, you can rename this to whatever you like. This would affect the SQL queries.

From pandas library

function : pandas.read_sql_query(‘SELECT * FROM augdata’, create_engine(‘sqlite:///name.db’))

Usage : make SQL query and save the results into a variable

Example : df = pd.read_sql_query(‘SELECT * FROM augdata’, disk_engine)

 from sqlalchemy import create_engine import pandas as pd disk_engine = create_engine('sqlite:///awesome.db') 

 import csvtosql as cs cs.makeFileIntoSQL('file1.csv', 'data', disk_engine) 

 data = pd.read_sql_query('SELECT * FROM augdata', disk_engine) print data ## To view the output of the query 

 cs.makeFileIntoSQL('file2.csv', 'data2', disk_engine) data2 = pd.read_sql_query('SELECT * FROM data2', disk_engine) print data2 

 import csvtosql as cs import pandas as pd from sqlalchemy import create_engine disk_engine = create_engine('sqlite:///awesome.db') cs.makeFileIntoSQL('file1.csv', 'data', disk_engine) data = pd.read_sql_query('SELECT * FROM data', disk_engine) print data cs.makeFileIntoSQL('file2.csv', 'data2', disk_engine) data2 = pd.read_sql_query('SELECT * FROM data2', disk_engine) print data2 

Fin. Enjoy your SQL querying.

Источник

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.

Источник

Вставка кадра данных Python в таблицу SQL

В этой статье описывается, как вставить кадр данных pandas в базу данных SQL с помощью пакета pyodbc в Python.

Предварительные требования

• Azure Data Studio. Перед выполнением установки ознакомьтесь со статьей Скачивание и установка Azure Data Studio.
• Выполните действия из статьи Образцы баз данных AdventureWorks, чтобы восстановить версию OLTP примера базы данных для вашей версии SQL Server. Проверить правильность восстановления базы данных можно с помощью запроса к таблице HumanResources.Department:

USE AdventureWorks; SELECT * FROM HumanResources.Department; 

Установка пакетов Python

1. В Azure Data Studio откройте новую записную книжку и подключитесь к ядру Python 3.
2. Выберите Управление пакетами.
3. В области Управление пакетами выберите вкладку Добавить новые.
4. Для каждого из следующих пакетов введите имя пакета, щелкните Поиск, а затем Установить.
   • pyodbc
   • pandas

Создание примера CSV-файла

Скопируйте следующий текст и сохраните его в файл с именем department.csv .

DepartmentID,Name,GroupName, 1,Engineering,Research and Development, 2,Tool Design,Research and Development, 3,Sales,Sales and Marketing, 4,Marketing,Sales and Marketing, 5,Purchasing,Inventory Management, 6,Research and Development,Research and Development, 7,Production,Manufacturing, 8,Production Control,Manufacturing, 9,Human Resources,Executive General and Administration, 10,Finance,Executive General and Administration, 11,Information Services,Executive General and Administration, 12,Document Control,Quality Assurance, 13,Quality Assurance,Quality Assurance, 14,Facilities and Maintenance,Executive General and Administration, 15,Shipping and Receiving,Inventory Management, 16,Executive,Executive General and Administration 

Создайте новую таблицу базы данных

1. Чтобы подключиться к базе данных AdventureWorks, выполните действия, описанные в разделе Подключение к SQL Server.
2. Создайте таблицу с именем HumanResources.DepartmentTest. Таблица SQL будет использоваться для вставки кадров данных.

CREATE TABLE [HumanResources].[DepartmentTest]( [DepartmentID] [smallint] NOT NULL, [Name] [dbo].[Name] NOT NULL, [GroupName] [dbo].[Name] NOT NULL ) GO 

Загрузка кадра данных из CSV-файла

С помощью пакета Python pandas создайте кадр данных, загрузите CSV-файл, а затем загрузите кадр данных в новую таблицу SQL HumanResources.DepartmentTest.

1. Подключитесь к ядру Python 3.
2. Вставьте следующий код в ячейку кода, указав в нем правильные значения для параметров server , database , username , password и расположение CSV-файла.

import pyodbc import pandas as pd # insert data from csv file into dataframe. # working directory for csv file: type "pwd" in Azure Data Studio or Linux # working directory in Windows c:\users\username df = pd.read_csv("c:\\user\\username\department.csv") # Some other example server values are # server = 'localhost\sqlexpress' # for a named instance # server = 'myserver,port' # to specify an alternate port server = 'yourservername' database = 'AdventureWorks' username = 'username' password = 'yourpassword' cnxn = pyodbc.connect('DRIVER=;SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) cursor = cnxn.cursor() # Insert Dataframe into SQL Server: for index, row in df.iterrows(): cursor.execute("INSERT INTO HumanResources.DepartmentTest (DepartmentID,Name,GroupName) values(. )", row.DepartmentID, row.Name, row.GroupName) cnxn.commit() cursor.close() 

Подтверждение данных в базе данных

Подключитесь к ядру SQL и базе данных AdventureWorks, а затем выполните следующую инструкцию SQL и убедитесь, что таблица была успешно заполнена данными из кадра данных.

SELECT count(*) from HumanResources.DepartmentTest; 

Источник