pandas で MariaDB の読み書き

データベースの作成

mariadb_create.py

#! /usr/bin/python
# -*- coding: utf-8 -*-
#
#	mariadb_create.py
#
#					Oct/01/2018
# ------------------------------------------------------------------
import sys
import pandas as pd
#
import mysql.connector
from sqlalchemy import create_engine
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
df = pd.DataFrame([
		["t1271","千葉",51476,"2003-9-25"],
		["t1272","勝浦",42573,"2003-3-16"],
		["t1273","市原",28471,"2003-6-21"],
		["t1274","流山",36872,"2003-8-27"],
		["t1275","八千代",24176,"2003-11-5"],
		["t1276","我孫子",13276,"2003-1-12"],
		["t1277","鴨川",91346,"2003-5-22"],
		["t1278","銚子",87192,"2003-12-18"]])

#print(df)
#
engine = create_engine('mysql+mysqlconnector://scott:tiger123@127.0.0.1/city')
#
#
df.to_sql(name='cities',con=engine,if_exists='replace',index=None)
#
#
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------

データベースの読み込み

mariadb_read.py

#! /usr/bin/python
# -*- coding: utf-8 -*-
#
#	mariadb_read.py
#
#					Oct/01/2018
# ------------------------------------------------------------------
import sys
import pandas as pd
#
from sqlalchemy import create_engine
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
engine = create_engine('mysql+mysqlconnector://scott:tiger123@127.0.0.1/city')
#
df=pd.read_sql_query('SELECT * FROM cities', con=engine)
print(df)
#
df.to_csv("tmp001.csv",header=None,index=None)
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------

確認したバージョン

$ python
Python 3.10.9 (main, Dec 19 2022, 17:35:49) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas
>>> pandas.__version__
'1.5.3'