Working with tables
TiDB uses tables to organize and store collections of related data. It provides flexible schema definition capabilities, allowing you to structure your tables according to your specific requirements.
A table can contain multiple columns with different data types to store various kinds of data. Supported data types include text, numbers, vectors, binary data (BLOB
), JSON, and more.
Tip
For a complete working example, see the basic example in our repository.
Create a table
Using TableModel
TiDB provides a TableModel
class that represents the schema of a table. This class is compatible with the Pydantic Model and allows you to define the table structure in a declarative way.
In the following example, you create a table named items
with these columns:
id
: a primary key column with an integer typecontent
: a text type columnembedding
: a vector type column with 3 dimensionsmeta
: a JSON type column
After you connect to the database using PyTiDB and obtain a client
instance, you can create a table with the create_table
method.
from pytidb.schema import TableModel, Field, VectorField
from pytidb.datatype import TEXT, JSON
class Item(TableModel):
__tablename__ = "items"
id: int = Field(primary_key=True)
content: str = Field(sa_type=TEXT)
embedding: list[float] = VectorField(dimensions=3)
meta: dict = Field(sa_type=JSON, default_factory=dict)
table = client.create_table(schema=Item, mode="overwrite")
The create_table
method accepts these parameters:
schema
: TheTableModel
class that defines your table structure.mode
: The creation mode of the table.create
(default): Creates the table if it does not exist; raises an error if it already exists.exists_ok
: Creates the table if it does not exist; does nothing if it already exists.overwrite
: Drops the existing table and creates a new one. This is useful for testing and development, but not recommended for production environments.
Once the table is created, you can use the table
object to insert, update, delete, and query data.
Add data to a table
With TableModel
You can use a TableModel
instance to represent a record and insert it into the table.
To insert a single record:
Use the table.insert()
method to insert a single record into the table.
To insert multiple records:
Use the table.bulk_insert()
method to insert multiple records into the table.
Use the INSERT INTO
statement to insert multiple records into the table.
Query data from a table
To fetch records from a table:
To fetch records based on query conditions:
Pass the filters
parameter to the table.query()
method.
For a complete list of supported filter operations and examples, refer to the filtering guide.
Update data in a table
Delete from a table
Truncate a table
To remove all data from the table but keep the table structure, use the table.truncate()
method.
To check that the table is truncated, verify that it contains 0 rows.
Drop a table
To permanently remove a table from the database, use the client.drop_table()
method.
To check that the table is removed from the database: