schema

This is the core of sqlite4dummy.

Important class quick link:

Chinese Doc (中文文档)

本模块是sqlite4dummy的核心模块, 定义了面向对象的数据库中重要概念的抽象类。

重要的类的API的快速链接:

class, method, func, exception

class sqlite4dummy.schema.Insert(table)[source]

An Insert statement objective oriented constructor.

Insert object are constructed from a table. You can call Table.insert() to create one for this Table.

Then you can use insert_record and other method to perform record/Row insert, bulk insert, smart insert and update (insdate).

For usage example, go unittest page and read the testcase source code.

中文文档

Insert语句的面向对象形式的定义类。

sql_from_record()[source]

Generate the ‘INSERT INTO table...’ sqlite command for recrod insertion.

Example:

INSERT INTO table_name VALUES (?,?,...,?);

中文文档

生成INSERT INTO table ... Sqlite语句。

sql_from_row(row)[source]

Generate the ‘INSERT INTO table...’ sqlite command for row insertion.

Example:

INSERT INTO table_name 
    (column1, column2, ..., columnN) 
VALUES 
    (?,?,...,?);

中文文档

生成INSERT INTO table ... Sqlite语句。

class sqlite4dummy.schema.Select(args)[source]

A Select statement objective oriented constructor.

To create a Select object, you have to name a list of Column object has to select. And use where(), limit(), offset(), distinct(), order_by() method to specify your selection.

For usage example, go unittest page and read the testcase source code.

中文文档

Select语句的面向对象形式的定义类。 Sqlite3Engine 在执行Select对象 时会将会调用 sql 其转化为SQL, 然后执行。

where(*args)[source]

where() method is used to filter records. It takes arbitrary many comparison of column and value. SQL_Param object is created for each comparison. And finally we produce the WHERE clause SQL.

Example:

>>> from sqlite4dummy
>>> s = S
where(column1 >= 3.14, column2.between(1, 100), column3.like("%pattern%"))

Supported operation:

>, >=, <, <=, ==, !=

Supported criterion function:

order_by(*argv)[source]

Sort the result-set by one or more columns. you can custom the priority of the orders and choose ascending or descending.

You can define it by three ways.

Method1:

>>> s = Select(table.all).order_by("column_name1", "column_name2")

Method2:

>>> s = Select(table.all).order_by(asc(table.c.column_name1),
                                   desc(table.c.column_nam2),)

Sqlite support asc(), desc().

limit(howmany)[source]

LIMIT clause.

offset(howmany)[source]

OFFSET clause.

distinct()[source]

DISTINCT clause.

select_from(select_obj)[source]

SELECT FROM clause.

sql

Return SELECT SQL.

class sqlite4dummy.schema.Update(table)[source]

A Update statement objective oriented constructor.

An update construct with

Two major part of UPDATE statement is set values and where. We provide two methods Update.values() and Update.where() for this purpose.

For example:

>>> metadata = MetaData()
>>> table = Table("test", metadata, 
...     Column("_id", dtype.INTEGER),
...     Column("_value", dtype.REAL),
...     )
>>> upd = Update(table).values(_value=3.14).where(table.c._id==1)
>>> upd.sql
UPDATE    test
SET    _value = 3.14
WHERE    test._id = 1

For usage example, go unittest page and read the testcase source code.

中文文档

Update语句的面向对象形式的定义类。

values(**kwarg)[source]

Construct set values clause for an UPDATE.

  1. absolute update: column_name = value
  2. relative update: column_name1 = column_name2 #operator value
  3. relative update: column_name1 = column_name2 #operator column_name3

Example:

Update(table).values(column1=value1, column2=value2)

中文文档

构造UPDATE语句中SET value的SQL语句部分。通常有三类设定更新值的方式:

  1. 绝对更新: 列 = 具体值
  2. 相对更新: 列 = 列 #操作符 具体值
  3. 相对更新: 列 = 列 #操作符 列
where(*argv)[source]

Define WHERE clause in UPDATE SQL command

sql

Return UPDATE SQL.

class sqlite4dummy.schema.Delete(table)[source]

A Delete statement objective oriented constructor.

The Delete.where() method specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

For usage example, go unittest page and read the testcase source code.

中文文档

Delete语句的面向对象形式的定义类。

where(*argv)[source]

where() method is used to filter records. It takes arbitrary many comparison of column and value. SQL_Param object is created for each comparison. And finally we produce the WHERE clause SQL.

Example:

>>> from sqlite4dummy
>>> s = S
where(column1 >= 3.14, column2.between(1, 100), column3.like("%pattern%"))
class sqlite4dummy.schema.Column(column_name, data_type, nullable=True, default=None, primary_key=False, skip_validate=False)[source]

Represent a Column in a Table.

Construct a Column object:

>>> from sqlite4dummy import *
>>> c = Column("employee_id", dtype.TEXT, primary_key=True)
>>> c
Column('employee_id', dtype.TEXT, nullable=True, default=None, primary_key=True)
Parameters:
  • column_name (string) – the column name, alpha, digit and understore only. Can’t start with digit.
  • data_type – Data type object.
  • nullable (boolean) – (default True) whether it is allow None value.
  • default (any Python types) – (default None) default value.
  • primary_key (boolean) – (default False) whether it is a primary_key.

For usage example, go unittest page and read the testcase source code.

bind_table(table)[source]

Bind a Column to a Table. So we can visit Column.table_name and Column.full_name afterwards.

中文文档

