Python使用SQLAlchemy访问MemFireDB
MemfireDB兼容Postgres的SQL接口协议,因此底层驱动使用Postgres的驱动,下面的代码演示了如何使用SQLAlchemy以SSL加密方式访问MemfireDB,需要注意的是证书文件如果不在当前目录,则根据实际情况进行适配修改,这段代码同样适合连接Postgres数据库:
# -*- coding: utf-8 -*-
import sys
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import sqlalchemy.engine.url as url
Base = declarative_base()
class Counter(Base):
__tablename__ = 'counters'
id = Column(Integer, primary_key=True)
counter = Column(Integer)
engine = None
def init_db_engine(host, port, dbname, user, ssl_ca, ssl_cert, ssl_key):
global engine
uri = url.URL(
drivername="postgresql",
host=host,
port=port,
username=user,
database=dbname,
)
ssl_args = {
"sslmode": "verify-ca",
"sslrootcert": ssl_ca,
"sslcert": ssl_cert,
"sslkey": ssl_key
}
print ssl_args
engine = create_engine(uri, connect_args=ssl_args, encoding='utf-8', echo=False,
pool_size=100, pool_recycle=3600, pool_pre_ping=True)
def get_session():
global engine
SessionCls = sessionmaker(bind=engine)
return SessionCls()
def main():
host = sys.argv[1]
port = int(sys.argv[2])
dbname = sys.argv[3]
dbuser = sys.argv[4]
init_db_engine(host, port, dbname, dbuser, './root.crt', './memfiredb.crt', './memfiredb.key')
Base.metadata.create_all(engine)
session = get_session()
session.execute('delete from counters')
cnt = Counter(counter=1)
session.add(cnt)
session.commit()
session.close()
if __name__ == '__main__':
main()
上面的代码稍作修改就可以直接用来访问Memfire Cloud上创建的云数据库。
相关文章