sqlite3

安装：

SQLite 安装 | 菜鸟教程

格式化输出

.header on

.mode column --按列输出

.timer on

.mode line --按行输出

CRUD

insert into 语句改成 replace into 语句，就可以实现防重复插入。

子查询

SQLite 子查询 | 菜鸟教程

C/C++连接接口

SQLite – C/C++ | 菜鸟教程

C++操作SQLITE获得查询结果集的几种方法总结_firejq 的博客-CSDN博客_c++ sqlite3查询

python连接接口

SQLite – Python | 菜鸟教程

C++demo

python demo

import sqlite3
import numpy as np


# 建表=================================================================
"""
.open C:\\Users\\Desktop\\sqlite3项目\\Database.db

select * from CoordinateSystem;
.tables
drop table CoordinateSystem;

"""

class RobotDatabase:
    def __init__(self,DBFilePath):
        self.conn = sqlite3.connect(DBFilePath)
        print("打开数据库文件：", DBFilePath)
        self.c = self.conn.cursor()



    # 初始化数据库，创建各个表
    def init_Database(self):
        self.init_CoordinateSystem_Table()    #创建坐标系表
        self.conn.commit()
        print("数据库初始化完成")

    # 获取数据库中所有的表名
    def getTablesName(self):
        name = self.c.execute("select name from sqlite_master where type='table';  ").fetchall()
        print("数据库中包含以下表：")
        for name_ in name:
            print(name_[0])
        return name

    # 获取某个表中所有的记录名
    def getDataName(self,TableName):
        str = "select *  from " + TableName;
        name = self.c.execute(str).fetchall()
        print(TableName,"表中包含以下记录：")
        for name_ in name:
            print(name_[0])
        return name


    # 初始化坐标系表——CoordinateSystem
    def init_CoordinateSystem_Table(self):
        self.c.execute('''CREATE TABLE IF NOT EXISTS CoordinateSystem  
                          (NAME TEXT  UNIQUE  NOT NULL,

                           T00 DOUBLE NOT NULL,
                           T01 DOUBLE NOT NULL,
                           T02 DOUBLE NOT NULL,
                           T03 DOUBLE NOT NULL,

                           T10 DOUBLE NOT NULL,
                           T11 DOUBLE NOT NULL,
                           T12 DOUBLE NOT NULL,
                           T13 DOUBLE NOT NULL,

                           T20 DOUBLE NOT NULL,
                           T21 DOUBLE NOT NULL,
                           T22 DOUBLE NOT NULL,
                           T23 DOUBLE NOT NULL,

                           T30 DOUBLE NOT NULL,
                           T31 DOUBLE NOT NULL,
                           T32 DOUBLE NOT NULL,
                           T33 DOUBLE NOT NULL
                    );''')
        #self.insert_Coordinate_System("wobj0",np.zeros((4,4)))
        #self.insert_Coordinate_System("TCP", np.zeros((4, 4)))
        #self.insert_Coordinate_System("1", np.zeros((4, 4)))
        print("CoordinateSystem表创建完成")
        self.conn.commit()


    # 修改坐标系表某一条
    def insertData_CoordinateSystem(self,name,T):
        #修改TCP。先判断是否存在
        self.c.execute('''
                    replace INTO CoordinateSystem (NAME,T00,T01,T02,T03,  T10,T11,T12,T13  ,T20,T21,T22,T23  ,T30,T31,T32,T33  ) 
                    VALUES ( ?, ?,?,?,?,  ?,?,?,?, ?,?,?,?,  ?,?,?,? );''',
                    [name,  T[0, 0], T[0, 1], T[0, 2], T[0, 3],
                            T[1, 0], T[1, 1], T[1, 2], T[1, 3],
                            T[2, 0], T[2, 1], T[2, 2], T[2, 3],
                            T[3, 0], T[3, 1], T[3, 2], T[3, 3]  ]
                    )
        self.conn.commit()

    # 获取坐标系表某一条数据
    def getData_CoordinateSystem(self,name):
        cursor = self.c.execute(
            "SELECT T00,T01,T02,T03,  T10,T11,T12,T13  ,T20,T21,T22,T23  ,T30,T31,T32,T33 from CoordinateSystem where name = ?;",[name])
        T = np.array(cursor.fetchall()).reshape((4,4))
        print("从CoordinateSystem表中查到",name,"中的T为：\n", T)


if __name__ == '__main__':
    DBFilePath = "C:\\Users\\Desktop\\sqlite3项目\\Database.db"
    DB = RobotDatabase(DBFilePath)
    DB.init_Database()
    DB.insertData_CoordinateSystem("wobj0", np.zeros((4, 4)))
    DB.insertData_CoordinateSystem("TCP", np.ones((4, 4)))
    DB.getData_CoordinateSystem("TCP")
    DB.getTablesName()
    DB.getDataName("CoordinateSystem")