pythonのSQLAlchemyによる接続コネクションがうまくいかない

Question

実現したいこと

EOLに伴いpythonを3.8.0から3.12.0にversion upしたい
そのversion upに伴い各種モジュールもupdateしたい

問題事象

元々下記のversionでは実施できていたSQLAlchemyのcreate_eningeでMS SQLに対してコネクション生成し、to_sqlでのデータ挿入処理が、version up後は上手くできない

元々のversion
python:ver3.8.0
pandas:1.1.3
SQLAlchemy:1.4.22

OS：Debian buster
Server DB：v17.3

ケース1

SQLAlchemyで作成したcreate_engineコネクションを利用したto_sqlによるDBへのデータ挿入処理においてエラーが発生する
python:ver3.12.0
pandas:2.2.2
SQLAlchemy:1.4.22

OS：Debian buster
Server DB：v17.3

・警告内容

UserWarning : pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.

・エラー内容

'Engine' object has no attribute 'cursor'

ケース2

SQLAlchemyで作成したcreate_engineコネクションを利用したto_sqlによるDBへのデータ挿入処理自体は出来るが、トランザクション処理ができない
python:ver3.12.0
pandas:2.1.4
SQLAlchemy:1.4.36

OS：Debian buster
Server DB：v17.3

・警告内容

UserWarning : pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.

・エラー内容

'Connection' object has no attribute 'commit'

該当のソースコード(元々)

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine(接続情報)

df = pd.DataFrame(略)
tablename= 'test'

try:
    # 下記のコネクション微妙に違うかも。。
    df.to_sql(tablename, engine.raw_connection().engine, if_exists="append")

except Exception as ex:
    engine.raw_connection().rollback()

該当のソースコード(ケース１)

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine(接続情報)

df = pd.DataFrame(略)
tablename= 'test'

df.to_sql(tablename, engine)

該当のソースコード(ケース2)

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine(接続情報)

df = pd.DataFrame(略)
tablename= 'test'

try:
    with engine.begin() as conn:
        df.to_sql(tablename, engine, if_exists="append")

    conn.commit()

except Exception as ex:
    conn.rollback()

試したこと

pandas2.2以上において、SQLAlchemyが2.0以上じゃないと対応していない、というような記事を見つけたので、2.1.4にダウングレードしたらトランザクション処理なしであれば警告はできるが正常系では問題なし。ただし、トランザクション処理のやり方がわからない

逆にSQLAlchemyを2.0以上にすると今度はcreate_engineが作成できない。やり方が変わっているように見受けるが、その方法がわからない。

手打ちでコードやエラーなど記載しているので、若干のtypoあるかもだが、元々は動いていたので、create_engineで入力している情報間違いではない想定。警告も元々はなし。
おそらくversionの互換性の問題だと思っている。

教えて頂きたい事

1. pandasは2.0以上、SQLAlchemyは特にこだわりなしなので、どのようなバージョンでどのようなソースとすればトランザクション処理含めて動作するのか知りたい

2. 警告においてcreate_engineが作成できてないようなエラーが発生しているが、DB自体にアクセスはできているように見受けたので、この警告が出る理由は何なのか？どのようにしたらこの警告を解消できるのか？

その他

情報に不足あればご指摘下さい

頂いた回答に対しての追記

create_engineの方法間違っている？と思いまして、該当部分の記述追加します。元々のversionではうまくいったので、これが現在のバージョンだとダメなのか？などが頂いた情報を参考に試してもみたのですが、引き続き同事象が継続していました。

