投稿2021/05/17 07:20
編集2021/05/20 16:24現在sqlalchemyでmodelを何個か定義してテーブルを作成しようと思っています。
作成の際に参考にしたのはfastAPIのチュートリアルのページです。
外部サーバ上のmysqlデータベースを使用しています。
MySQL
├── database.py ├── main.py └── models.py
from sshtunnel import SSHTunnelForwarder from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker, scoped_session with SSHTunnelForwarder( ("111.111.111.111", 1111), ssh_host_key=None, ssh_username="testuser", ssh_password="testpassword", remote_bind_address=("127.0.0.1", 3306), ) as server: DATABASE_URL = f'mysql://{"test"}:{"password"}@localhost:{server.local_bind_port}/{"testdb"}?charset=utf8' ENGINE = create_engine(DATABASE_URL, encoding="utf-8", echo=True) # Sessionの作成 session = scoped_session( sessionmaker(autocommit=False, autoflush=False, bind=ENGINE) )
from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import as_declarative, declarative_base, declared_attr from database import session @as_declarative() class Model(object): @declared_attr def __tablename__(cls): return cls.__name__.lower() id = Column(Integer, primary_key=True, index=True) created_at = Column(DateTime, server_default=text("NOW()"), nullable=False) updated_at = Column(DateTime, server_default=text("NOW()"), nullable=False) Base = declarative_base(cls=Model) Base.query = session.query_property() class User(Base): __tablename__ = "users" username = Column(String, unique=True, index=True) password = Column(String) is_active = Column(Boolean) # Relationship exam_result = relationship("ExamResult", back_populates="users")
from models import Base from database import session, ENGINE # 全テーブル作成 Base.metadata.create_all(bind=ENGINE)
上記の3ファイルにて、main.pyを実行して models.py で定義したテーブルを一括作成しようとしているのですが、
下記のようなエラーが発生しています。
Traceback (most recent call last): File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3141, in _wrap_pool_connect return fn() File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 301, in connect return _ConnectionFairy._checkout(self) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 755, in _checkout fairy = _ConnectionRecord.checkout(pool) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 419, in checkout rec = pool._do_get() File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get self._dec_overflow() File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__ compat.raise_( File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_ raise exception File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 142, in _do_get return self._create_connection() File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 247, in _create_connection return _ConnectionRecord(self) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 362, in __init__ self.__connect(first_connect_check=True) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 605, in __connect pool.logger.debug("Error on connect(): %s", e) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__ compat.raise_( File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_ raise exception File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 599, in __connect connection = pool._invoke_creator(self) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 578, in connect return dialect.connect(*cargs, **cparams) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 559, in connect return self.dbapi.connect(*cargs, **cparams) File "/home/kson/.local/lib/python3.8/site-packages/MySQLdb/__init__.py", line 130, in Connect return Connection(*args, **kwargs) File "/home/kson/.local/lib/python3.8/site-packages/MySQLdb/connections.py", line 185, in __init__ super().__init__(*args, **kwargs2) MySQLdb._exceptions.OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)") The above exception was the direct cause of the following exception: Traceback (most recent call last): File "main.py", line 11, in <module> Base.metadata.create_all(bind=ENGINE) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4744, in create_all bind._run_ddl_visitor( File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3007, in _run_ddl_visitor with self.begin() as conn: File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2923, in begin conn = self.connect(close_with_result=close_with_result) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3095, in connect return self._connection_cls(self, close_with_result=close_with_result) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 91, in __init__ else engine.raw_connection() File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3174, in raw_connection return self._wrap_pool_connect(self.pool.connect, _connection) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3144, in _wrap_pool_connect Connection._handle_dbapi_exception_noconnection( File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2003, in _handle_dbapi_exception_noconnection util.raise_( File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_ raise exception File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3141, in _wrap_pool_connect return fn() File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 301, in connect return _ConnectionFairy._checkout(self) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 755, in _checkout fairy = _ConnectionRecord.checkout(pool) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 419, in checkout rec = pool._do_get() File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get self._dec_overflow() File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__ compat.raise_( File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_ raise exception File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 142, in _do_get return self._create_connection() File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 247, in _create_connection return _ConnectionRecord(self) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 362, in __init__ self.__connect(first_connect_check=True) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 605, in __connect pool.logger.debug("Error on connect(): %s", e) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__ compat.raise_( File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_ raise exception File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 599, in __connect connection = pool._invoke_creator(self) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 578, in connect return dialect.connect(*cargs, **cparams) File "/home/kson/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 559, in connect return self.dbapi.connect(*cargs, **cparams) File "/home/kson/.local/lib/python3.8/site-packages/MySQLdb/__init__.py", line 130, in Connect return Connection(*args, **kwargs) File "/home/kson/.local/lib/python3.8/site-packages/MySQLdb/connections.py", line 185, in __init__ super().__init__(*args, **kwargs2) sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)") (Background on this error at: http://sqlalche.me/e/14/e3q8)
(2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
が悪さしていると思われます。var/run/mysqld/mysqld.sock を確認したらファイルが存在しなかったため新たに作成してもう一回実行してみましたが、うまく行きませんでした。
なにか考えられる原因がわかる方がいればご教授お願いします。よろしくお願いいたします。
SSHTunnelForwarderを実行しできており、server変数はきちんと取得されています。
回答1件
0
自己解決
database.pyの、SSHTunnelForwarderをwithでなくserverという変数に入れてやってみたら作成できました。
何度も対応してくださったKUROROさん、アドバイスをくださったbsdfanさんありがとうございました！！
from sshtunnel import SSHTunnelForwarder from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker, scoped_session server = SSHTunnelForwarder( ("111.111.111.111", 1111), ssh_host_key=None, ssh_username="testuser", ssh_password="testpassword", remote_bind_address=("127.0.0.1", 3306), ) DATABASE_URL = f'mysql://{"test"}:{"password"}@localhost:{server.local_bind_port}/{"testdb"}?charset=utf8' ENGINE = create_engine(DATABASE_URL, encoding="utf-8", echo=True) session = scoped_session( sessionmaker(autocommit=False, autoflush=False, bind=ENGINE) )
投稿2021/05/21 09:40
総合スコア18
あなたの回答
tips
プレビュー
Q&A
