OpenStack 数据库操作 demo

#!/usr/bin/env python

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, Boolean

sql_connection = "mysql+pymysql://nova:533e988a566a25d4@192.168.200.100:3306/nova" 
engine = create_engine(sql_connection, echo=True)
Session = sessionmaker(bind=engine)
session = Session()

BASE = declarative_base() 

class InstanceTypes(BASE):
    __tablename__ = "instance_types" 

    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    memory_mb = Column(Integer)
    vcpus = Column(Integer)
    root_gb = Column(Integer)
    ephemeral_gb = Column(Integer)
    flavorid = Column(String(255))
    swap = Column(Integer, nullable=False, default=0)
    rxtx_factor = Column(Float, nullable=False, default=1)
    vcpu_weight = Column(Integer, nullable=True)
    disabled = Column(Boolean, default=False)
    is_public = Column(Boolean, default=True)

flavors = session.query(InstanceTypes).all()

for flavor in flavors:
    print flavor.id, flavor.name

执行结果：

[root@controller ~]# python sql.py
2018-11-01 22:35:08,943 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2018-11-01 22:35:08,943 INFO sqlalchemy.engine.base.Engine ()
2018-11-01 22:35:08,944 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2018-11-01 22:35:08,944 INFO sqlalchemy.engine.base.Engine ()
2018-11-01 22:35:08,944 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2018-11-01 22:35:08,944 INFO sqlalchemy.engine.base.Engine ()
2018-11-01 22:35:08,946 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2018-11-01 22:35:08,946 INFO sqlalchemy.engine.base.Engine ()
2018-11-01 22:35:08,946 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2018-11-01 22:35:08,947 INFO sqlalchemy.engine.base.Engine ()
2018-11-01 22:35:08,947 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2018-11-01 22:35:08,947 INFO sqlalchemy.engine.base.Engine ()
2018-11-01 22:35:08,948 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-11-01 22:35:08,949 INFO sqlalchemy.engine.base.Engine SELECT instance_types.id AS instance_types_id, instance_types.name AS instance_types_name, instance_types.memory_mb AS instance_types_memory_mb, instance_types.vcpus AS instance_types_vcpus, instance_types.root_gb AS instance_types_root_gb, instance_types.ephemeral_gb AS instance_types_ephemeral_gb, instance_types.flavorid AS instance_types_flavorid, instance_types.swap AS instance_types_swap, instance_types.rxtx_factor AS instance_types_rxtx_factor, instance_types.vcpu_weight AS instance_types_vcpu_weight, instance_types.disabled AS instance_types_disabled, instance_types.is_public AS instance_types_is_public
FROM instance_types
2018-11-01 22:35:08,949 INFO sqlalchemy.engine.base.Engine ()
1 m1.medium
2 m1.tiny
3 m1.large
4 m1.xlarge
5 m1.small
6 4Vcpu_4096Mem_50Disk_disk0type
7 m1.vcomputer

DEMO2：

#!/usr/bin/env python

from nova.db.sqlalchemy.models import Instance
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from oslo_config import cfg
from oslo_db import options as oslo_db_options

CONF = cfg.CONF
CONF.register_opts(oslo_db_options.database_opts, 'database')
CONF(project='nova', default_config_files=['/etc/nova/nova.conf'])

def get_session():
    sql_connection = CONF.database.connection
    engine = create_engine(sql_connection, echo=False)
    session = sessionmaker(bind=engine)

    return session()

session = get_session()
instances = session.query(Instance).all()
for instance in instances:
    print instance.uuid,  instance.vm_state, instance.display_name

运行测试：

[root@controller ~]# python sql.py 
3b2618d6-e939-4646-a5a2-402529c54ffc deleted centos01
aec899d3-71ee-4e1e-b61b-519e0b57b30b deleted centos02
72ba077b-fdc8-422d-a246-62b0fc172170 deleted centos02
c40003b4-5192-4264-8516-2d01374ba910 deleted centos02
1d735ae7-e17d-465d-a223-ba059daff3ee deleted centos02
58fe007f-b4d6-48c9-ae02-513911019654 deleted centos03
88e13b55-5a96-4ea6-927a-5e3b04c9d5d9 deleted centos02
b1454d87-3645-4499-92ec-55cda2231cf5 deleted centos01
33b50afe-dbea-4191-a3b0-27712d903639 deleted centos04
a18d964d-86a0-4e68-92ee-83ce2fb2134e deleted centos05
ca44a126-bf6c-4a2e-a929-6a8c98e0dbfc deleted centos06
3bdc3bef-d8a3-4cea-bc3f-d622fcd4fe14 active vm1
eb6f2354-a473-4615-b424-fe24768dcee5 active vm2
fb307b80-006a-488c-b2e0-c63080bd68ab active vm3
35ac1093-b518-4cf2-a709-bc5e359bd90b active vm4
98786896-86f6-4eda-b584-de9e13617017 active vm5
47575707-ca87-4eea-b94c-80bc8d1fb91b active vm6
6af8a528-33ac-435d-a059-26b68aff2c95 deleted vm7
b1ae14b9-6e7c-43a7-b1cc-bc9c2573a005 deleted vm8
db705de1-4d3d-47d5-8634-a45f95bec82b deleted vmaa
3e62700b-70d4-4b66-b086-407cc01b816b deleted vmbb