This is an automated email from the ASF dual-hosted git repository.
haonan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new a7466e551a [IOTDB-3466] Python client add support for SQLAlchemy
Dialect (#6255)
a7466e551a is described below
commit a7466e551a1e6aef41ab45d1630b611f6ed33097
Author: Leiyang <[email protected]>
AuthorDate: Fri Jun 17 14:40:22 2022 +0800
[IOTDB-3466] Python client add support for SQLAlchemy Dialect (#6255)
---
client-py/README.md | 95 ++++++++
client-py/iotdb/dbapi/Cursor.py | 10 +-
client-py/iotdb/sqlalchemy/IoTDBDialect.py | 136 ++++++++++++
.../sqlalchemy/IoTDBIdentifierPreparer.py} | 14 +-
client-py/iotdb/sqlalchemy/IoTDBSQLCompiler.py | 243 +++++++++++++++++++++
.../sqlalchemy/IoTDBTypeCompiler.py} | 32 ++-
.../sqlalchemy/__init__.py} | 6 -
.../sqlalchemy/tests/__init__.py} | 6 -
client-py/iotdb/sqlalchemy/tests/test_dialect.py | 92 ++++++++
client-py/requirements.txt | 5 +-
client-py/setup.py | 7 +
.../UserGuide/API/Programming-Python-Native-API.md | 100 ++++++++-
.../UserGuide/API/Programming-Python-Native-API.md | 97 +++++++-
13 files changed, 814 insertions(+), 29 deletions(-)
diff --git a/client-py/README.md b/client-py/README.md
index 3166acacc4..beef55e351 100644
--- a/client-py/README.md
+++ b/client-py/README.md
@@ -273,6 +273,11 @@ session.execute_query_statement(sql)
session.execute_non_query_statement(sql)
```
+* Execute statement
+
+```python
+session.execute_statement(sql)
+```
### Schema Template
#### Create Schema Template
@@ -463,6 +468,96 @@ cursor.close()
conn.close()
```
+### IoTDB SQLAlchemy Dialect (Experimental)
+The SQLAlchemy dialect of IoTDB is written to adapt to Apache Superset.
+This part is still being improved.
+Please do not use it in the production environment!
+#### Mapping of the metadata
+The data model used by SQLAlchemy is a relational data model, which describes
the relationships between different entities through tables.
+While the data model of IoTDB is a hierarchical data model, which organizes
the data through a tree structure.
+In order to adapt IoTDB to the dialect of SQLAlchemy, the original data model
in IoTDB needs to be reorganized.
+Converting the data model of IoTDB into the data model of SQLAlchemy.
+
+The metadata in the IoTDB are：
+
+1. Storage Group
+2. Path
+3. Entity
+4. Measurement
+
+The metadata in the SQLAlchemy are：
+1. Schema
+2. Table
+3. Column
+
+The mapping relationship between them is：
+
+| The metadata in the SQLAlchemy | The metadata in the IoTDB
|
+| -------------------- | ---------------------------------------------- |
+| Schema | Storage Group |
+| Table | Path ( from storage group to entity ) + Entity |
+| Column | Measurement |
+
+The following figure shows the relationship between the two more intuitively:
+
+
+
+#### Data type mapping
+| data type in IoTDB | data type in SQLAlchemy |
+|--------------------|-------------------------|
+| BOOLEAN | Boolean |
+| INT32 | Integer |
+| INT64 | BigInteger |
+| FLOAT | Float |
+| DOUBLE | Float |
+| TEXT | Text |
+| LONG | BigInteger |
+#### Example
+
++ execute statement
+
+```python
+from sqlalchemy import create_engine
+
+engine = create_engine("iotdb://root:[email protected]:6667")
+connect = engine.connect()
+result = connect.execute("SELECT ** FROM root")
+for row in result.fetchall():
+ print(row)
+```
+
++ ORM (now only simple queries are supported)
+
+```python
+from sqlalchemy import create_engine, Column, Float, BigInteger, MetaData
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import sessionmaker
+
+metadata = MetaData(
+ schema='root.factory'
+)
+Base = declarative_base(metadata=metadata)
+
+
+class Device(Base):
+ __tablename__ = "room2.device1"
+ Time = Column(BigInteger, primary_key=True)
+ temperature = Column(Float)
+ status = Column(Float)
+
+
+engine = create_engine("iotdb://root:[email protected]:6667")
+
+DbSession = sessionmaker(bind=engine)
+session = DbSession()
+
+res = session.query(Device.status).filter(Device.temperature > 1)
+
+for row in res:
+ print(row)
+```
+
+
## Developers
### Introduction
diff --git a/client-py/iotdb/dbapi/Cursor.py b/client-py/iotdb/dbapi/Cursor.py
index fd680835ba..a1d6e2caab 100644
--- a/client-py/iotdb/dbapi/Cursor.py
+++ b/client-py/iotdb/dbapi/Cursor.py
@@ -111,6 +111,7 @@ class Cursor(object):
sql = operation % parameters
time_index = []
+ time_names = []
if self.__sqlalchemy_mode:
sql_seqs = []
seqs = sql.split("\n")
@@ -120,6 +121,10 @@ class Cursor(object):
int(index)
for index in seq.replace("FROM Time Index", "").split()
]
+ elif seq.find("FROM Time Name") >= 0:
+ time_names = [
+ name for name in seq.replace("FROM Time Name",
"").split()
+ ]
else:
sql_seqs.append(seq)
sql = "\n".join(sql_seqs)
@@ -137,8 +142,8 @@ class Cursor(object):
time_column = data.columns[0]
time_column_value = data.Time
del data[time_column]
- for index in time_index:
- data.insert(index, time_column + str(index),
time_column_value)
+ for i in range(len(time_index)):
+ data.insert(time_index[i], time_names[i],
time_column_value)
col_names = data.columns.tolist()
col_types = data_set.get_column_types()
@@ -152,6 +157,7 @@ class Cursor(object):
"row_count": len(rows),
}
except Exception:
+ logger.error("failed to execute statement:{}".format(sql))
self.__result = {
"col_names": None,
"col_types": None,
diff --git a/client-py/iotdb/sqlalchemy/IoTDBDialect.py
b/client-py/iotdb/sqlalchemy/IoTDBDialect.py
new file mode 100644
index 0000000000..baf5d6525d
--- /dev/null
+++ b/client-py/iotdb/sqlalchemy/IoTDBDialect.py
@@ -0,0 +1,136 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+#
+
+from sqlalchemy import types, util
+from sqlalchemy.engine import default
+from sqlalchemy.sql.sqltypes import String
+
+from iotdb import dbapi
+
+from .IoTDBSQLCompiler import IoTDBSQLCompiler
+from .IoTDBTypeCompiler import IoTDBTypeCompiler
+from .IoTDBIdentifierPreparer import IoTDBIdentifierPreparer
+
+TYPES_MAP = {
+ "BOOLEAN": types.Boolean,
+ "INT32": types.Integer,
+ "INT64": types.BigInteger,
+ "FLOAT": types.Float,
+ "DOUBLE": types.Float,
+ "TEXT": types.Text,
+ "LONG": types.BigInteger,
+}
+
+
+class IoTDBDialect(default.DefaultDialect):
+ name = "iotdb"
+ driver = "iotdb-python"
+ statement_compiler = IoTDBSQLCompiler
+ type_compiler = IoTDBTypeCompiler
+ preparer = IoTDBIdentifierPreparer
+ convert_unicode = True
+
+ supports_unicode_statements = True
+ supports_unicode_binds = True
+ supports_simple_order_by_label = False
+ supports_schemas = True
+ supports_right_nested_joins = False
+ description_encoding = None
+
+ if hasattr(String, "RETURNS_UNICODE"):
+ returns_unicode_strings = String.RETURNS_UNICODE
+ else:
+
+ def _check_unicode_returns(self, connection, additional_tests=None):
+ return True
+
+ _check_unicode_returns = _check_unicode_returns
+
+ def create_connect_args(self, url):
+ # inherits the docstring from interfaces.Dialect.create_connect_args
+ opts = url.translate_connect_args()
+ opts.update(url.query)
+ opts.update({"sqlalchemy_mode": True})
+ return [[], opts]
+
+ @classmethod
+ def dbapi(cls):
+ return dbapi
+
+ def has_schema(self, connection, schema):
+ return schema in self.get_schema_names(connection)
+
+ def has_table(self, connection, table_name, schema=None, **kw):
+ return table_name in self.get_table_names(connection, schema=schema)
+
+ def get_schema_names(self, connection, **kw):
+ cursor = connection.execute("SHOW STORAGE GROUP")
+ return [row[0] for row in cursor.fetchall()]
+
+ def get_table_names(self, connection, schema=None, **kw):
+ cursor = connection.execute(
+ "SHOW DEVICES %s.**" % (schema or self.default_schema_name)
+ )
+ return [row[0].replace(schema + ".", "", 1) for row in
cursor.fetchall()]
+
+ def get_columns(self, connection, table_name, schema=None, **kw):
+ cursor = connection.execute("SHOW TIMESERIES %s.%s.*" % (schema,
table_name))
+ columns = [self._general_time_column_info()]
+ for row in cursor.fetchall():
+ columns.append(self._create_column_info(row, schema, table_name))
+ return columns
+
+ def get_pk_constraint(self, connection, table_name, schema=None, **kw):
+ pass
+
+ def get_foreign_keys(self, connection, table_name, schema=None, **kw):
+ return []
+
+ def get_indexes(self, connection, table_name, schema=None, **kw):
+ return []
+
+ @util.memoized_property
+ def _dialect_specific_select_one(self):
+ # IoTDB does not support select 1
+ # so replace the statement with "show version"
+ return "SHOW VERSION"
+
+ def _general_time_column_info(self):
+ """
+ Treat Time as a column
+ """
+ return {
+ "name": "Time",
+ "type": self._resolve_type("LONG"),
+ "nullable": False,
+ "default": None,
+ }
+
+ def _create_column_info(self, row, schema, table_name):
+ """
+ Generate description information for each column
+ """
+ return {
+ "name": row[0].replace(schema + "." + table_name + ".", "", 1),
+ "type": self._resolve_type(row[3]),
+ "nullable": True,
+ "default": None,
+ }
+
+ def _resolve_type(self, type_):
+ return TYPES_MAP.get(type_, types.UserDefinedType)
diff --git a/client-py/requirements.txt
b/client-py/iotdb/sqlalchemy/IoTDBIdentifierPreparer.py
similarity index 72%
copy from client-py/requirements.txt
copy to client-py/iotdb/sqlalchemy/IoTDBIdentifierPreparer.py
index 566c75f79c..e09dd3c230 100644
--- a/client-py/requirements.txt
+++ b/client-py/iotdb/sqlalchemy/IoTDBIdentifierPreparer.py
@@ -16,8 +16,12 @@
# under the License.
#
-# Pandas Export
-pandas~=1.3.5
-# Testcontainer
-testcontainers==3.3.0
-numpy~=1.21.4
\ No newline at end of file
+from sqlalchemy.sql.compiler import IdentifierPreparer
+
+
+class IoTDBIdentifierPreparer(IdentifierPreparer):
+ def __init__(self, dialect, **kw):
+ quote = "`"
+ super(IoTDBIdentifierPreparer, self).__init__(
+ dialect, initial_quote=quote, escape_quote=quote, **kw
+ )
diff --git a/client-py/iotdb/sqlalchemy/IoTDBSQLCompiler.py
b/client-py/iotdb/sqlalchemy/IoTDBSQLCompiler.py
new file mode 100644
index 0000000000..36c4ca0bf7
--- /dev/null
+++ b/client-py/iotdb/sqlalchemy/IoTDBSQLCompiler.py
@@ -0,0 +1,243 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+#
+
+from sqlalchemy.sql.compiler import SQLCompiler
+from sqlalchemy.sql.compiler import OPERATORS
+from sqlalchemy.sql import operators
+
+
+class IoTDBSQLCompiler(SQLCompiler):
+ def order_by_clause(self, select, **kw):
+ """allow dialects to customize how ORDER BY is rendered."""
+
+ order_by = select._order_by_clause._compiler_dispatch(self, **kw)
+ if "Time" in order_by:
+ return " ORDER BY " + order_by.replace('"', "")
+ else:
+ return ""
+
+ def group_by_clause(self, select, **kw):
+ """allow dialects to customize how GROUP BY is rendered."""
+ return ""
+
+ def visit_select(
+ self,
+ select,
+ asfrom=False,
+ parens=True,
+ fromhints=None,
+ compound_index=0,
+ nested_join_translation=False,
+ select_wraps_for=None,
+ lateral=False,
+ **kwargs,
+ ):
+ """
+ Override this method to solve two problems
+ 1. IoTDB does not support querying Time as a measurement name (e.g.
select Time from root.storagegroup.device)
+ 2. IoTDB does not support path.measurement format to determine a
column (e.g. select root.storagegroup.device.temperature from
root.storagegroup.device)
+ """
+ needs_nested_translation = (
+ select.use_labels
+ and not nested_join_translation
+ and not self.stack
+ and not self.dialect.supports_right_nested_joins
+ )
+
+ if needs_nested_translation:
+ transformed_select =
self._transform_select_for_nested_joins(select)
+ text = self.visit_select(
+ transformed_select,
+ asfrom=asfrom,
+ parens=parens,
+ fromhints=fromhints,
+ compound_index=compound_index,
+ nested_join_translation=True,
+ **kwargs,
+ )
+
+ toplevel = not self.stack
+ entry = self._default_stack_entry if toplevel else self.stack[-1]
+
+ populate_result_map = need_column_expressions = (
+ toplevel
+ or entry.get("need_result_map_for_compound", False)
+ or entry.get("need_result_map_for_nested", False)
+ )
+
+ if compound_index > 0:
+ populate_result_map = False
+
+ # this was first proposed as part of #3372; however, it is not
+ # reached in current tests and could possibly be an assertion
+ # instead.
+ if not populate_result_map and "add_to_result_map" in kwargs:
+ del kwargs["add_to_result_map"]
+
+ if needs_nested_translation:
+ if populate_result_map:
+ self._transform_result_map_for_nested_joins(select,
transformed_select)
+ return text
+
+ froms = self._setup_select_stack(select, entry, asfrom, lateral)
+
+ column_clause_args = kwargs.copy()
+ column_clause_args.update(
+ {"within_label_clause": False, "within_columns_clause": False}
+ )
+
+ text = "SELECT " # we're off to a good start !
+
+ if select._hints:
+ hint_text, byfrom = self._setup_select_hints(select)
+ if hint_text:
+ text += hint_text + " "
+ else:
+ byfrom = None
+
+ if select._prefixes:
+ text += self._generate_prefixes(select, select._prefixes, **kwargs)
+
+ text += self.get_select_precolumns(select, **kwargs)
+ # the actual list of columns to print in the SELECT column list.
+ # IoTDB does not support querying Time as a measurement name (e.g.
select Time from root.storagegroup.device)
+ columns = []
+ for name, column in select._columns_plus_names:
+ column.table = None
+ columns.append(
+ self._label_select_column(
+ select,
+ column,
+ populate_result_map,
+ asfrom,
+ column_clause_args,
+ name=name,
+ need_column_expressions=need_column_expressions,
+ )
+ )
+ inner_columns = [c for c in columns if c is not None]
+
+ if populate_result_map and select_wraps_for is not None:
+ # if this select is a compiler-generated wrapper,
+ # rewrite the targeted columns in the result map
+
+ translate = dict(
+ zip(
+ [name for (key, name) in select._columns_plus_names],
+ [name for (key, name) in
select_wraps_for._columns_plus_names],
+ )
+ )
+
+ self._result_columns = [
+ (key, name, tuple(translate.get(o, o) for o in obj), type_)
+ for key, name, obj, type_ in self._result_columns
+ ]
+ # IoTDB does not allow to query Time as column,
+ # need to filter out Time and pass Time and Time's alias to DBAPI
separately
+ # to achieve the query of Time by encoding.
+ time_column_index = []
+ time_column_names = []
+ for i in range(len(inner_columns)):
+ column_strs = (
+ inner_columns[i].replace(self.preparer.initial_quote,
"").split()
+ )
+ if "Time" in column_strs:
+ time_column_index.append(str(i))
+ time_column_names.append(
+ column_strs[2]
+ if OPERATORS[operators.as_] in column_strs
+ else column_strs[0]
+ )
+ # delete Time column
+ inner_columns = list(
+ filter(
+ lambda x: "Time"
+ not in x.replace(self.preparer.initial_quote, "").split(),
+ inner_columns,
+ )
+ )
+ if inner_columns and time_column_index:
+ inner_columns[-1] = (
+ inner_columns[-1]
+ + " \n FROM Time Index "
+ + " ".join(time_column_index)
+ + "\n FROM Time Name "
+ + " ".join(time_column_names)
+ )
+
+ text = self._compose_select_body(
+ text, select, inner_columns, froms, byfrom, kwargs
+ )
+
+ if select._statement_hints:
+ per_dialect = [
+ ht
+ for (dialect_name, ht) in select._statement_hints
+ if dialect_name in ("*", self.dialect.name)
+ ]
+ if per_dialect:
+ text += " " + self.get_statement_hint_text(per_dialect)
+
+ if self.ctes and toplevel:
+ text = self._render_cte_clause() + text
+
+ if select._suffixes:
+ text += " " + self._generate_prefixes(select, select._suffixes,
**kwargs)
+
+ self.stack.pop(-1)
+
+ if (asfrom or lateral) and parens:
+ return "(" + text + ")"
+ else:
+ return text
+
+ def visit_table(
+ self,
+ table,
+ asfrom=False,
+ iscrud=False,
+ ashint=False,
+ fromhints=None,
+ use_schema=True,
+ **kwargs,
+ ):
+ """
+ IoTDB's table does not support quotation marks (e.g. select ** from
`root.`)
+ need to override this method
+ """
+ if asfrom or ashint:
+ effective_schema = self.preparer.schema_for_object(table)
+
+ if use_schema and effective_schema:
+ ret = effective_schema + "." + table.name
+ else:
+ ret = table.name
+ if fromhints and table in fromhints:
+ ret = self.format_from_hint_text(ret, table, fromhints[table],
iscrud)
+ return ret
+ else:
+ return ""
+
+ def visit_column(
+ self, column, add_to_result_map=None, include_table=True, **kwargs
+ ):
+ """
+ IoTDB's where statement does not support "table".column format(e.g.
"table".column > 1)
+ need to override this method to return the name of column directly
+ """
+ return column.name
diff --git a/client-py/requirements.txt
b/client-py/iotdb/sqlalchemy/IoTDBTypeCompiler.py
similarity index 55%
copy from client-py/requirements.txt
copy to client-py/iotdb/sqlalchemy/IoTDBTypeCompiler.py
index 566c75f79c..4cfd2480bd 100644
--- a/client-py/requirements.txt
+++ b/client-py/iotdb/sqlalchemy/IoTDBTypeCompiler.py
@@ -16,8 +16,30 @@
# under the License.
#
-# Pandas Export
-pandas~=1.3.5
-# Testcontainer
-testcontainers==3.3.0
-numpy~=1.21.4
\ No newline at end of file
+from sqlalchemy.sql.compiler import GenericTypeCompiler
+
+
+class IoTDBTypeCompiler(GenericTypeCompiler):
+ def visit_FLOAT(self, type_, **kw):
+ return "FLOAT"
+
+ def visit_NUMERIC(self, type_, **kw):
+ return "INT64"
+
+ def visit_DECIMAL(self, type_, **kw):
+ return "DOUBLE"
+
+ def visit_INTEGER(self, type_, **kw):
+ return "INT32"
+
+ def visit_SMALLINT(self, type_, **kw):
+ return "INT32"
+
+ def visit_BIGINT(self, type_, **kw):
+ return "LONG"
+
+ def visit_TIMESTAMP(self, type_, **kw):
+ return "LONG"
+
+ def visit_text(self, type_, **kw):
+ return "TEXT"
diff --git a/client-py/requirements.txt b/client-py/iotdb/sqlalchemy/__init__.py
similarity index 90%
copy from client-py/requirements.txt
copy to client-py/iotdb/sqlalchemy/__init__.py
index 566c75f79c..2a1e720805 100644
--- a/client-py/requirements.txt
+++ b/client-py/iotdb/sqlalchemy/__init__.py
@@ -15,9 +15,3 @@
# specific language governing permissions and limitations
# under the License.
#
-
-# Pandas Export
-pandas~=1.3.5
-# Testcontainer
-testcontainers==3.3.0
-numpy~=1.21.4
\ No newline at end of file
diff --git a/client-py/requirements.txt
b/client-py/iotdb/sqlalchemy/tests/__init__.py
similarity index 90%
copy from client-py/requirements.txt
copy to client-py/iotdb/sqlalchemy/tests/__init__.py
index 566c75f79c..2a1e720805 100644
--- a/client-py/requirements.txt
+++ b/client-py/iotdb/sqlalchemy/tests/__init__.py
@@ -15,9 +15,3 @@
# specific language governing permissions and limitations
# under the License.
#
-
-# Pandas Export
-pandas~=1.3.5
-# Testcontainer
-testcontainers==3.3.0
-numpy~=1.21.4
\ No newline at end of file
diff --git a/client-py/iotdb/sqlalchemy/tests/test_dialect.py
b/client-py/iotdb/sqlalchemy/tests/test_dialect.py
new file mode 100644
index 0000000000..7b0e3e2ad6
--- /dev/null
+++ b/client-py/iotdb/sqlalchemy/tests/test_dialect.py
@@ -0,0 +1,92 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+#
+
+import operator
+
+from sqlalchemy import create_engine, inspect
+from sqlalchemy.dialects import registry
+
+from iotdb.IoTDBContainer import IoTDBContainer
+
+final_flag = True
+failed_count = 0
+
+
+def test_fail():
+ global failed_count
+ global final_flag
+ final_flag = False
+ failed_count += 1
+
+
+def print_message(message):
+ print("*********")
+ print(message)
+ print("*********")
+
+
+def test_dialect():
+ with IoTDBContainer("iotdb:dev") as db:
+ db: IoTDBContainer
+ url = (
+ "iotdb://root:root@"
+ + db.get_container_host_ip()
+ + ":"
+ + db.get_exposed_port(6667)
+ )
+ registry.register("iotdb", "iotdb.sqlalchemy.IoTDBDialect",
"IoTDBDialect")
+ eng = create_engine(url)
+ eng.execute("create storage group root.cursor")
+ eng.execute("create storage group root.cursor_s1")
+ eng.execute(
+ "create timeseries root.cursor.device1.temperature with
datatype=FLOAT,encoding=RLE"
+ )
+ eng.execute(
+ "create timeseries root.cursor.device1.status with
datatype=FLOAT,encoding=RLE"
+ )
+ eng.execute(
+ "create timeseries root.cursor.device2.temperature with
datatype=FLOAT,encoding=RLE"
+ )
+ insp = inspect(eng)
+ # test get_schema_names
+ schema_names = insp.get_schema_names()
+ if not operator.eq(schema_names, ["root.cursor", "root.cursor_s1"]):
+ test_fail()
+ print_message("test get_schema_names failed!")
+ # test get_table_names
+ table_names = insp.get_table_names("root.cursor")
+ if not operator.eq(table_names, ["device1", "device2"]):
+ test_fail()
+ print_message("test get_table_names failed!")
+ # test get_columns
+ columns = insp.get_columns(table_name="device1", schema="root.cursor")
+ if len(columns) != 3:
+ test_fail()
+ print_message("test get_columns failed!")
+ eng.execute("delete storage group root.cursor")
+ eng.execute("delete storage group root.cursor_s1")
+ # close engine
+ eng.dispose()
+
+
+if final_flag:
+ print("All executions done!!")
+else:
+ print("Some test failed, please have a check")
+ print("failed count: ", failed_count)
+ exit(1)
diff --git a/client-py/requirements.txt b/client-py/requirements.txt
index 566c75f79c..8715d50595 100644
--- a/client-py/requirements.txt
+++ b/client-py/requirements.txt
@@ -20,4 +20,7 @@
pandas~=1.3.5
# Testcontainer
testcontainers==3.3.0
-numpy~=1.21.4
\ No newline at end of file
+numpy~=1.21.4
+# SQLAlchemy Dialect
+sqlalchemy == 1.3.20
+sqlalchemy-utils == 0.36.8
\ No newline at end of file
diff --git a/client-py/setup.py b/client-py/setup.py
index 11e30cf40f..a3f147af3c 100644
--- a/client-py/setup.py
+++ b/client-py/setup.py
@@ -44,6 +44,8 @@ setuptools.setup(
"pandas>=1.0.0,<1.99.99",
"numpy>=1.0.0",
"testcontainers>=2.0.0",
+ "sqlalchemy>=1.3.16, <1.4, !=1.3.21",
+ "sqlalchemy-utils>=0.37.8, <0.38",
],
classifiers=[
"Programming Language :: Python :: 3",
@@ -55,4 +57,9 @@ setuptools.setup(
python_requires=">=3.7",
license="Apache License, Version 2.0",
website="https://iotdb.apache.org";,
+ entry_points={
+ "sqlalchemy.dialects": [
+ "iotdb = iotdb.sqlalchemy.IoTDBDialect:IoTDBDialect",
+ ],
+ },
)
diff --git a/docs/UserGuide/API/Programming-Python-Native-API.md
b/docs/UserGuide/API/Programming-Python-Native-API.md
index c3e39e6cd7..0f962ebb44 100644
--- a/docs/UserGuide/API/Programming-Python-Native-API.md
+++ b/docs/UserGuide/API/Programming-Python-Native-API.md
@@ -251,6 +251,11 @@ session.execute_query_statement(sql)
session.execute_non_query_statement(sql)
```
+* Execute statement
+
+```python
+session.execute_statement(sql)
+```
### Schema Template
#### Create Schema Template
@@ -407,7 +412,7 @@ cursor = conn.cursor()
```
+ simple SQL statement execution
```python
-cursor.execute("SELECT * FROM root.*")
+cursor.execute("SELECT ** FROM root")
for row in cursor.fetchall():
print(row)
```
@@ -416,7 +421,7 @@ for row in cursor.fetchall():
IoTDB DBAPI supports pyformat style parameters
```python
-cursor.execute("SELECT * FROM root.* WHERE time <
%(time)s",{"time":"2017-11-01T00:08:00.000"})
+cursor.execute("SELECT ** FROM root WHERE time <
%(time)s",{"time":"2017-11-01T00:08:00.000"})
for row in cursor.fetchall():
print(row)
```
@@ -440,6 +445,97 @@ cursor.close()
conn.close()
```
+### IoTDB SQLAlchemy Dialect (Experimental)
+The SQLAlchemy dialect of IoTDB is written to adapt to Apache Superset.
+This part is still being improved.
+Please do not use it in the production environment!
+#### Mapping of the metadata
+The data model used by SQLAlchemy is a relational data model, which describes
the relationships between different entities through tables.
+While the data model of IoTDB is a hierarchical data model, which organizes
the data through a tree structure.
+In order to adapt IoTDB to the dialect of SQLAlchemy, the original data model
in IoTDB needs to be reorganized.
+Converting the data model of IoTDB into the data model of SQLAlchemy.
+
+The metadata in the IoTDB are：
+
+1. Storage Group
+2. Path
+3. Entity
+4. Measurement
+
+The metadata in the SQLAlchemy are：
+1. Schema
+2. Table
+3. Column
+
+The mapping relationship between them is：
+
+| The metadata in the SQLAlchemy | The metadata in the IoTDB
|
+| -------------------- | ---------------------------------------------- |
+| Schema | Storage Group |
+| Table | Path ( from storage group to entity ) + Entity |
+| Column | Measurement |
+
+The following figure shows the relationship between the two more intuitively:
+
+
+
+#### Data type mapping
+| data type in IoTDB | data type in SQLAlchemy |
+|--------------------|-------------------------|
+| BOOLEAN | Boolean |
+| INT32 | Integer |
+| INT64 | BigInteger |
+| FLOAT | Float |
+| DOUBLE | Float |
+| TEXT | Text |
+| LONG | BigInteger |
+
+#### Example
+
++ execute statement
+
+```python
+from sqlalchemy import create_engine
+
+engine = create_engine("iotdb://root:[email protected]:6667")
+connect = engine.connect()
+result = connect.execute("SELECT ** FROM root")
+for row in result.fetchall():
+ print(row)
+```
+
++ ORM (now only simple queries are supported)
+
+```python
+from sqlalchemy import create_engine, Column, Float, BigInteger, MetaData
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import sessionmaker
+
+metadata = MetaData(
+ schema='root.factory'
+)
+Base = declarative_base(metadata=metadata)
+
+
+class Device(Base):
+ __tablename__ = "room2.device1"
+ Time = Column(BigInteger, primary_key=True)
+ temperature = Column(Float)
+ status = Column(Float)
+
+
+engine = create_engine("iotdb://root:[email protected]:6667")
+
+DbSession = sessionmaker(bind=engine)
+session = DbSession()
+
+res = session.query(Device.status).filter(Device.temperature > 1)
+
+for row in res:
+ print(row)
+```
+
+
## Developers
### Introduction
diff --git a/docs/zh/UserGuide/API/Programming-Python-Native-API.md
b/docs/zh/UserGuide/API/Programming-Python-Native-API.md
index 645e281008..9f385081bd 100644
--- a/docs/zh/UserGuide/API/Programming-Python-Native-API.md
+++ b/docs/zh/UserGuide/API/Programming-Python-Native-API.md
@@ -246,6 +246,12 @@ session.execute_query_statement(sql)
session.execute_non_query_statement(sql)
```
+* 执行语句
+
+```python
+session.execute_statement(sql)
+```
+
### 元数据模版接口
#### 构建元数据模版
@@ -401,7 +407,7 @@ cursor = conn.cursor()
```
+ 执行简单的SQL语句
```python
-cursor.execute("SELECT * FROM root.*")
+cursor.execute("SELECT ** FROM root")
for row in cursor.fetchall():
print(row)
```
@@ -410,7 +416,7 @@ for row in cursor.fetchall():
IoTDB DBAPI 支持pyformat风格的参数
```python
-cursor.execute("SELECT * FROM root.* WHERE time <
%(time)s",{"time":"2017-11-01T00:08:00.000"})
+cursor.execute("SELECT ** FROM root WHERE time <
%(time)s",{"time":"2017-11-01T00:08:00.000"})
for row in cursor.fetchall():
print(row)
```
@@ -434,6 +440,93 @@ cursor.close()
conn.close()
```
+### IoTDB SQLAlchemy Dialect（实验性）
+IoTDB的SQLAlchemy方言主要是为了适配Apache superset而编写的，该部分仍在完善中，请勿在生产环境中使用！
+#### 元数据模型映射
+SQLAlchemy 所使用的数据模型为关系数据模型，这种数据模型通过表格来描述不同实体之间的关系。
+而 IoTDB 的数据模型为层次数据模型，通过树状结构来对数据进行组织。
+为了使 IoTDB 能够适配 SQLAlchemy 的方言，需要对 IoTDB 中原有的数据模型进行重新组织，
+把 IoTDB 的数据模型转换成 SQLAlchemy 的数据模型。
+
+IoTDB 中的元数据有：
+
+1. Storage Group：存储组
+2. Path：存储路径
+3. Entity：实体
+4. Measurement：物理量
+
+SQLAlchemy 中的元数据有：
+1. Schema：数据模式
+2. Table：数据表
+3. Column：数据列
+
+它们之间的映射关系为：
+
+| SQLAlchemy中的元数据 | IoTDB中对应的元数据 |
+| -------------------- | ---------------------------------------------- |
+| Schema | Storage Group |
+| Table | Path ( from storage group to entity ) + Entity |
+| Column | Measurement |
+
+下图更加清晰的展示了二者的映射关系：
+
+
+
+#### 数据类型映射
+| IoTDB 中的数据类型 | SQLAlchemy 中的数据类型 |
+|--------------|-------------------|
+| BOOLEAN | Boolean |
+| INT32 | Integer |
+| INT64 | BigInteger |
+| FLOAT | Float |
+| DOUBLE | Float |
+| TEXT | Text |
+| LONG | BigInteger |
+#### Example
+
++ 执行语句
+
+```python
+from sqlalchemy import create_engine
+
+engine = create_engine("iotdb://root:[email protected]:6667")
+connect = engine.connect()
+result = connect.execute("SELECT ** FROM root")
+for row in result.fetchall():
+ print(row)
+```
+
++ ORM (目前只支持简单的查询)
+
+```python
+from sqlalchemy import create_engine, Column, Float, BigInteger, MetaData
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import sessionmaker
+
+metadata = MetaData(
+ schema='root.factory'
+)
+Base = declarative_base(metadata=metadata)
+
+
+class Device(Base):
+ __tablename__ = "room2.device1"
+ Time = Column(BigInteger, primary_key=True)
+ temperature = Column(Float)
+ status = Column(Float)
+
+
+engine = create_engine("iotdb://root:[email protected]:6667")
+
+DbSession = sessionmaker(bind=engine)
+session = DbSession()
+
+res = session.query(Device.status).filter(Device.temperature > 1)
+
+for row in res:
+ print(row)
+```
+
## 给开发人员
### 介绍
