关系型数据库代码 ********************************* * sqlalchemy: https://docs.sqlalchemy.org/en/20/index.html * gorm: https://gorm.io/zh_CN/docs/index.html 连接 =============================== .. tabs:: .. tab:: 原生 .. tabs:: .. tab:: mysql .. code-block:: console $ docker exec -it mysql mysql -u root -p .. tab:: pymysql .. literalinclude:: /../../reference/data/connect/main_pymysql.py .. tab:: Orm .. tabs:: .. tab:: sqlalchemy url格式: https://docs.sqlalchemy.org/en/20/core/engines.html#backend-specific-urls .. literalinclude:: /../../dataCode/_sqlalchemy/_connection.py .. code-tab:: go GORM package P2 import ( "fmt" "gorm.io/driver/mysql" "gorm.io/gorm" "gorm.io/gorm/logger" ) var DB *gorm.DB var MysqlLogger logger.Interface func init() { username := "root" password := "root" host := "127.0.0.1" port := 3306 Dbname := "gorm" timeout := "10s" // 第一种配置日志的方式 MysqlLogger = logger.Default.LogMode(logger.Info) dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local&timeout=%s", username, password, host, port, Dbname, timeout) db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{ // 如果没有数据一致性的要求，可以在初始化时禁用, 这样可以获得60%的性能提升 //SkipDefaultTransaction: true, //NamingStrategy: schema.NamingStrategy{ // TablePrefix: "f_", // 表名前缀 // SingularTable: true, // 是否单数表明 // NoLowerCase: false, // 关闭大小写转换 //}, //Logger: MysqlLogger, }) if err != nil { panic("连接数据库失败, error=" + err.Error()) } // 连接成功 DB = db type Result struct { Col int } var result Result DB.Raw("SELECT 1 col").Scan(&result) fmt.Println(result.Col) } .. tab:: sqlx(go-zero) .. literalinclude:: /../../reference/data/connect/sqlx/main.go :language: golang 模型 ================================= 从代码定义模型 ------------------------------------- .. tabs:: .. tab:: sqlalchemy .. tabs:: .. tab:: CORE .. literalinclude:: /../../dataCode/_sqlalchemy/_tables.py .. tab:: ORM .. literalinclude:: /../../dataCode/_sqlalchemy/_models.py .. code-tab:: go GORM /* https://gorm.io/zh_CN/docs/models.html#%E6%A8%A1%E5%9E%8B%E5%AE%9A%E4%B9%89 */ package main import ( "gorm.io/driver/mysql" "gorm.io/gorm" "log" "time" ) // Student 定义模型 type Student struct { ID uint // Standard field for the primary key FirstName string `gorm:"size:128,unique"` LastName string // 一个常规字符串字段 Birthday *time.Time // A pointer to time.Time, can be null } func main() { dsn := "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local" db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) if err != nil { log.Fatal(err) } // 建表 // https://gorm.io/zh_CN/docs/migration.html#AutoMigrate db.AutoMigrate(&Student{}) } .. tab:: mysql .. literalinclude:: /../../dataCode/table/create.sql 从db生成模型代码 -------------------------------------- .. tabs:: .. tab:: sqlalchemy 支持sqlalchemy2.X版本的sqlacodegen3.X仍未正式发布，等发布后再整理 sqlalchemy支持映射表结构到Table类: https://docs.sqlalchemy.org/en/20/core/reflection.html#reflecting-database-objects 使用场景之一是同步多个cmdb资源，每一个资源对应一张info表, 需要更新哪些字段是配置在数据库中的。此时可以自动映射表结构到Table, 然后编写 函数动态写入数据 .. code-block:: python from sqlalchemy import MetaData from sqlalchemy.engine import Engine from sqlalchemy.dialects.mysql import insert def sync_cmdb_task(engine: Engine, table_name, inputlist: list[dict], updatelist: list): """" :param inputlist: 资源数据 :param: updatelist: 有则更新的字段 """ # 自动映射表结构 table = Table(table_name, MetaData(), autoload_with=engine) with engine.connect() as conn: stmt = insert(table).values(inputlist) try: duplicate_key_data = {column: insert_stmt.inserted[column] for column in updatelist} except KeyError as err: raise AssertionError("该字段不存在: " + str(err)) from err duplicate_key_data['is_delete'] = 0 duplicate_key_data['deletetime'] = None stmt = stmt.on_duplicate_key_update(**duplicate_key_data) conn.execute(stmt) conn.commit() .. seealso:: * 知乎: `操作数据库已存在的表(单文件脚本) `_ * bilibili: `操作数据库已存在的表(单文件脚本) `_ .. tab:: gorm 用官方的 `gen `_ 工具 gen的配置文件 .. literalinclude:: /../../dataCode/script/gencode/main.go :language: golang 源文件: https://gitee.com/luzhenxiong/bilibili-orm/blob/master/gorm/examples/script/gencode/main.go .. seealso:: 知乎: `操作数据库已存在的表(单文件脚本) `_ bilibili: `操作数据库已存在的表(单文件脚本) `_ .. tab:: django `inspcetdb `_ .. code-block:: console python manage.py inspectdb table1 table2 > ./application/models/gen.py 这个功能是作为一个快捷方式，而不是作为明确的模型生成。生成之后看下是否需要调整模型代码。 默认情况下, 自动生成的模型的Meta类中的managed为False告诉Django不要管理每张表的创建、修改和删除, 这在单元测试中是一个大坑, 不会自动在测试库中自动建表。 所以一般都要手工将managed这一行代码移除, `将非托管模型变为托管的 `_ .. tab:: mysql ``information_schema.COLUMNS`` 表记录了字段的信息 基本CRUD ================================= .. tabs:: .. tab:: sqlalchemy .. literalinclude:: /../../reference/data/crud/_sqlalchemy.py .. tab:: pymysql .. literalinclude:: /../../reference/data/crud/_pymysql.py .. tab:: pandas .. literalinclude:: /../../reference/data/crud/_pandas.py .. tab:: mysql .. literalinclude:: /../../reference/data/crud/mysql.sql :language: mysql 更新 -------------------------------- .. tabs:: .. tab:: sqlalchemy .. tabs:: .. tab:: CORE .. literalinclude:: /../../dataCode/_sqlalchemy/update.py .. tab:: ORM .. literalinclude:: /../../dataCode/_sqlalchemy/put.py .. tab:: mysql .. code-block:: mysql UPDATE person SET name='Maria' WHERE id=5 删除 ----------------------------------- .. tabs:: .. tab:: sqlalchemy .. literalinclude:: /../../dataCode/_sqlalchemy/delete.py .. tab:: mysql .. code-block:: mysql DELETE FROM person .. include:: insert.rst 高级查询 ================================= 关联查询 ---------------------------- 知识点: * 条件查询 * 关联查询 .. tabs:: .. tab:: sqlalchemy .. tabs:: .. tab:: CORE .. literalinclude:: select/relations/query.py .. tab:: ORM .. literalinclude:: select/relations/get.py .. tab:: gorm .. tabs:: .. tab:: GEN :file:`app/internal/logic/retrievestorylogic.go` .. literalinclude:: select/relations/retrievestorylogic.gen.go :language: go .. tab:: GORM .. literalinclude:: select/relations/retrievestorylogic.gorm.go :language: go 跨库join表 ------------------------------------------ .. tabs:: .. tab:: Gorm 在表名前手工加上库名 .. code-block:: go type User struct { } type (User) TableName() string { return "user.user" } type Discover struct { } func (Discover) TableName() string { return "discover.discover" } .. seealso:: issue: `#2959 `_ 子查询 --------------------------------------- .. tabs:: .. tab:: gorm .. tabs:: .. group-tab:: Gen :file:`tests/gen_test.go` .. literalinclude:: select/subquery/gen_test.go :language: go 自定义复杂查询 -------------------------------------- .. tabs:: .. tab:: gorm .. tabs:: .. group-tab:: Gen .. code-block:: go // TestRawSelect 写raw sql实现复杂的字段查询 // 关联的issue: https://github.com/go-gorm/gen/issues/1240 // // github上已有用户要求Filed支持自定义字段并发起了pr: https://github.com/go-gorm/gen/issues/1212 // Field的文档: https://gorm.io/zh_CN/gen/query.html#%E6%96%B0%E5%BB%BA%E5%AD%97%E6%AE%B5 func (s *AuthorGenSuite) TestRawSelect() { ast := assert.New(s.T()) ctx := context.Background() type Result struct { Id int FirstName string Year int } var result Result table := s.q.Author table.WithContext(ctx).Where(table.ID.Eq(1)).UnderlyingDB(). Select([]string{ table.ID.ColumnName().String(), table.FirstName.ColumnName().String(), fmt.Sprintf("YEAR(CAST(%s as DATE)) as year", table.BirthDate.ColumnName()), }).Debug().Scan(&result) // 执行sql: SELECT `id`,`first_name`,YEAR(CAST(birth_date as DATE)) as year FROM `author` WHERE `author`.`id` = 1 ast.Equal(2024, result.Year) ast.Equal("蝉", result.FirstName) } 函数 ================================ 拼接字符串 -------------------------- .. tabs:: .. tab:: mysql .. literalinclude:: /../../reference/data/concat.sql :language: mysql .. tab:: django .. literalinclude:: /../../reference/data/concat.py 钩子 ================================= .. tabs:: .. tab:: sqlalchemy https://docs.sqlalchemy.org/en/20/core/event.html .. tab:: gorm https://gorm.io/zh_CN/docs/hooks.html