对象数据库的调研与使用

对象数据库

一、定义：对象数据库（Object-Oriented Database，简称OODB）是一种以对象的形式存储和管理数据的数据库系统。

面向对象数据库系统的开发基本上是采取两种策略，一种是在现有关系数据库中加入许多面向对象数据库的功能，另一种方法是采用面向对象的概念开发新一代的面向对象数据库系统。

二、常见的对象数据库：

1.db4o

a.查询速度快，db4o直接存储和检索对象

b.纯Java编写的数据库，支持java、.net，具有良好的跨平台性

c.可以随时添加、删除或修改对象的字段

d.轻量级对象数据库

2.ZODB

a.直接存储Python对象

b.支持小、中规模数据存储

c.使用简单

3.ObjectDB

a.支持客户端-服务器模式和嵌入式模式

b.适用于处理各种规模的数据，从较小的数据库到非常大的数据库，支持数据库文件大小可达到128TB

c.ObjectDB 提供了高级查询和索引功能

d.纯Java数据库，使用对象图来存储和查询数据库

4.Objectivity/DB

a.适用于需要处理大量复杂数据

b.支持python、Java、c++等多种语言

c.使用量最多

5.Versant Object Database(V/OD)

a.高性能、可扩展，适用于需要处理大量复杂数据

b.支持C++、Java和.NET三种语言

c.对象模型直接映射到数据库模式，无需额外的映射层

d.支持模式的动态更新、通过在线数据库管理实现高可用性

e.比较复杂

6.GemStone/S

a.使用 Smalltalk开发（语言有点冷门）

b.用于开发、部署和管理可扩展、高性能、多层的应用程序

三、关系数据库转化为对象数据库

1.ORM框架：支持Java（Hibernate）、python（SQLAlchemy、Django）、.NET（Entity Framework）。

2.把mysql数据库的表都以csv格式导出，根据表和表的关系半手动写入对象数据库。

四、使用SQLAlchemy尝试一下把mysql数据库转化为对象数据库zodb

1.在本地连接到已有的mysql数据库，执行一个简单的查询

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy.sql import text

# 创建连接到 MySQL 的引擎
#注意localhost后面的值建议自己查看一下
connection_string = 'mysql+pymysql://root@localhost:0000/yourdatabase'
engine = create_engine(connection_string)

# 执行一个简单的查询
with Session(engine) as session:
    result = session.execute(text("SELECT * FROM students"))
    for row in result:
        print(row)

# 关闭引擎连接
engine.dispose()

示例mysql数据库有三张表students，courses，enrollments

2.创建一个简单的zodb数据库

import ZODB
import transaction
from prettytable import PrettyTable

# 创建一个数据库对象
db = ZODB.DB('mydb.fs')

# 获取数据库连接并开始事务
connection = db.open()
root = connection.root()

# 定义一个要存储的对象类
class Person:
    def __init__(self, person_id, name, age):
        self.person_id = person_id
        self.name = name
        self.age = age

# 创建一个对象并存储到数据库
person = Person('0', 'Alice', 30)
root['person1'] = person
person2 = Person('1', 'Alice2', 30)
root['person2'] = person2

# 提交事务
transaction.commit()

# 关闭数据库连接
connection.close()
db.close()

3.把mysql数据库转化为对象数据库

