Skip to main content

Export Airtable bases to an sqlite database

Project description

Airtable to SQlite

PyPI - Version PyPI - Python Version


Table of Contents

Installation

pip install airtable-to-sqlite

Usage

The tool is primarily intended to be used through the command line. Once installed, you can use it like this:

airtable-to-sqlite app123456789

This will fetch the base with the ID app123456789 and save it to a file called BaseName.db in your current directory.

Authentication

For the tool to work you need to authenticate with Airtable API using a personal access token. To generate a token visit your Airtable account.

There are two ways to use the token. You can set it as an environment variable (called AIRTABLE_PERSONAL_ACCESS_TOKEN), and the tool will pick it up:

export AIRTABLE_PERSONAL_ACCESS_TOKEN=patABCDE123456789
airtable-to-sqlite app123456789

Or you can pass it to the tool directly:

airtable-to-sqlite --personal-access-token patABCDE123456789 app123456789

Download more than one base

You can add more Base IDs to download more than one Base.

airtable-to-sqlite app123456789 app567891234

These will be saved to Base1Name.db and Base2Name.db respectively.

Customise the output file

To customise the name of the file where the database will be saved, just pass the --output parameter. So for example:

airtable-to-sqlite --output "output.db" app123456789

Will save the file to output.db. If you include {} in the filename it will be replaced with the name of the base. So for example:

airtable-to-sqlite --output "db/{}/output.db" app123456789

Will result in a file saved to db/BaseName/output.db.

The string {} must be included if more than one Base is requested, omitting it will produce an error.

Use IDs instead of names

By default, the tool will use the names of tables, fields and bases. You can use the --prefer-ids flag to tell the tool to use the IDs instead.

If this flag is used it will mean the filenames use the Base ID (eg app123456789.db instead of BaseName.db), the table names will use IDs instead of names, and columns within the tables will be named using IDs.

This may be helpful if your table or field names contain characters that can't be used in sqlite. You can use the _meta_table and _meta_field tables (see below) to find the names of the tables and columns

Database format

Each table within the Airtable Base gets in own table within the database. Each of these tables always contains two default fields, and then the rest of the data from the table. The additional fields are:

  • _id: The airtable ID for the record. This is set as the primary key
  • _createdTime: The date and time the record was created.

All fields are stored in the database, with the exception of fields with the type multipleRecordLinks, which are instead stored in a linking table.

Where possible, the tool will attempt to assign an appropriate column type to each field. Note that constraints on these fields are not enforced by sqlite by default, so the database may contain invalid data.

Storage for linked records

Where a field has the type multipleRecordLinks, i.e. where it is a record that links to other records in another table, a linking table is created. The name of this table is {table_name}_{field_id}, and it always contains two columns, with foreign key constraints to their tables:

  • recordId: the record in the original table
  • otherRecordId: the record in the linked table

Note that these fields contain many-to-many relationships, so values in both fields may appear more than once.

Meta tables

In addition to the main data tables from the Base, the tool creates tables holding metadata about the original Base and the export process. These tables are:

_meta_table

A record for each table in the Base. Fields are:

  • id: (str) Airtable Table ID
  • name: (str) Table name
  • primaryFieldId: (str) ID of the primary field

_meta_field

A record for each field in each table in the Base. Fields are (only the first 4 fields are mandatory):

  • id: (str) Airtable Field ID
  • name: (str) Field Name
  • type: (str) Type of field
  • tableId: (str) Airtable Table ID
  • options: (json) Any remaining options not covered by other variables
  • linkedTableId: (str) For fields of type multipleRecordLinks, the other table looked up
  • isReversed: (bool)
  • prefersSingleRecordLink: (bool)
  • inverseLinkFieldId: (str)
  • isValid: (bool)
  • recordLinkFieldId: (str)
  • icon: (str)
  • color: (str)
  • referencedFieldIds: (str)
  • result: (str)
  • precision: (str)
  • symbol: (str)

_meta_field_choice

Where a field has choices (e.g. where it is a single or multiple select field), this table contains the options. Fields are:

  • id: (str) Choice ID
  • name: (str) Choice Name
  • color: (str)
  • fieldId: (str) Airtable Field ID

_meta_view

A record for each view in the Base. Fields are:

  • id: (str) Airtable View ID
  • name: (str) View name
  • tableId: (str) Airtable Table ID

This table doesn't contain enough information to reconstruct the view.

_meta_settings

Each record contains a key value pair with a piece of metadata, for example the original Base ID and Base Name. Fields are:

  • key: (str)
  • value: (str)

Alternatives

Future development

Potential future developments include:

  • Viewer/editor for exported files
  • Greater coverage of available fields
  • export to Excel (could be a separate tool)

Contributions are very welcome.

License

airtable-to-sqlite is distributed under the terms of the MIT license.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

airtable_to_sqlite-0.2.1.tar.gz (19.9 MB view details)

Uploaded Source

Built Distribution

airtable_to_sqlite-0.2.1-py3-none-any.whl (10.8 kB view details)

Uploaded Python 3

File details

Details for the file airtable_to_sqlite-0.2.1.tar.gz.

File metadata

  • Download URL: airtable_to_sqlite-0.2.1.tar.gz
  • Upload date:
  • Size: 19.9 MB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: python-httpx/0.25.1

File hashes

Hashes for airtable_to_sqlite-0.2.1.tar.gz
Algorithm Hash digest
SHA256 0167fff0fa1a80e3de5ef227ab09f91541cd8e05a65c1081aed02c621d7b866a
MD5 1fed51e64694473c179567362662daf0
BLAKE2b-256 7ab73262b802bd1597c7223bae5aea74a50a80bb2383a28a5e5e74b4c0869052

See more details on using hashes here.

File details

Details for the file airtable_to_sqlite-0.2.1-py3-none-any.whl.

File metadata

File hashes

Hashes for airtable_to_sqlite-0.2.1-py3-none-any.whl
Algorithm Hash digest
SHA256 fcce740568c95606923e76a0294f323fdf112145a3fe5c8a6d3e42bb0b1d8bf9
MD5 6a84232acb0032c82ae703eaa29c4683
BLAKE2b-256 64558fb1d018e693a4bd06820ba2346464a5df9ef42b4a003597dbe793ea5f19

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page