Edit page in Livemark
(2023-06-06 13:09)

Sql Format

Frictionless supports reading and writing SQL databases.

pip install frictionless[sql]
pip install 'frictionless[sql]' # for zsh shell

Reading Data

You can read SQL database:

from pprint import pprint
from frictionless import Package

package = Package('postgresql://database')
pprint(package)
for resource in package.resources:
  pprint(resource.read_rows())

SQLite

Here is a example of reading a table from a SQLite database using basepath:

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())

Writing Data

You can write SQL databases:

from frictionless import Package

package = Package('path/to/datapackage.json')
package.publish('postgresql://database')

Configuration

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)

Reference

formats.SqlControl (class)

formats.SqlControl (class)

SQL control representation. Control class to set params for Sql read/write class.

Signature

(*, 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) -> None

Parameters

  • name (Optional[str])
  • title (Optional[str])
  • description (Optional[str])
  • table (Optional[str])
  • order_by (Optional[str])
  • where (Optional[str])
  • namespace (Optional[str])
  • basepath (Optional[str])

formats.sqlControl.table (property)

Table name from which to read the data.

Signature

Optional[str]

formats.sqlControl.order_by (property)

It specifies the ORDER BY keyword for SQL queries to sort the results that are being read. The default value is None.

Signature

Optional[str]

formats.sqlControl.where (property)

It specifies the WHERE clause to filter the records in SQL queries. The default value is None.

Signature

Optional[str]

formats.sqlControl.namespace (property)

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")

Signature

Optional[str]

formats.sqlControl.basepath (property)

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")

Signature

Optional[str]