I want to INSERT a DataFrame into MSSQL

About this article

It was created after thinking that there would be a way to insert a large amount of data as easily as possible when you want to store the data in DataFrame in .read_csv etc. in MSSQL. bulk insert is not used assuming you are not authorized.

It takes about 30 minutes to INSERT 1 million rows. Please let me know if there is a better way.

mssql_insert.py


#! /usr/bin/env python
# -*- coding: utf-8 -*-
# python 3.5
import pymssql
import pandas as pd

#Preparation
table_name = '[db].[dbo].[sample_table]'
#Type specification of INSERT destination table
columns = ['%s', '%s', '%d', '%d', '%d', '%d']

#Create a DataFrame
df = pd.DataFrame(...)

#INSERT statement generation
args = dict(table=table_name, columns=', '.join(columns))
sql = 'INSERT INTO {table} VALUES ({columns})'.format(**args)

#Convert NaN to None
df = df.astype(object).where(pd.notnull(df), None)

params = [tuple(x) for x in df.values]

#SQL execution
cnn = pymssql.connect(host="server", user="sa", password="password", database="db")
cur = cnn.cursor()
cur.executemany(sql, params)
cnn.commit()
cur.close()

Recommended Posts

I want to INSERT a DataFrame into MSSQL
I want to print in a comprehension
I want to build a Python environment
I want to make matplotlib a dark theme
I want to easily create a Noise Model
I want to create a window in Python
I want to make a game with Python
I don't want to take a coding test
I want to create a plug-in type implementation
I want to easily find a delicious restaurant
I want to write to a file with Python
I want to upload a Django app to heroku
I want to embed a variable in a Python string
I want to easily implement a timeout in python
I want to iterate a Python generator many times
I want DQN Puniki to hit a home run
100 image processing knocks !! (021-030) I want to take a break ...
I want to give a group_id to a pandas data frame
I want to generate a UUID quickly (memorandum) ~ Python ~
I want to transition with a button in flask
I want to climb a mountain with reinforcement learning
I want to write in Python! (2) Let's write a test
I want to find a popular package on PyPi
I want to randomly sample a file in Python
I want to easily build a model-based development environment
I want to work with a robot in python.
I want to split a character string with hiragana
I want to install a package of Php Redis
[Python] I want to make a nested list a tuple
I want to manually create a legend with matplotlib
I want to send a business start email automatically
I want to run a quantum computer with Python
I want to bind a local variable with lambda
I want a mox generator
I want to solve Sudoku (Sudoku)
I want a mox generator (2)
7rep --Insert Dataframe To Elasitcsearch
I want to make a blog editor with django admin
I want to start a jupyter environment with one command
[Python] I want to get a common set between numpy
I want to start a lot of processes from python
I want to make a click macro with pyautogui (desire)
I want to automatically generate a modern metal band name
[Markov chain] I tried to read a quote into Python.
NikuGan ~ I want to see a lot of delicious meat! !!
I want to make a click macro with pyautogui (outlook)
I want to use a virtual environment with jupyter notebook!
I want to install a package from requirements.txt with poetry
I want to send a message from Python to LINE Bot
[Visualization] I want to draw a beautiful graph with Plotly
I want to make input () a nice complement in python
I want to create a Dockerfile for the time being.
I want to understand systemd roughly
I want to scrape images to learn
I want to do ○○ with Pandas
I want to copy yolo annotations
I want to debug with Python
I want to record the execution time and keep a log.
I want to import Google Keep memos into Bear (memo app)
I want to use a wildcard that I want to shell with Python remove
MacBookPro Setup After all I want to do a clean installation