This article introduced a Python ORM library for manipulating TdEngine. This article is intended for developers who need to use the Python language to manipulate a TdEngine database.

The address of the project: https://github.com/machine-w/crown

What is ORM?

Object Relational Mapping (ORM) is a programming technique used to convert data between different type systems in object-oriented programming languages. In effect, it creates a “virtual object database” that can be used in a programming language. Simply put, by establishing relationships between classes and database tables, objects and database data items. This allows you to manipulate the database through the data types of the programming language.

The motivation to develop an ORM library for Tdengine

As a developer using Python as my primary programming language, I often write code that works with a variety of databases. For databases of key-value pair type (e.g. Redis) or document type (e.g. MongoDB), the Python ecosystem provides good third-party connection libraries. For the most commonly used relational data (such as MySQL and PostgreSQL), Python provides solutions to third-party ORM libraries such as SQLAlchemy and PeeWee. As a result, in my daily work, I have very few scenarios where I need to manually concatenate SQL queries (and I have forgotten about this skill).

Recently, the author needed to lead a team to complete a project of intelligent power system. In the process of technology selection, TDEngine, an excellent big data platform for the Internet of Things, was found. After testing and evaluation, we found that no matter from the ultra-high performance and stability, or simple design, open source concept. TDengine is very suitable for the use of the basic platform of intelligent power system. However, in the process of using, we found a more intractable problem. That is: because TdEngine is so new, there is still a little less ecosystem around it than other database platforms that have been in development for many years. In particular, there are no native connectors available for OS X, so you’ll need to take your program to Linux for debugging. This is not an easy thing for spoiled Python programmers to get used to. And given that the other programmers on my team are used to ORM operations, they are not familiar with raw SQL. As a result, I realized that if I were to develop with native connectors, I would encounter a lot of difficulties. Thus began the development of Tdengine’s open source ORM library. On the one hand, it can help the team to complete the system development work more efficiently. On the other hand, it can also help Tdengine better improve the ecological tool chain.

How to install and use it

Introduction to the

  • Python version 3.0 or higher is required
  • Tdengine 2.0.8 version of the test passed
  • Resolved the Mac operating system without native Python connectors
  • This greatly reduces the threshold for Python programmers to use TDEngine technology
  • You can easily convert data to Numpy and Pandas

Since Tdengine does not provide a native client under Mac operating system at present, in order to ensure the compatibility of the library, the underlying RESTful interface of the Crown library is currently used for connection. In a future release, I will provide native connector interfaces under Windows and Linux for configuration.

Project Address:

https://github.com/machine-w/crown

The installation

The crown library, like other Python third party libraries, can be easily installed with the latest version via PIP:

pip install crown

You can also install it through Git.

git clone https://github.com/machine-w/crown.gitcd crowmpython setup.py install

Simple to use

1. Connect to the database

Using Crown to connect to TdEngine, you simply need to provide the address of the Taos RESTful service, the port number, and the name of the database you need to operate on. You can then create a new database object using the TDineDatabase class. Here is the sample code to connect to the database:

From crown import DATABASENAME = 'taos_test # Default port: 6041, user name: root, password: TaosData # If you do not use default parameters, # db = TDineDatabase (databaseName,host= host,port= port,user='yourusername',passwd='yourpassword') # Normally we use the CONNECT method to try to connect to the database, and if the current database does not exist, the database will be automatically built. Db.connect () # db db will automatically retrieve all database information and store it as a dictionary in the database attribute. Print (db. Databases) # You can also use manual database building method to create database. Db.create_database (safe=True) # If safe is True, skip the build instruction. # Optional fields: Configure the database parameters when building the database. Please refer to the TDEngine documentation for the meaning of the fields. Db. create_database(safe=True,keep= 100,comp=0,replica=1,quorum=2,blocks=115) Db. alter_database(keep= 1,replica=1, replica=1, replica=1, replica=1,quorum=1,blocks= 2) # delete database from db. database(safe=True) If the library does not exist, skip the library delete directive.