将Column与Table绑定, 生成两个新的属性: Column.table_name, 和 Column.full_name。这样在SQL语句中可以选择调用column_name或是full_name。

asc()[source]

Construct an Sql parameter in ORDER BY clause or CREATE INDEX clause.

desc()[source]

Construct an Sql parameter in ORDER BY clause or CREATE INDEX clause.

between(lowerbound, upperbound)[source]

WHERE ... BETWEEN ... AND ... clause.

like(wildcards)[source]

WHERE ... LIKE ... clause.

in_(choice)[source]

WHERE ... IN ... clause.

class sqlite4dummy.schema.CreateTable(table)[source]

Generate ‘CREATE TABLE’ SQL statement.

Example:

CREATE TABLE table_name
(
    column_name1 dtype1 CONSTRAINS,
    column_name2 dtype2 CONSTRAINS,
    PRIMARY KEY (column, ...),
    FOREIGN KEY (table_column, ...)
)

中文文档

创建Table的抽象类, 用于根据Schema生成CREATE TABLE ...的SQL语句。目前不支持 FOREIGN KEY语法。

class sqlite4dummy.schema.Table(table_name, metadata, *args)[source]

Represent a table in a database.

Define a Table:

>>> from sqlite4dummy import *
>>> metadata = MetaData() 
>>> mytable = Table("mytable", metadata,
        Column("mytable_id", dtype.INTEGER, primary_key=True),
        Column("value", dtype.TEXT),
        )

columns can be accessed by table.c.column_name:

>>> mytable.c.mytable_id # return a Column object
_id
Parameters:
  • table_name (string) – the table name, alpha, digit and understore only. Can’t start with digit.
  • metadata (MetaData) – Data type object.
  • args (Column) – list of Column object

For usage example, go unittest page and read the testcase source code.

中文文档

sqlite4dummy.schema.Table 是抽象数据表对象类。

定义Table的方法如下:

>>> from sqlite4dummy import *
>>> metadata = MetaData() # 定义metadata 
>>> mytable = Table("mytable", metadata, # 定义表名, metadata和列
        Column("mytable_id", dtype.INTEGER, primary_key=True),
        Column("value", dtype.TEXT),
        )

从Table中获得Column对象有如下两种方法:

>>> mytable.c._id
_id

>>> mytable.get_column("_id")
_id
get_column(column_name)[source]

Get a column by column name.

中文文档

根据列名称获取Column对象。

create(engine)[source]

Create this table in the database binded to the Sqlite3Engine.

drop_table_sql

The SQL for deleting this Table.

drop(engine)[source]

Drop this table in the database binded to the Sqlite3Engine.

insert()[source]

Construct an Insert object.

update()[source]

Construct an Update object.

delete()[source]

Construct a Delete object.

class sqlite4dummy.schema.Index(index_name, metadata, args, table_name=None, unique=False, skip_validate=False)[source]

Represent a index of a Table.

Parameters:
  • index_name (string) – the table name, alpha, digit and understore only. Can’t start with digit.
  • metadata (MetaData) – Data type object.
  • args (Column or SQL_Param) – index configuration
  • table_name (string) – (default None) create index on which table name.
  • unique (boolean) – (default False) Whether it’s an unique index.

For usage example, go unittest page and read the testcase source code.

中文文档

Sqlite的CREATE INDEX语句中, 需要指定ON table_name, 而column_name不允许 table_name.column_name的形式, 只允许原生的column_name。

Index对象可以由, Column对象, 代表column name的字符串, 或是由Column.asc() 或desc(Column)所生成的_SQL_PARAM对象初始化。其中Column, _SQL_PARAM对象中 是包含了所属的table对象的信息的。当Index只由字符串生成时, 则要额外指定可 选参数``table_name``。在其他时候, 我们并不需要显式地指定表名。

create_index_sql

The Sql for creating this Index.

create(engine)[source]

Create this Index in the database binded to the Sqlite3Engine.

drop_index_sql

The Sql for deleting this Index.

drop(engine)[source]

Drop this Index in the database binded to the Sqlite3Engine.

exception sqlite4dummy.schema.DuplicateTableError[source]

Raises when duplicated table name been added into metadata.

exception sqlite4dummy.schema.DuplicateIndexError[source]

Raises when duplicated index name been added into metadata.

class sqlite4dummy.schema.MetaData(bind=None)[source]

A schema information container holds all Table objects in a database and their columns’ schema definition and constructs.

MetaData are also able to bind to an Sqlite3Engine object. If bound, more Table related SQL execution can be easily generated.

Access all table:

MetaData.tables

Access table by table name:

MetaData.get_table(table_name)

Create all table:

MetaData.create_all(engine)
get_table(table_name)[source]

Access Table by table name.

get_index(index_name)[source]

Access Index by index name.

create_all(engine)[source]

Create all table in metadata in database engine. Also bind itself to this engine.

drop_all(engine)[source]

Drop all table in metadata in database engine. Also bind itself to this engine.

create_all_index(engine)[source]

Create all index in metadata in database engine. Also bind itself to this engine.

drop_all_index(engine)[source]

Drop all index in metadata in database engine. Also bind itself to this engine.

reflect(engine, pickletype_columns=[])[source]

Read table, column, index metadata from database schema information.

Parameters:
  • engine – Bind to Sqlite3Engine
  • pickletype_columns – pickletype columns’ full name list. e.g. [table_name1.column_name1, table_name2.column_name2, ...]