engine = create_engine(
    "mssql+pyodbc://{user_id}:{password}@{localhost}:{port}/{dbname}?driver={driver}".format(
        localhost = "略",
        port = "略",
        dbname = "略",
        userid = "略",
        password = "略",
        driver = "ODBC Driver 17 for SQL Server",
)

with句のインデントの指摘を頂いて、確かになと思ったので、下記も試してみたのですが、同様のエラーが生じました

with engine.begin() as conn:
    try:
        df.to_sql(tablename, engine, if_exists="append")

        conn.commit()

    except Exception as ex:
        conn.rollback()

'Connection' object has no attribute 'commit'

Comments

[FiroProchainezo]

```
with engine.begin() as conn:
df.to_sql(tablename, engine)
conn.commit()
```

この部分インデント合ってますか？

[python_heroku]

コメントありがとうございます。
指摘いただき、
with engine.begin() as conn:
df.to_sql(tablename, engine)
conn.commit()

こちらも試してみたのですが、同エラーが発生しておりました。

[FiroProchainezo]

コメント欄はインデントが保持されないので、ここにコードを書かれてもわからないです。

私のコメントは、 `with xxx as conn:`から`conn.commit()`部分のインデントが正しいかを確認しています。
`with`で開いたあと、`conn.commit()`実行前に`conn`が閉じていますが、意図した通りですか？
コードの転記ミス(質問作成にあたり手打ちしてたりしませんか？)がありませんか？
つまり、「インデントの位置は合ってますか？」という指摘です。

connection閉じてしまっているから、commitできないのような気がします。

> mssql*pyodbc:

URIのこの部分、`*`じゃなくて`+`じゃないですか？
何かを参考にしたんでしょうか？

エラーメッセージ見る限り、pandasがサポートしているURIになっていないように見えます。

[退会済みユーザー]

全然関係ありませんが、コメントでもブラウザによっては(確認した限りだとfirefoxでは)コピペするとインデントが分かったりしますよ。chromeでもconsoleで
console.log(document.querySelector('div.questionCommentListItem_comment__ZlKzZ').textContent);
とか
console.log(document.querySelector('div.questionCommentListItem_innerpanel__Zlq9W').textContent)
とかしたら見えます。裏技的な方法ですが…。

ただソースで修正した方がいいですね。

あとはdriverとdatabaseのバージョンの違いが書かれていないし、何より、どこまで何が出来ているのかの確認が出来ていません。そしてOSも明記しないといけません。
odbcで繋げるだけならSQLエディタでも出来るのでそこからなのか、それが出来たならpyodbcからなのか、それが出来たらSQLAlchemyなのか、それが出来たなら初めてpandasやpython自身の話になります。
全部まとめて上げておいて、出来ませんでした〜、それを他人に丸投げで聞いていては話になりません。

他人に聞くのであれば再現環境構築用のスクリプトを書き、誰でも全ケース再現できるようにしてから質問しましょう。
その手間が惜しいのであれば、どんなに時間がかかったとしても、自分で調べた方が早いのです。
有料のサポートを受ければ簡単な問答と詳細データ提示だけで調べてくれる場合もありますよ。
ただ今回のように再現環境の記述すらまともに出来ないのであれば、有料サポートでも解決できないかもしれません。

[python_heroku]

FireProchainezo様
コメント欄にインデントが保存されない件、失礼しました。
おっしゃる通り、そこ自体おかしいのでは？というのはありましたので、おそらく指摘事項の内容で修正してみて試してみましたが、同様のエラーが生じているように見受けました。
質問内容編集しております。そちらであれば試したインデントの形もご確認いただけるかと。

またcreate_engineについてもご指摘事項について誤記でしたので修正しております。

[python_heroku]

dameo様
コメントありがとうございます。
当方、普段からそこまでプログラミングやっている人間ではないもので、無作法失礼しました。
ただ、ご指摘頂き、いくつか気になりましたので、もしよろしければ教えて下さい。

前提として、元々の環境
python:ver3.8.0
pandas:1.1.3
SQLAlchemy:1.4.22
に関しては、DB接続、DB操作、トランザクション処理について出来ておりました。

そのため、論点の争点になるのが、今回のケースで言うとpandasやSQLAlchemyのversionパターンに伴うcreate_engineの作成方法ではないのか？と思ったのですが、違うのでしょうか？
特にpandas==2.1.4ではデータ操作まではできているので、つまりSQLAlchemyのコネクション関連の問題なのでは？と思っています。

環境構築用のScriptについては申し訳ございません。DockerFileなど用意できれば良いのですが、私の力量で作成できず。。
OSやDBについてのVersionは下記となります。

OS：Debian buster
DB：v17.3

[退会済みユーザー]

> create_engineの作成方法ではないのか？と思ったのですが、違うのでしょうか？
違います

> 環境構築用のScriptについては申し訳ございません。DockerFileなど用意できれば良いのですが、私の力量で作成できず。。

では質問もやめたほうがいいですね。

Answer 1

回答ではありません

環境構築＆調査用の中途半端なスクリプトです。
すでに一度解決済みになっているので、解説はしません。

mkdir data python_old python_new
chmod a+w data
cat >entry.sh <<EOF
entrypoint=/sleep.sh
kill_child() {
    output=\$(ps -o pid,pgid,args)
    pids=\$(
        echo "\$output" |
        sed '/ps -o pid,pgid,args *\$/d;/bash \\/entry.sh *\$/d' |
        awk '\$2~/^'\$\$'\$/{print \$1;}' |
        sed '/'\$\$'/d'
    )
    kill \$pids
}
trap "echo trapped;kill_child;wait;exit 1" INT TERM
bash -c "sleep infinity" &
wait
echo "entry finished"
exit 0
EOF
cat >docker-compose.yml <<EOF
services:
  db:
    image: mcr.microsoft.com/mssql/server:2019-latest
    volumes:
      - ./data:/var/opt/mssql
    ports:
      - 1433:1433
    environment:
      ACCEPT_EULA: Y
      MSSQL_PID: Express
      MSSQL_SA_PASSWORD: yourStrong(!)Password
  python_old:
    image: python:3.8
    volumes:
      - ./python_old:/home/python
      - ./entry.sh:/entry.sh
    user: "1000:1000"
    working_dir: /home/python
    environment:
      HOME: /home/python
    command: bash /entry.sh
  python_new:
    image: python:3.12
    volumes:
      - ./python_new:/home/python
      - ./entry.sh:/entry.sh
    user: "1000:1000"
    working_dir: /home/python
    environment:
      HOME: /home/python
    command: bash /entry.sh
EOF
docker compose up -d
SA_PASS='yourStrong(!)Password'
cat >wait_db_started.sh <<EOF
while ! /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '${SA_PASS}' </dev/null 2>/dev/null;do
    sleep 1
done
EOF
docker compose exec -T db bash -x <wait_db_started.sh
cat >initialize_db.sql <<EOF
CREATE DATABASE TestDB COLLATE Japanese_CI_AS;
go
SELECT Name from sys.databases;
go
CREATE LOGIN testuser WITH PASSWORD = '${SA_PASS}';
go
USE TestDB;
go
CREATE USER testuser FROM LOGIN testuser;
go
ALTER ROLE db_ddladmin ADD MEMBER testuser;
go
ALTER ROLE db_datawriter ADD MEMBER testuser;
go
ALTER ROLE db_datareader ADD MEMBER testuser;
go
EOF
docker compose exec -T db /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "${SA_PASS}" -e <initialize_db.sql
cat >initialize_tables.sql <<EOF
USE TestDB;
go
CREATE TABLE Inventory (id INT PRIMARY KEY, name NVARCHAR(50), quantity INT);
go
SET IMPLICIT_TRANSACTIONS ON;
go
INSERT INTO Inventory VALUES (1, 'banana', 150);
go
INSERT INTO Inventory VALUES (2, 'orange', 154);
go
INSERT INTO Inventory VALUES (3, 'バナナ', 180);
go
SELECT * FROM Inventory;
go
commit;
go
EOF
docker compose exec -T db /opt/mssql-tools/bin/sqlcmd -S localhost -U testuser -P "${SA_PASS}" -e <initialize_tables.sql
cat >setup_drivers.sh <<EOF
curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg
curl https://packages.microsoft.com/config/debian/12/prod.list | tee /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install -y msodbcsql18
ACCEPT_EULA=Y apt-get install -y mssql-tools18
apt-get install -y unixodbc-dev
apt-get install -y libgssapi-krb5-2
EOF
cat >setup_venv_1.sh <<EOF
if [ ! -d env1 ]; then
    python3 -m venv env1
fi
. env1/bin/activate
pip install -U pip setuptools
pip install sqlalchemy==1.4.22 pandas==1.1.3 pyodbc
deactivate
EOF
cat >setup_venv_2.sh <<EOF
if [ ! -d env2 ]; then
    python3 -m venv env2
fi
. env2/bin/activate
pip install -U pip setuptools
pip install sqlalchemy==1.4.22 pandas==2.2.2 pyodbc
deactivate
EOF
cat >setup_venv_3.sh <<EOF
if [ ! -d env3 ]; then
    python3 -m venv env3
fi
. env3/bin/activate
pip install -U pip setuptools
pip install sqlalchemy==1.4.36 pandas==2.1.4 pyodbc
deactivate
EOF
cat >setup_venv_4.sh <<EOF
if [ ! -d env4 ]; then
    python3 -m venv env4
fi
. env4/bin/activate
pip install -U pip setuptools
pip install sqlalchemy pandas pyodbc
deactivate
EOF
for service in python_old python_new; do
    docker compose exec -u root:root -T ${service} bash -x <setup_drivers.sh
    for venvsh in ./setup_venv_*.sh; do
        docker compose exec -T ${service} bash <${venvsh}
    done
    cat >$service/sqlalchemy_test_1.py <<EOF
import traceback
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine(
    "mssql+pyodbc://{user_id}:{password}@{host}:{port}/{dbname}?driver={driver}".format(
        host = "db",
        port = "1433",
        dbname = "TestDB",
        user_id = "testuser",
        password = "yourStrong(!)Password",
        driver = "ODBC Driver 18 for SQL Server"
    ),
    connect_args={'TrustServerCertificate': 'yes'}
)

df1 = pd.DataFrame([[4,"みかん", 300],], columns=["id", "name", "quantity"])
df2 = pd.DataFrame([[5,"りんご", 200],], columns=["id", "name", "quantity"])
df3 = pd.DataFrame([[6,"もも", 250],], columns=["id", "name", "quantity"])
tablename= 'inventory'

with engine.connect() as conn:
    with conn.begin() as tran:
        try:
            df1.to_sql(tablename, conn, index=False, if_exists="append")
            raise Exception("test")
            df2.to_sql(tablename, conn, index=False, if_exists="append")
            tran.commit()
        except Exception:
            tran.rollback()
            print(traceback.format_exc())
    with conn.begin() as tran:
        try:
            df2.to_sql(tablename, conn, index=False, if_exists="append")
            df3.to_sql(tablename, conn, index=False, if_exists="append")
            tran.commit()
            print("success")
        except Exception:
            tran.rollback()
            print(traceback.format_exc())
EOF
    cat >$service/sqlalchemy_test_2.py <<EOF
import traceback
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine(
    "mssql+pyodbc://{user_id}:{password}@{host}:{port}/{dbname}?driver={driver}".format(
        host = "db",
        port = "1433",
        dbname = "TestDB",
        user_id = "testuser",
        password = "yourStrong(!)Password",
        driver = "ODBC Driver 18 for SQL Server"
    ),
    connect_args={'TrustServerCertificate': 'yes'}
    #, isolation_level="READ COMMITTED"
)

df1 = pd.DataFrame([[4,"みかん", 300],], columns=["id", "name", "quantity"])
df2 = pd.DataFrame([[5,"りんご", 200],], columns=["id", "name", "quantity"])
df3 = pd.DataFrame([[6,"もも", 250],], columns=["id", "name", "quantity"])
tablename= 'inventory'

with engine.connect() as conn:
    with conn.begin():
        try:
            df1.to_sql(tablename, conn, index=False, if_exists="append")
            raise Exception("test")
            df2.to_sql(tablename, conn, index=False, if_exists="append")
            conn.commit()
        except Exception:
            conn.rollback()
            print(traceback.format_exc())
    with conn.begin():
        try:
            df2.to_sql(tablename, conn, index=False, if_exists="append")
            df3.to_sql(tablename, conn, index=False, if_exists="append")
            conn.commit()
            print("success")
        except Exception:
            conn.rollback()
            print(traceback.format_exc())
EOF
    cat >$service/assert_reset.sql <<EOF
select * from inventory;
go
delete from inventory where id between 4 and 6;
go
EOF
    for env in 1 2 3 4; do
        docker compose exec -T ${service} bash <<EOF
. env${env}/bin/activate
pip list
python sqlalchemy_test_1.py
/opt/mssql-tools18/bin/sqlcmd -S db -d TestDB -U testuser -P "${SA_PASS}" -C -e <assert_reset.sql
python sqlalchemy_test_2.py
/opt/mssql-tools18/bin/sqlcmd -S db -d TestDB -U testuser -P "${SA_PASS}" -C -e <assert_reset.sql
deactivate
EOF
    done
done 2>&1 | tee setup.log

Answer 2

自己解決できました。
pandas==2.1.4においてcreate_engineは作成出来るが、トランザクション処理ができない、という件についてSQLでトランザクションを明示的に実行すればトランザクション処理できました。
要件としては満たせるので、これで対応したいと思います。

pandas=2.2.2の方でcreate_engineを作成する方法は相変わらず出来なかったので、こちらはもしよろしければ教えて頂けると助かります。

with engine.connect() as conn:
    try:
        conn.execute('BEGIN TRANSACTION')
        df.to_sql(tabel, conn, if_exists="append")
        conn.execute('BEGIN COMMIT')
    except Exception as ex:
        conn.execute('ROLLBACK')
  

Answer 3

ごめんなさい。回答に失敗して2回目です・・

警告は実行時に表示されるものでしょうか？
次のようなコードで試したところ警告もエラーもないようでした。

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("sqlite:////absolute/path/to/foo.db")
df = pd.DataFrame([
    { 'title': 'title1', 'year': 2001, 'score': 11 },
    { 'title': 'title2', 'year': 2002, 'score': 12 },
    { 'title': 'title3', 'year': 2003, 'score': 13 },
    { 'title': 'title4', 'year': 2004, 'score': 14 },
])
tablename = 'movie'
with engine.begin() as conn:
    try:
        df.to_sql(tablename, conn, if_exists='append')
        raise Exception('test exception')
        conn.commit()
        print('commit')
    except Exception as ex:
        conn.rollback()
        print(ex)

to_sqlはデフォルトではテーブルの作り直しみたいですので、drop tableはdml(データ操作)ではなく、ddl(データ定義)なので、完全にはロールバックはできないと思います。
(ロールバックによってdrop,createの結果からテーブルが空っぽの状態になるみたいです。)

接続文字列はデータベースによって異なると思いますので、下の「参考」のリンク先を参考にしてみてください。

警告は、Stack Overflowによると、接続文字列の記述の仕方によるもののように思いましたので、このあたりを見直してみると良いかもしれません。
(ダメそうでしたらデータベースの種類やバージョンなどの環境面の情報も質問欄に追記してみてください)
sql server - Getting a warning when using a pyodbc Connection object with pandas - Stack Overflow

環境

Python 3.12.4
pandas 2.2.2
SQLAlchemy 2.0.31

参考

Engine Configuration — SQLAlchemy 2.0 Documentation
pandas.DataFrame.to_sql — pandas 2.2.2 documentation

Comments

[python_heroku]

回答ありがとうございます。
頂いた参考情報なども見て、create_engineの作り方を修正するなども試してもみたのですが、自己解決できずでした。

pandas==2.1.4ではto_sqlの処理までは完了できますが、やはりconn.commit()やconn.rollbackが同じようにwithwith句のインデント内に含まれる形に修正してもて引き続き、同エラーが発生していました。

環境情報についてですが、pyodbcを利用してMSのSQL Serverに対して接続しようとしている、かつDriverはODBC Driver 17 for SQL Serverを利用していますが、他に必要事項あればご指摘ください。