pandas: SQLAlchemy 2.0 と連携

　次のページを参考にしました。
 [SQLAlchemy2.0以降を使ってread_sqlする方法](https://barorin-to.com/posts/how-to-use-read-sql-with-sqlalchemy-2-0-and-later/)

 ## 確認したバージョン

 ```text
 $ python
Python 3.12.5 (main, Aug  9 2024, 08:20:41) [GCC 14.2.1 20240805] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'2.0.31'
```

## Sqlite3

```py:sqlite3_read.py
#! /usr/bin/python
#
#	sqlite3_read.py
#
#					Sep/04/2024
# ------------------------------------------------------------------
import sys
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import text as q_text

# ------------------------------------------------------------------
def connection_db():
    """DB接続"""
    SQLALCHEMY_DATABASE_URL = "sqlite:///cities.db"
    engine = create_engine(
        SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
    )
    return engine

# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
engine = connection_db()

# ポイント
# SQLはSQLAlchemyのtext関数を通す
# engineにconnect()メソッドをつける
q = q_text("SELECT * FROM cities")
df = pd.read_sql(sql=q, con=engine.connect())

print(df)
print(df.shape)
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------
```

実行結果

```text
$ ./sqlite3_read.py
*** 開始 ***
      id  name  population    date_mod
0  t0711    郡山       52176   2003-4-12
1  t0712  会津若松       47935   2003-5-10
2  t0713    白河       21654   2003-6-14
3  t0714    福島       83672    2003-9-9
4  t0715   喜多方       42391    2003-8-4
5  t0716   二本松       35187   2003-1-21
6  t0717   いわき       87246   2003-7-23
7  t0718    相馬       91784  2003-10-26
8  t0719   須賀川       75823  2003-12-15
(9, 4)
*** 終了 ***
```

## MariaDB

```text:mariadb_read.py
#! /usr/bin/python
#
#	postgresql_read.py
#
#					Sep/04/2024
# ------------------------------------------------------------------
#
import sys
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import text as q_text

# ------------------------------------------------------------------
def connection_db():
	"""DB接続"""
	host ='localhost'
	data_base = 'city'
	user ='scott'
	password = 'tiger123'
#
	SQLALCHEMY_DATABASE_URL = "mysql://" + user + ":" + password + "@" + host + "/" + data_base
	engine = create_engine(SQLALCHEMY_DATABASE_URL)
	return engine
#
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
engine = connection_db()

# ポイント
# SQLはSQLAlchemyのtext関数を通す
# engineにconnect()メソッドをつける
q = q_text("SELECT * FROM cities")
df = pd.read_sql(sql=q, con=engine.connect())

print(df)
print(df.shape)
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------
```

実行結果

```text
$ ./mariadb_read.py 
*** 開始 ***
      id name  population   date_mod
0  t3321   岡山      519476 2003-03-20
1  t3322   倉敷      291835 2003-05-15
2  t3323   津山      863754 2003-08-18
3  t3324   玉野      469172 2003-01-09
4  t3326   井原      518397 2003-05-21
5  t3327   総社      248156 2003-07-23
6  t3328   高梁    89145700 2024-09-04
7  t3329   新見      463751 2003-12-15
(8, 4)
*** 終了 ***
```

## PostgreSQL

```py:postgresql_read.py
#! /usr/bin/python
#
#	postgresql_read.py
#
#					Sep/04/2024
# ------------------------------------------------------------------
#
import sys
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import text as q_text

# ------------------------------------------------------------------
def connection_db():
	"""DB接続"""
	host ='localhost'
	data_base = 'city'
	user ='scott'
	password = 'tiger123'
#
	SQLALCHEMY_DATABASE_URL = "postgresql://" + user + ":" + password + "@" + host + "/" + data_base
	engine = create_engine(SQLALCHEMY_DATABASE_URL)
	return engine
#
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
engine = connection_db()

# ポイント
# SQLはSQLAlchemyのtext関数を通す
# engineにconnect()メソッドをつける
q = q_text("SELECT * FROM cities")
df = pd.read_sql(sql=q, con=engine.connect())

print(df)
print(df.shape)
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------
```

実行結果

```text
$ ./postgresql_read.py 
*** 開始 ***
      id name  population   date_mod
0  t3461   広島       81394 2003-09-24
1  t3462   福山       47135 2003-05-15
2  t3463  東広島       28654 2003-02-08
3  t3464    呉       83152 2003-10-09
4  t3465   尾道       42791 2003-08-04
5  t3466   竹原       35687 2003-01-21
6  t3469   府中       71423 2003-12-15
7  t3468   大竹    89145700 2024-09-04
(8, 4)
*** 終了 ***
```