import ZODB
import transaction
from persistent import Persistent
from sqlalchemy import (
    Column, Integer, String, ForeignKey, create_engine
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base

# 一、定义关系数据库模型（使用 SQLAlchemy）
Base = declarative_base()

class Student(Base):
    __tablename__ = 'students'
    student_id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    courses = relationship("Course", secondary="enrollments")

class Course(Base):
    __tablename__ = 'courses'
    course_id = Column(Integer, primary_key=True)
    name = Column(String)

class Enrollment(Base):
    __tablename__ = 'enrollments'
    student_id = Column(Integer, ForeignKey('students.student_id'), primary_key=True)
    course_id = Column(Integer, ForeignKey('courses.course_id'), primary_key=True)
    enrollments_id = Column(Integer, primary_key=True)

# 二、连接到 MySQL 数据库并读取数据
engine = create_engine('mysql+pymysql://root@localhost:3308/hospital_cerebraltrauma')
Session = sessionmaker(bind=engine)
session = Session()

students_data = session.query(Student).all()
courses_data = session.query(Course).all()

# 三、定义 ZODB 对象模型
class ZODBStudent(Persistent):
    def __init__(self, student_id, name, age):
        self.student_id = student_id
        self.name = name
        self.age = age
        self.courses = []

class ZODBCourse(Persistent):
    def __init__(self, course_id, name):
        self.course_id = course_id
        self.name = name
        self.students = []

# 四、数据转换和存储到 ZODB
def convert_and_store_data():
    zodb_students = []
    zodb_courses = []
    for student in students_data:
        zodb_student = ZODBStudent(student.student_id, student.name, student.age)
        enrollments = session.query(Enrollment).filter(Enrollment.student_id == student.student_id).all()
        for enrollment in enrollments:
            course = session.query(Course).filter(Course.course_id == enrollment.course_id).first()
            zodb_student.courses.append(course)
        zodb_students.append(zodb_student)

    for course in courses_data:
        zodb_course = ZODBCourse(course.course_id, course.name)
        enrollments = session.query(Enrollment).filter(Enrollment.course_id == course.course_id).all()
        for enrollment in enrollments:
            student = session.query(Student).filter(Student.student_id == enrollment.student_id).first()
            zodb_course.students.append(student)
        zodb_courses.append(zodb_course)

    # 创建一个数据库对象
    db = ZODB.DB('mydb.fs')
    connection = db.open()
    root = connection.root()
    root['students'] = zodb_students
    root['courses'] = zodb_courses

    # 遍历并打印存储的对象
    print("Students:")
    for student in root['students']:
        print(f"Student ID: {student.student_id}, Name: {student.name}, Age: {student.age}")
        print("Courses:")
        for course in student.courses:
            print(f"  - Course ID: {course.course_id}, Name: {course.name}")
        print("--------------------")

    print("Courses:")
    for course in root['courses']:
        print(f"Course ID: {course.course_id}, Name: {course.name}")
        print("Students:")
        for student in course.students:
            print(f"  - Student ID: {student.student_id}, Name: {student.name}")
        print("--------------------")

    transaction.commit()
    connection.close()
    db.close()

convert_and_store_data()

# 五、关闭 SQLAlchemy 连接
session.close()
engine.dispose()

4.输出结果

Students:
Student ID: 1, Name: Mary, Age: 18
Courses:
  - Course ID: 101, Name: Macth
  - Course ID: 102, Name: English
--------------------
Student ID: 2, Name: Lily, Age: 19
Courses:
  - Course ID: 101, Name: Macth
--------------------
Courses:
Course ID: 101, Name: Macth
Students:
  - Student ID: 1, Name: Mary
  - Student ID: 2, Name: Lily
--------------------
Course ID: 102, Name: English
Students:
  - Student ID: 1, Name: Mary
--------------------

五、尝试将csv中的内容写入zodb中

1.创建students.csv、courses.csv、enrollments.csv文件

students.csv
student_id,name,age
1,Alice,20
2,Bob,21


courses.csv
course_id,name
101,English
102,Math


enrollments.csv
student_id,course_id
1,101
1,102
2,102

2.读取csv中的内容，写入zodb（注意：“方法”的实现）

import ZODB
import transaction
import csv
from persistent import Persistent

# 创建一个数据库对象
db = ZODB.DB('mydb.fs')

# 获取数据库连接并开始事务
connection = db.open()
root = connection.root()

# 定义学生类
class Student(Persistent):
    def __init__(self, student_id, name, age):
        self.student_id = student_id
        self.name = name
        self.age = age
        self.courses = []

# 定义课程类
class Course(Persistent):
    def __init__(self, course_id, name):
        self.course_id = course_id
        self.name = name
        self.students = []

# 数据迁移函数
def migrate_data():
    students_data = {}
    courses_data = {}
    # 读取学生 CSV 文件
    with open('students.csv', 'r', newline='') as csvfile:
        reader = csv.reader(csvfile)
        next(reader)  # 跳过标题行
        for row in reader:
            student_id = int(row[0])
            name = row[1]
            age = int(row[2])
            students_data[student_id] = Student(student_id, name, age)

    # 读取课程 CSV 文件
    with open('courses.csv', 'r', newline='') as csvfile:
        reader = csv.reader(csvfile)
        next(reader)  # 跳过标题行
        for row in reader:
            course_id = int(row[0])
            name = row[1]
            courses_data[course_id] = Course(course_id, name)

    # 处理选课关系
    with open('enrollments.csv', 'r', newline='') as csvfile:
        reader = csv.reader(csvfile)
        next(reader)  # 跳过标题行
        for row in reader:
            student_id = int(row[0])
            course_id = int(row[1])
            student = students_data[student_id]
            course = courses_data[course_id]
            student.courses.append(course)
            course.students.append(student)

    # 将对象存储到对象数据库
    root['students'] = list(students_data.values())
    root['courses'] = list(courses_data.values())

migrate_data()

transaction.commit()

# 遍历并打印存储的对象
for student in root['students']:
    print(f"Student ID: {student.student_id}, Name: {student.name}, Age: {student.age}")
    print("Courses:")
    for course in student.courses:
        print(f"  - Course ID: {course.course_id}, Name: {course.name}")

for course in root['courses']:
    print(f"Course ID: {course.course_id}, Name: {course.name}")
    print("Students:")
    for student in course.students:
        print(f"  - Student ID: {student.student_id}, Name: {student.name}")

# 关闭数据库连接
connection.close()
db.close()

3.结果

Student ID: 1, Name: Alice, Age: 20
Courses:
  - Course ID: 101, Name: English
  - Course ID: 102, Name: Math
Student ID: 2, Name: Bob, Age: 21
Courses:
  - Course ID: 102, Name: Math
Course ID: 101, Name: English
Students:
  - Student ID: 1, Name: Alice
Course ID: 102, Name: Math
Students:
  - Student ID: 1, Name: Alice
  - Student ID: 2, Name: Bob