如何从SQL文件中提取表结构信息

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

import re

def extract_table_structure(file_path):
    with open(file_path, 'r') as file:
        sql_content = file.read()

    # 使用正则表达式匹配 CREATE TABLE 语句
    create_table_pattern = r'CREATE TABLE\s+(\w+)\s*\((.*?)\);'
    matches = re.findall(create_table_pattern, sql_content, re.DOTALL)

    table_structures = {}
    for match in matches:
        table_name = match[0]
        columns = match[1].strip().split(',')

        table_structures[table_name] = []
        for column in columns:
            column_info = column.strip().split()
            if len(column_info) > 1:
                column_name = column_info[0]
                data_type = column_info[1]
                constraints = ' '.join(column_info[2:])
                table_structures[table_name].append({
                    'name': column_name,
                    'type': data_type,
                    'constraints': constraints
                })

    return table_structures

# 使用示例
file_path = 'schema.sql'
structures = extract_table_structure(file_path)
for table_name, columns in structures.items():
    print(f"Table: {table_name}")
    for column in columns:
        print(f"  - {column['name']}: {column['type']} {column['constraints']}")