Python : Insert dataframe data into MySQL table 



Dataframe type in python is so useful to data processing and it’s possible to insert data as dataframe into MySQL . There is a sample of that.


Python 3.7.3
MySQL 5.5.62

Step1 : Making the table

Defining a table like the following.

> CREATE TABLE testdb.mysql_table(
	col1 int
	,col2 int
	,col3 int

Step2 : Making data

Making data for insert from python. the data should be the same type as the table you will insert it. The column name of dataframe is also same as the table if they are different you will get some error when you execute insert. These are sample code to make the dataframe.

import pandas as pd
df = pd.DataFrame([[1,2,3],[4,5,6]],columns=['col1','col2','col3'])
   0  1  2
0  1  2  3
1  4  5  6

Step3 : Inserting data into MySQL table

To insert dataframe into the table you have to import sqlalchemy. You can define the connection information with create_engine method of sqlalchemy. This is a sample code.

from sqlalchemy import create_engine

#engine = create_engine('mysql://<user>:<password>@<host>/<database>?charset=utf8')
engine = create_engine('mysql://user:password@localhost/test_db?charset=utf8')

#df.to_sql(<table_name>,con=engine, if_exists='append', index=False)
df.to_sql('testdb.mysq_table',con=engine, if_exists='append', index=False)
   col1  col2  col3
0     1     2     3
1     4     5     6

That’s all.

Additional explanation

If there is no table at the database you are inserting dataframe, sqlalchemy can create table automatically so it works.




Your email address will not be published. Required fields are marked *


Connection to PostgreSQL, Oracle&MySQL from Python

Contents1 Overview2 How to connect PostgreSQL3 Connect to MySQL4 Oracleの場合 Overview There are some samples to connect PostgreSQL, Oracle, MySQL from Python. How to connect PostgreSQL Package installation pip install psycopg2 Example import psycopg2 HOST = ‘your_host’ PORT = ‘5432’ DB_NAME = ‘your_db_name’ USER = ‘your_user_name’ PASSWORD = ‘your_password’ conn = psycopg2.connect("host=" + HOST + " port=" + PORT + " dbname=" + DB_NAME + " user=" + USER + " password=" + PASSWORD ) cur = conn.cursor() cur.execute("select version()") rows = cur.fetchall() cur.close() conn.close() print(rows) Connect to MySQL Package installation pip install mysqlclient Example import MySQLdb HOST = ‘your_host’ …

Bulk insert to Oracle with Python

Contents1 Overview2 Environments3 Step1 : Creating the table4 Step2 : Insert in bulk with executemany5 Summary Overview There is an example code to insert data in bulk to Oracle database with python cx_Oracle Environments python 3.7.3Oracle 18c Express Edition Step1 : Creating the table Creating the table ‘oracle_insert’ in the schema ‘USER01’. CREATE TABLE USER01.ORACLE_INSERT( col1 int ,col2 int ,col3 int ) Step2 : Insert in bulk with executemany dataset is the dataset to be insertedMaking a connection to database with cx_OracleInserting in bulk with using cur.executemany() import cx_Oracle dataset =[ [1,2,3] ,[4,5,6] ,[7,8,9] ,[10,11,12] ,[13,14,15] ,[16,17,18] ,[19,20,21] ] HOST …

【Python】Transforming datetime to date and time with pandas dateframe

Contents1 Original Data2 Transforming dataframe to date and time. Original Data This code is to make sample dataframe. import pandas as pd import datetime as dt df = pd.DataFrame([dt.datetime(2020,6,1,0,0,0),dt.datetime(2020,6,2,10,0,0),dt.datetime(2020,6,3,15,0,0)],columns=[’datetimes’])     datetimes 0 2020-06-01 00:00:00 1 2020-06-02 10:00:00 2 2020-06-03 15:00:00 Transforming dataframe to date and time. It can realize to use lambda & apply functions like following. df[’dates’] = df[’datetimes’].apply(lambda x :,x.month, df[’times’] = df[’datetimes’].apply(lambda x : dt.time(x.hour,x.minute,x.second))      datetimes      dates   times 0 2020-06-01 00:00:00 2020-06-01 00:00:00 1 2020-06-02 10:00:00 2020-06-02 10:00:00 2 2020-06-03 15:00:00 2020-06-03 15:00:00 If you want to get with string, these code is better. df[’dates’] …

【Python】Not getting all rows with BeautifulSoup

Contents1 Overview2 Environment3 Problem occurred example3.0.1 Result4 The way to fix it Overview When Scraping with Beautiful Soup a problem occurred like not getting all rows of the table but a few of them. This example shows how to fix it Environment Python 3.7.3 Problem occurred example This is a example table you want to scrape. NumberName1Sato2Kato3Ito4Goto I saw the html code in a web browser by pushing F12 key. <table class="test_table"> <thead> <tr> <th>Number</th> <th>Name</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>Sato</td> </tr> </tbody> <tbody> <tr> <td>2</td> <td>Kato</td> </tr> </tbody> <tbody> <tr> <td>3</td> <td>Ito</td> </tr> </tbody> <tbody> <tr> <td>4</td> <td>Goto</td> …

【Python】Changing images periodically with tkinter

Contents1 Overview2 Python library to be used3 Step1 : Display a image with tkintertkinter4 Step2 : Changing images5 Summary Overview There is a good library in python to make GUI, that is tkinter. It can display images too. Here, we are trying to change images periodically with tkinter. Python library to be used Following three libraries are used. tkinter ‘tkinter’ is used to make GUI. PIL(pillow) ‘PIL’ is used to deal with images in the python. threading ‘threading’ is used to change images automatically. Detail about that will be explained later. By the way, if you are using anaconda which …

Language Switcher