SQLAlchemy connecting to a remote MySQL server via SSH

Source: Internet
Author: User
Tags connection pooling

First you need a module sshtunnel, if there is no direct pip install Sshtunnel

From Sshtunnel import sshtunnelforwarderfrom sqlalchemy import Column, String, Integer, Create_engine, Eventfrom Sqlalche My.orm Import sessionmakerfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.exc Import Disconnectionerrorssh_host = "" # Fortress machine IP address or hostname ssh_port = 22 # The port number that the bastion machine connects to the MySQL server, which is generally 22, must be the number Ssh_user = "" # This is your username on the fortress machine Ssh_password = "" # This is your user password on the fortress machine Mysql_host = "" # This is your MySQL server hostname or IP address mysql_port = 3306 # This is the port on your MySQL server, 3306,mysql is 3306, must be the number Mysql_user = "" # This is your MySQL database username Mysql_password = "" # This is your MySQL database password mysql_db = "# MySQL server database name base = Dec Larative_base () class phones (base): __tablename__ = ' phones ' id = column (Integer, primary_key=true) name = Column (        String (+)) def checkout_listener (Dbapi_con, Con_record, Con_proxy): try:try:dbapi_con.ping (False) Except TypeError:dbapi_con.ping () except Dbapi_con.        Operationalerror as Exc:if Exc.args[0] In (2006, 2013, 2014, 2045, 2055):    Raise Disconnectionerror () Else:raisewith sshtunnelforwarder (Ssh_host, ssh_port), S    Sh_username=ssh_user, Ssh_password=ssh_password, remote_bind_address= (Mysql_host, Mysql_port)) as server: Server.start () # SSH Channel service start local_port = str (server.local_bind_port) engine = Create_engine (' Mysql+pymysql: {}:{}@{}:{}/{}?charset=utf8 '. Format (Mysql_user, Mysql_password, ' 127.0.0.1 ', local_port,mysql_db), pool_size=100 , pool_recycle=3600) Event.listen (engine, ' checkout ', Checkout_listener) # Prevents errors associated with connection pooling Base.metadata.create_ All (engine) # detects all classes in the file that inherit the base class, builds all the tables in Mysqld, class is table session = Sessionmaker (bind=engine) session = Session ()

SQLAlchemy connecting to a remote MySQL server via SSH

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.