In theory, using the Crown library to operate TDEngine, all database operations do not need to manually assemble SQL statements, but for more specific application scenarios, the Crown library also provides the ability to execute raw SQL statements.

# Execute SQL statements directly through the RAW_SQL method of the database object. The statement rules are consistent with the requirements of the TDEngine RESTful interface. Res = db.raw_sql('select c1,c2 from taos_test.member1') print(res) print(res.head) print(res.rowcount) # The res.head property is an array object that holds the column name of the representative of each row of data. The res.rowcount property holds the number of rows returned. # res: [[1.2, 2.2], [1.3, 2.1], [1.5, 2.0], [1.6, 2.1]] # res. Head: [' c1 ', 'the c2] # res. Rowcount: 4

2. Build and delete tables

Once the database objects are created, you can define and create new database tables by subclassing the Model class (using a similar approach to the ORM libraries commonly used in Python). Each new class corresponds to a table in the database, and each object of the class corresponds to an object in the table. The following example creates a simple database table:

If you do not explicitly define a Field of primary key type, you will add a primary key by default. The primary key is named "ts" class Meter1(Model): Cur = floatField () # If you omit the column name parameter, CurInt = integerField (db_column='c2') curDouble = doubleField (db_column='c3') desc = BinaryField(db_column='des') # custom_ts = primaryKeyField () # If a primary key column is defined, use the name of the primary key column as the primary key Db_table = 'meter1' db_table = 'meter1

The TDEngine field type is supported by the TDEngine field type.

After the table model is defined, we can call the class method create_table to create the table:

# create_table raises error meter1.create_table (safe=True) #safe: If the table exists, skip the table creation directive # can also be used by the database object create_table method. # db.create_table(Meter1,safe=True) # raise error meter1.drop_table (Meter1,safe=True) #safe: # db.drop_table(Meter1,safe=True) #table_exists (table_exists, table_exists, table_exists) False Meter1.table_exists()

3. Data insertion

You can create a new data object from the newly created data table class Meter1, pass in the values of the specific fields, and then insert the data using the save method of the object.

You can also insert data directly using the class method INSERT of class Meter1. The following examples illustrate both approaches:

Import time # for I in range(1,101): # Each object instantiated with the model class corresponds to each row in the table. M = Meter1(cur =1/ I,curInt= I,curDouble=1/ I +10,desc='g1',ts= datetime.datetime.now()) time.sleep(1) M.save () print(meter1.select ().count()) print(meter1.select ().count()) print(meter1.select ().count()) You can also insert data directly using the model class's insert method. Meter1.insert(cur = 1/i,curInt=i,curDouble=1/i+10,desc='g1',ts= datetime.datetime.now() - Datetime. timedelta(seconds=(102-i)))) print(meter1.select ().count()

If the time attribute ts is not passed in, the current time is passed in as the default value

Meter1.insert(cur = 1/i,curInt=i,curDouble=1/i+10,desc='g1')
m = Meter1(cur = 1/i,curInt=i,curDouble=1/i+10,desc='g1')

4. Data query

Crown provides a wealth of data query functions. For space reasons, here are only a few commonly used queries in the author’s project. For more information on how to use queries, please refer to the project documentation:

https://github.com/machine-w/crown/blob/main/README.rst

Single data query: Use the select() method of class Meter1 to get the query object of the table, and the one method of the query object can get the first data that meets the condition.

# fetching data: Get the query fields using the select() class method (leave the parameters blank to indicate that all fields are taken), Then can use one method to obtain the first data chain res = Meter1. Select () one () print (desc. Res., res. CurDouble, res. CurInt, res. Cur, res., ts) Res = meter1.select (meter1.cur, meter1.desc).one(); print(res.desc,res.curDouble,res.curInt,res.cur,res.ts)

Multiple data queries: The select() method of class Meter1 can get the query object of the table, and the all method of the query object can get all the data that meets the conditions.

Res_all = meter1.select ().all() for res in res_all: Print (desc. Res., res curDouble, res. CurInt, res. Cur, res., ts) # can choose to read fields in the select function res_all = Meter1.select(Meter1.cur,Meter1.desc).all() for res in res_all: print(res.desc,res.curDouble,res.curInt,res.cur,res.ts)

