CasNote (5)Python连接Cassandra

安装包

pip install cassandra-driver

使用

最简单的连接

from cassandra.cluster import Cluster

cluster = Cluster(['192.168.92.159', '192.168.92.160','192.168.92.161'])

session = cluster.connect('ycsb')

print(session)

查询所有命名空间

from cassandra.cluster import Cluster

# 连接到Cassandra数据库
cluster = Cluster(['192.168.92.159', '192.168.92.160','192.168.92.161'])
session = cluster.connect()


# 查询所有的keyspace
keyspaces = session.execute("SELECT keyspace_name FROM system_schema.keyspaces;")

# 打印keyspace列表
for keyspace in keyspaces:
    print(keyspace.keyspace_name)

查询命名空间是否存在，没有则创建

from cassandra.cluster import Cluster

# 连接到Cassandra数据库
cluster = Cluster(['192.168.92.159', '192.168.92.160','192.168.92.161'])
session = cluster.connect()


# 查询keyspace是否存在
keyspace_name = 'seimic_keyspace'
keyspace_query = f"SELECT keyspace_name FROM system_schema.keyspaces WHERE keyspace_name = '{keyspace_name}';"
keyspace_exists = bool(session.execute(keyspace_query).one())

if not keyspace_exists:
    # 创建keyspace
    create_keyspace_query = f"CREATE KEYSPACE {keyspace_name} WITH replication = {{'class': 'SimpleStrategy', 'replication_factor': 1}};"
    session.execute(create_keyspace_query)

# 使用新的或现有的keyspace
session.set_keyspace(keyspace_name)

创建表

from cassandra.cluster import Cluster
def create_table(keyspace_name, table_name):
    # 连接到Cassandra数据库
    cluster = Cluster(['192.168.92.159', '192.168.92.160','192.168.92.161'])
    session = cluster.connect(keyspace_name)

    # 定义创建表的CQL命令
    create_table_query = f"""
        CREATE TABLE IF NOT EXISTS {table_name} (
            id UUID PRIMARY KEY,
            name text,
            age int
        );
    """
    
    # 执行创建表命令
    session.execute(create_table_query)

    # 关闭连接
    cluster.shutdown()

# 调用函数，传入keyspace名称和要创建的表名称
keyspace_name = 'seimic_keyspace'
table_name = 'project'
create_table(keyspace_name, table_name)

查询表

def query_tables_in_keyspace(keyspace_name):
    # 连接到Cassandra数据库
    cluster = Cluster(['192.168.92.159', '192.168.92.160','192.168.92.161'])
    session = cluster.connect(keyspace_name)

    # 查询当前keyspace中的所有表
    tables_query = "SELECT table_name FROM system_schema.tables WHERE keyspace_name = %s;"
    result = session.execute(tables_query, [keyspace_name])

    # 打印表名
    for row in result:
        print(row.table_name)
    
    # 关闭连接
    cluster.shutdown()

# 调用函数，传入keyspace名称
keyspace_name = 'seimic_keyspace'
query_tables_in_keyspace(keyspace_name)

查询表里的字段

from cassandra.cluster import Cluster

cassandra_host =['192.168.92.159', '192.168.92.160','192.168.92.161']

# 连接到Cassandra集群
cluster = Cluster(cassandra_host)  # 替换为实际的Cassandra主机地址
session = cluster.connect()

# 指定要查询的keyspace和table名称
keyspace_name = 'seismic_keyspace'
table_name = 'project_chunks_table'

# 切换到指定keyspace
session.set_keyspace(keyspace_name)

# 查询表的元数据
query = f"SELECT column_name, type FROM system_schema.columns WHERE keyspace_name = '{keyspace_name}' AND table_name = '{table_name}'"
result = session.execute(query)

# 输出字段信息
for row in result:
    print(f"Column Name: {row.column_name}, Type: {row.type}")

# 关闭连接
session.shutdown()
cluster.shutdown()

删除表

from cassandra.cluster import Cluster
your_cassandra_host = ['192.168.92.159', '192.168.92.160','192.168.92.161']
# 连接到Cassandra集群
cluster = Cluster(your_cassandra_host)  # 替换为实际的Cassandra主机地址
session = cluster.connect()

# 指定要删除的keyspace和table名称
keyspace_name = 'seismic_keyspace'
table_name = 'project_chunks_table'

# 构建删除表的CQL查询语句
drop_table_query = f"DROP TABLE IF EXISTS {keyspace_name}.{table_name}"

# 执行删除表操作
session.execute(drop_table_query)

# 关闭连接
session.shutdown()
cluster.shutdown()