Frictionless supports reading and writing SQL databases.
Frictionless Framework in-general support many databases that can be used with sqlalchemy. Here is a list of the databases with tested support:
It's a well-tested default database used by Frictionless:
pip install frictionless[sql]
This database is well-tested and provides the most data types:
pip install frictionless[postgresql]
Another popular databases having been tested with Frictionless:
pip install frictionless[mysql]
DuckDB is a reletively new database and, currently, Frictionless support is experimental:
pip install frictionless[duckdb]
You can read SQL database:
from frictionless import Resource, formats
control = SqlControl(table="test_table", basepath='data')
with Resource(path="sqlite:///sqlite.db", control=control) as resource:
print(resource.read_rows())
You can write SQL databases:
from frictionless import Package
package = Package('path/to/datapackage.json')
package.publish('postgresql://database')
There is a dialect to configure how Frictionless read and write files in this format. For example:
from frictionless import Resource, formats
control = SqlControl(table='table', order_by='field', where='field > 20')
resource = Resource('postgresql://database', control=control)
SQL control representation. Control class to set params for Sql read/write class.
(*, name: Optional[str] = None, title: Optional[str] = None, description: Optional[str] = None, table: Optional[str] = None, order_by: Optional[str] = None, where: Optional[str] = None, namespace: Optional[str] = None, basepath: Optional[str] = None, with_metadata: bool = False) -> None
Table name from which to read the data.
Optional[str]
It specifies the ORDER BY keyword for SQL queries to sort the results that are being read. The default value is None.
Optional[str]
It specifies the WHERE clause to filter the records in SQL queries. The default value is None.
Optional[str]
To refer to table using schema or namespace or database such as `FOO`.`TABLEFOO1` we can specify namespace. For example: control = formats.SqlControl(table="test_table", namespace="FOO")
Optional[str]
It specifies the base path for the database. The basepath will be appended to the db path. The default value is None. For example: formats.SqlControl(table="test_table", basepath="data")
Optional[str]
Indicates if a table contains metadata columns like _rowNumber or _rowValid
bool