#!/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--a5a2-402529c54ffc deleted centos01
aec899d3-71ee-4e1e-b61b-519e0b57b30b deleted centos02
72ba077b-fdc8-422d-a246-62b0fc172170 deleted centos02
c40003b4----2d01374ba910 deleted centos02
1d735ae7-e17d-465d-a223-ba059daff3ee deleted centos02
58fe007f-b4d6-48c9-ae02- deleted centos03
88e13b55-5a96-4ea6-927a-5e3b04c9d5d9 deleted centos02
b1454d87---92ec-55cda2231cf5 deleted centos01
33b50afe-dbea--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--b424-fe24768dcee5 active vm2
fb307b80-006a-488c-b2e0-c63080bd68ab active vm3
35ac1093-b518-4cf2-a709-bc5e359bd90b active vm4
-86f6-4eda-b584-de9e13617017 active vm5
-ca87-4eea-b94c-80bc8d1fb91b active vm6
6af8a528-33ac-435d-a059-26b68aff2c95 deleted vm7
b1ae14b9-6e7c-43a7-b1cc-bc9c2573a005 deleted vm8
db705de1-4d3d-47d5--a45f95bec82b deleted vmaa
3e62700b-70d4-4b66-b086-407cc01b816b deleted vmbb