Export Airtable bases to an sqlite database
Project description
Airtable to SQlite
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 tableotherRecordId
: 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 IDname
: (str) Table nameprimaryFieldId
: (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 IDname
: (str) Field Nametype
: (str) Type of fieldtableId
: (str) Airtable Table IDoptions
: (json) Any remaining options not covered by other variableslinkedTableId
: (str) For fields of typemultipleRecordLinks
, the other table looked upisReversed
: (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 IDname
: (str) Choice Namecolor
: (str)fieldId
: (str) Airtable Field ID
_meta_view
A record for each view in the Base. Fields are:
id
: (str) Airtable View IDname
: (str) View nametableId
: (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
Built Distribution
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
Algorithm | Hash digest | |
---|---|---|
SHA256 | 0167fff0fa1a80e3de5ef227ab09f91541cd8e05a65c1081aed02c621d7b866a |
|
MD5 | 1fed51e64694473c179567362662daf0 |
|
BLAKE2b-256 | 7ab73262b802bd1597c7223bae5aea74a50a80bb2383a28a5e5e74b4c0869052 |
File details
Details for the file airtable_to_sqlite-0.2.1-py3-none-any.whl
.
File metadata
- Download URL: airtable_to_sqlite-0.2.1-py3-none-any.whl
- Upload date:
- Size: 10.8 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via: python-httpx/0.25.1
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | fcce740568c95606923e76a0294f323fdf112145a3fe5c8a6d3e42bb0b1d8bf9 |
|
MD5 | 6a84232acb0032c82ae703eaa29c4683 |
|
BLAKE2b-256 | 64558fb1d018e693a4bd06820ba2346464a5df9ef42b4a003597dbe793ea5f19 |