Import data into NumPy and Pandas: While Tdengine provides many aggregation and statistical functions, it is not uncommon to import temporal data into a data analysis component such as NumPy or Pandas for processing.

Here’s how to import the resulting data into Numpy and Pandas using crown:

The all_raw function is used to retrieve the results of the data query in a two-dimensional array format. The title represented by each column of the result is stored in the head property of the result object. Raw_results = Meter1. Select (Meter1. Cur, Meter1. CurInt, Meter1 curDouble). All_raw (#) can easily convert the result to np_data = numpy array object "Pandas" = "Pandas" = "Pandas" = "Pandas" = "Pandas" = "Pandas" = "Pandas" = "Pandas" You can easily import data into Pandas using the point in time as the index and the returned data title as the column name. pd_data = pd.DataFrame(raw_results,columns=raw_results.head).set_index('ts') print(pd_data)

Select the four column operations:

Select (); select(); select(); Select ((meter1.curDouble + meter1.cur), meter1.ts).all() for res in (+ - * / %) res_all = meter1.select ((meter1.curDouble + meter1.cur), meter1.ts).all() for res in res_all: Print (res.get(meter1.curDouble + meter1.cur),res.ts) Any other attribute placed in the select function can use the alias) res_all = Meter1.select(((Meter1.curDouble+Meter1.cur)*Meter1.curDouble).alias('new_name'),Meter1.ts).all() for res in res_all: Print (res.new_name,res.ts) print(res.new_name,res.ts)

Where the function:

One_time =datetime.datetime.now() - datetime.timedelta(hours=10) ress =1 Select (). Where (meter1.ts > one_time). All () # > <= = >= <=! = and or ! And so on. Ress = meter1.select (). WHERE (meter1.cur > 0 or meter1.desc %' g%').all(). The #where function can take as many arguments as it wants. Each argument is a qualified condition, with an "and" relationship between the parameters. ress = Meter1.select().where(Meter1.cur > 0, Meter1.ts > one_time, Meter1.desc % '%1').all()

Paging and limit:

# You can chain-call paginate after the select function to page. The following example takes page 6 with 5 pieces of data per page. Ress_1 = meter1.select ().paginate(6,page_size=5).all() ress_2 = meter1.select ().paginate(6).all() # Default page_size is 20 The limit and offset operations can be chained after the select function. ress_3 = Meter1.select().limit(2).offset(5).all() ress_4 = Meter1.select().limit(2).all()

Sorting: Currently TDEngine only supports primary key sorting

Res = meter1.select ().desc().one() res = meter1.select ().desc().

Here is an example of how to use it.

#count count = meter1.select (). Count () from print(count) # print(count) 100 count = meter1.select (). Count (meter1.desc) print(count) print(count) 90 # avg (sum, stddev, min, Max, first, last, last_row, spread usage is the same as the avg) avg1 = Meter1. The select (). Avg (Meter1. Cur, Meter1. CurDouble. Alias (' aa ') # can obtain more columns at the same time, Print (avg1.get(meter1.cur.avg ()),avg1.aa),avg1.aa) Select (twa1 = Meter1. Select (). WHERE (meter1.ts > datetime. 946118),Meter1.ts < datetime.datetime.now()).twa(Meter1.cur,Meter1.curDouble.alias('aa')) Print (twa1. Get (meter1.cur.twa ()),avg1.aa) #diff diffs = meter1.select ().diff(meter1.curint.alias ('aa')) #diff diffs = meter1.curint.alias #diff can currently aggregate only one attribute. for diff1 in diffs: Print (diff1.aa,diff1.ts) print(diff1.aa,diff1.ts) print(diff1.aa,diff1.ts) print(diff1.aa,diff1.ts) TOPS = Meter1. SELECT (). TOP (Meter1.cur,3,alias='aa') FOR TOPS IN TOPS: TOPS = Meter1. Print (top1.aa,top1.ts) # print(top1.aa,top1.ts) # print(top1.aa,top1.ts) # print(top1.aa,top1.ts) # print(top1.aa,top1.ts) # print(top1.aa,top1.ts) # print(top1.aa,top1.ts) # print(top1.aa,top1.ts) # print(top1.aa,top1.ts) # Print (top1.get(meter1.cur.top (3))) #percentile (percentile) #percentile (percentile) #percentile (percentile) #percentile (percentile) #percentile (percentile) P value (P value range 0≤P≤100), optional alias. Percentile1 = meter1.select ().percentile((meter1.cur,1,'aa'),(meter1.curDouble,2)) print(percentile1.aa) Need to use the get method gets an attribute print (percentile1. Get (Meter1. CurDouble. The percentile (2))) # # leastsquares each attribute parameters for a tuple (array), were defined to statistical properties, Start_val (initial value of the argument), step_val(step size value of the argument), optional alias. LEASTSQUARES1 = METER1. SELECT (). LEASTSQUARES2 FROM (METER1. CURDOUBLE,2,2) PRINT (LEASTSQUARES1 = METER1. {slop: 0.001595, intercept, 0.212111} # don't specify an alias, need to use the get method gets an attribute print (leastsquares1. Get (Meter1. CurDouble. Leastsquares (2, 2)))

