Edit page in Livemark
(2023-01-25 11:55)

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.from_sql('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.to_sql('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

(*, title: Optional[str] = None, description: Optional[str] = None, driver: Optional[str] = None, user: Optional[str] = None, password: Optional[str] = None, host: Optional[str] = None, port: Optional[int] = None, database: Optional[str] = None, params: Optional[dict] = None, table: Optional[str] = None, order_by: Optional[str] = None, where: Optional[str] = None, namespace: Optional[str] = None, basepath: Optional[str] = None) -> None

Parameters

  • title (Optional[str])
  • description (Optional[str])
  • driver (Optional[str])
  • user (Optional[str])
  • password (Optional[str])
  • host (Optional[str])
  • port (Optional[int])
  • database (Optional[str])
  • params (Optional[dict])
  • table (Optional[str])
  • order_by (Optional[str])
  • where (Optional[str])
  • namespace (Optional[str])
  • basepath (Optional[str])

formats.sqlControl.driver (property)

Database driver

Signature

Optional[str]

formats.sqlControl.user (property)

Database user

Signature

Optional[str]

formats.sqlControl.password (property)

Database password

Signature

Optional[str]

formats.sqlControl.host (property)

Database host

Signature

Optional[str]

formats.sqlControl.port (property)

Database port

Signature

Optional[int]

formats.sqlControl.database (property)

Database name

Signature

Optional[str]

formats.sqlControl.params (property)

Database query string parameters

Signature

Optional[dict]

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]