Note: The current version does not support multiple table JOIN queries. If you need multiple table JOIN queries, use RAW_SQL to execute the original SQL statement. Join functionality will be added in later versions.

5. Super table definitions

The difference between a super table definition and a regular table is that it inherits from the SuperModel. Also, in the Meta class, tags can be defined. Supertable query operations are used in the same way as regular table queries. All of the methods described above can be used in super table classes as well. Labels can also be used as normal fields when querying operations.

Class = 'SuperModel'; class = 'SuperModel'; class = 'SuperModel'; cur = FloatField(db_column='c1') curInt = IntegerField(db_column='c2') curDouble = DoubleField(db_column='c3') desc = BinaryField(db_column='des') class Meta: = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = Location = binaryField (max_length=30) groupid = integerField (db_column='gid') # create a super table for the tag location = binaryField (max_length=30) groupid = integerField (db_column='gid') # = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Create a subtable from a supertable

For data insert operations, you need to create child tables from the super table. You can create child tables using the create_son_table method of the Meters class. This method returns a class object corresponding to a child table. This object can be used just like the normal table class object (Meter1) described above.

# Automatically create a table in the database while creating a table model class. SonTable_d3 = Meters. Create_son_table ('d3',location=' Beijing ',groupid=3) # SonTable_d3 is used in the same way as the model class inherited from the Modle class. M = SonTable_d3(cur = 65.8,curInt=10,curDouble=1.1,desc='g1',ts =1) datetime.datetime.now()) m.save()

The above describes the basic installation and use of Tdengine’s Python ORM connection library Crown. In addition to the above, Crown provides a number of useful features, such as dynamic table building, retrieving model classes by table name, grouping queries, and more. Interested readers can check out the usage documentation on GitHub. You are also welcome to post valuable comments and bug reports on GitHub. I will continue to maintain this project and strive to provide richer functionality and more complete documentation for everyone to use.

conclusion

As an excellent domestic open source software, TDengine has elegant software design and excellent performance. It is very suitable to be used as a data base platform in the application scenario of big data in the Internet of Things. In the future, with the booming development of the Internet of Things industry, TDengine will also become a part of the big data infrastructure of the Internet of Things and attract wide attention from practitioners in related fields all over the world. As an ordinary developer in the Internet of Things industry, the author is very honored to have the opportunity to develop and maintain such an open source small project around Tdengine. It is hoped that this project can make more developers more convenient to use TDEngine and improve work efficiency. We also hope that it can play a role in attracting more developers to join the development of open source projects, and make contributions to enrich the surrounding ecology of Tdengine together.

If you have a good idea that you’d like to implement with TDengine, you’re welcome to be a Contributor Family. Join us by clicking the link in the description!

https://www.taosdata.com/cn/contributor/