DataFrames, Arrow and columns

This page covers column-shaped data in and out of a database. Load it with insert_dataframe (a pandas or Polars frame), insert_arrow (an Arrow table) or insert_columns (numpy arrays and lists). Read a result as columns with to_columns, to_arrow, to_dataframe or to_polars. For a list of Python dicts use insert_many, and for a file use the importer: Bulk loads and commit size and Importing and exporting data. The calls here are bindings calls and have no statement in SQL, so the main manual has nothing to say about them. The types that a stored value can have are the engine’s: Data types.

The libraries are optional extras. A pandas frame needs pandas and pyarrow, a Polars frame needs Polars, and the readers need numpy, pyarrow, pandas or Polars as their names say:

uv add 'arcadedb[arrow,pandas]'
uv add 'arcadedb[polars]'

arcadedb-jvm has the same extras: uv add 'arcadedb-jvm[arrow,pandas]'.

The blocks on this page run one after the other against one empty database, and a test in the repository runs them that way on both packages (tests/test_docs_data_exchange_pages.py). A block that prints something is followed by its output. Every block assumes that db is an open database.

import datetime as dt

import numpy as np
import pandas as pd
import polars as pl

from arcadedb import ArcadeDBError


def rows(sql):
    return db.query("sql", sql).to_list()

Choose a call

Your data is Load it with Notes

A pandas or Polars DataFrame

db.insert_dataframe(type_name, frame)

Checks every column against the declared properties before it writes a row.

A pyarrow Table, RecordBatch or RecordBatchReader

db.insert_arrow(type_name, table)

The same rules as a frame. Any object that speaks the Arrow PyCapsule interface also loads.

A dict of numpy arrays or lists, or a pandas frame

db.insert_columns(type_name, columns)

No check against declared properties: the engine converts the values.

A list of dicts

db.insert_many(type_name, rows)

Bulk loads and commit size.

insert_dataframe and insert_arrow load a document type or a vertex type and refuse an edge type, which is loaded with graph_batch().new_edges(). All three return the number of records written. A frame with no rows writes nothing and returns 0.

Load a frame

A column becomes a property of the same name. The pandas index is not loaded: call frame.reset_index() to make it a column. Column names must be strings and must be different from each other, and a Polars LazyFrame must be collected first. The type must exist. It can declare its properties or not.

A missing value is a property that is not there. A pandas NaN in a float column is pandas' missing value, so it loads as missing. A Polars NaN is a number that Polars keeps apart from null, so it loads as a NaN, and only a Polars null loads as missing.

db.command("sql", "CREATE DOCUMENT TYPE Trip")
frame = pd.DataFrame(
    {
        "id": np.array([1, 2, 3], dtype=np.int64),
        "fare": [12.5, float("nan"), 7.25],
        "city": ["Oslo", "Rome", None],
        "at": pd.to_datetime(
            ["2026-01-02 03:04:05", "2026-01-03 00:00:00", "2026-01-04 12:00:00"]
        ),
    }
)
print(db.insert_dataframe("Trip", frame))
for row in rows("SELECT FROM Trip ORDER BY id"):
    print(row["id"], row.get("fare"), row.get("city"), row["at"])

db.command("sql", "CREATE DOCUMENT TYPE Reading")
db.insert_dataframe(
    "Reading", pl.DataFrame({"k": [0, 1, 2], "x": [1.5, float("nan"), None]})
)
for row in rows("SELECT FROM Reading ORDER BY k"):
    print(row["k"], row.get("x"))
3
1 12.5 Oslo 2026-01-02 03:04:05
2 None Rome 2026-01-03 00:00:00
3 7.25 None 2026-01-04 12:00:00
0 1.5
1 nan
2 None

What an Arrow type becomes

A frame is read as an Arrow table first, so the table below holds for a frame and for insert_arrow. It is the mapping for a property that the type does not declare. "Read back as" is the Python type that Result.get returns. A date-time is a UTC wall clock: a timestamp without a time zone is stored as the clock shows, and a timestamp with a time zone is converted to UTC first. Reading it back gives a naive datetime. The same rule for Python datetime values is on Values and records.

Arrow type Read back as

int8 to int64, uint8 to uint32

int

float16, float32, float64

float

bool

bool

string, large_string

str

timestamp, with or without a time zone, any unit

datetime

date32, date64

date

decimal128, decimal256

Decimal, with every digit

list, large_list, fixed_size_list

list

struct

dict

dictionary (a pandas categorical, a Polars Categorical)

the type of its values

a column of only nulls

no property

An Arrow type with no mapping, which is time of day, duration or map, raises TypeError before a row is written. A uint64 column with a value above 263 - 1 raises ValueError.

Load into a declared property

When the type declares a property, the column is checked against it before anything is written. A column loads when the engine can convert it without guessing:

  • Integers load into BYTE, SHORT, INTEGER, LONG, FLOAT, DOUBLE, DECIMAL and STRING.

  • Floats load into FLOAT, DOUBLE, DECIMAL and STRING, and never into an integer property, because that would cut the fraction.

  • Booleans load into BOOLEAN and STRING, strings into STRING and LINK, timestamps into any DATETIME precision and STRING, and dates into DATE, any DATETIME precision and STRING.

Any other pair raises TypeError that names the column, the Arrow type and the declared type, and nothing is written. A declared property of a parent type counts. A value that the declared type cannot hold, such as an integer beyond the range of INTEGER, passes this check and fails when it is written: the load raises ArcadeDBError and the batch is rolled back. A column that the type does not declare is stored as its own type.

db.command("sql", "CREATE DOCUMENT TYPE Item")
db.command("sql", "CREATE PROPERTY Item.qty INTEGER")
db.command("sql", "CREATE PROPERTY Item.label STRING")
try:
    db.insert_dataframe("Item", pd.DataFrame({"qty": [1.5, 2.5]}))
except TypeError as error:
    print("refused:", "is declared INTEGER" in str(error))
print(rows("SELECT count(*) AS n FROM Item")[0]["n"])

fixed = pd.DataFrame({"qty": [1.5, 2.5]}).astype("int64")
print(db.insert_dataframe("Item", fixed))
print(db.insert_dataframe("Item", pd.DataFrame({"qty": [3], "label": [7]})))
print([r["label"] for r in rows("SELECT label FROM Item WHERE label IS NOT NULL")])
refused: True
0
2
1
['7']

Batches, memory and a failed load

The frame becomes one Arrow table first. After that only one batch of batch_size rows (10,000 by default) is converted to columns at a time, written, and committed. A table that already comes in chunks keeps its chunks, and a chunk larger than batch_size is split. So the frame and its Arrow table are in memory together while the rows are written, one batch at a time. For a source larger than memory, read it in pieces and call the load once for each piece.

A failure rolls back the batch in flight and leaves the batches before it committed. The message says how many rows that is.

db.command("sql", "CREATE DOCUMENT TYPE Dup")
db.command("sql", "CREATE PROPERTY Dup.id LONG")
db.command("sql", "CREATE INDEX ON Dup (id) UNIQUE")
repeated = pd.DataFrame({"id": list(range(10)) + [3]})
try:
    db.insert_dataframe("Dup", repeated, batch_size=10)
except ArcadeDBError as error:
    print(str(error).endswith("were committed"))
print(rows("SELECT count(*) AS n FROM Dup")[0]["n"])
True
10

A load inside a transaction of yours joins it, as insert_columns and import_documents do: the batches are written and nothing is committed, and the message of a failure says that the earlier rows were written in your transaction, not committed. Your rollback() removes them. A vertex type is the exception: it loads through a graph batch, which refuses to run inside your transaction (GraphBatch inside a transaction).

parallel=True hands the batches of a document type to the asynchronous writers. A vertex type raises ValueError with it.

Load whole columns

db.insert_columns(type_name, columns, commit_every=10_000, parallel=False) takes a dict of property name to column. A column is a numpy array or any sequence of Python values. All columns have the same length. None in a sequence is a missing value, and a NaN in a numpy float column is stored as a NaN, not as missing. A 2-D float32 array is a column of vectors: each row of it becomes the ARRAY_OF_FLOATS property of one record.

db.command("sql", "CREATE DOCUMENT TYPE Col")
db.command("sql", "CREATE PROPERTY Col.qty INTEGER")
written = db.insert_columns(
    "Col",
    {
        "id": np.arange(3),
        "qty": np.array([10, 20, 30], dtype=np.int32),
        "label": ["a", None, "c"],
        "score": np.array([0.5, float("nan"), 2.5]),
    },
)
print(written)
for row in rows("SELECT FROM Col ORDER BY id"):
    print(row["id"], row["qty"], row.get("label"), row["score"])
3
0 10 a 0.5
1 20 None nan
2 30 c 2.5

Bad input is refused before anything is written: columns of different lengths, no column, a name that is not a string, a column that is one str or bytes value (which would otherwise become one row for each character), a column that is not one-dimensional (a 2-D float32 array excepted), and a uint64 value above 263 - 1.

A numpy array of dates, datetime64, raises TypeError that points to insert_many, on both packages. A list of Python datetime objects loads, and so does a datetime64 column in a frame, through insert_dataframe, which reads it as an Arrow timestamp.

try:
    db.insert_columns(
        "Col",
        {
            "id": np.arange(2),
            "when": np.array(["2026-01-01", "2026-01-02"], dtype="datetime64[D]"),
        },
    )
except TypeError as error:
    print("refused:", "insert_many" in str(error))
stamps = [dt.datetime(2026, 1, 2, 3, 4, 5), dt.datetime(2026, 1, 3)]
print(db.insert_columns("Col", {"id": np.array([10, 11]), "when": stamps}))
refused: True
2

insert_columns does not check a column against a declared property the way insert_dataframe does. The values go to the engine, which converts them: a float column into an INTEGER property is stored without its fraction, so 1.5 becomes 1. Use insert_dataframe when you want that refused.

commit_every is the transaction size. Its default is 10,000 rows on both packages. The native default of 1000 on Bulk loads and commit size is the default of insert_many when you do not pass a value, and insert_columns and insert_dataframe always pass their own value to it (10,000 for commit_every and for batch_size), so a native insert_columns load commits in groups of 10,000 unless you say otherwise. A failure rolls back the transaction in flight and keeps the ones before it: 30 rows with commit_every=10 and a duplicate key in the third group leave 20 rows. A transaction of yours is not committed, as above.

Reading columns

The four readers take the whole result, so they hold every row in memory. For a result that is larger than memory read the rows one by one (Databases and queries) or write it with export_to_csv. A result set is read once (A result set is read once), so call one reader for each query.

The next block stores one record with every value and one record with none, then reads them with each reader. The records are the same for all of them, and the readers differ in what they do with the gap:

  • to_columns() returns a dict of numpy arrays, and a list for text. It follows pandas: an integer column with a gap becomes float64 with NaN, a boolean column with a gap becomes a list with None, and a text column uses None.

  • to_arrow() returns a pyarrow.Table that keeps the type: an integer column with a gap stays int64, with a null. That is the reader to use when the value must survive exactly, such as an id above 253.

  • to_polars() goes through Arrow, so it keeps the type too.

  • to_dataframe() returns a pandas frame with the pandas types, NaN for a gap in a number and NaT in a date-time.

db.command("sql", "CREATE DOCUMENT TYPE Rec")
with db.transaction():
    db.command("sql", "INSERT INTO Rec SET k = 'a', n = 7, ok = true, s = 'x'")
    db.command("sql", "INSERT INTO Rec SET k = 'b'")
query = "SELECT k, n, ok, s FROM Rec ORDER BY k"

columns = db.query("sql", query).to_columns()
print(columns["n"].dtype, columns["n"].tolist())
print(columns["ok"], columns["s"])

table = db.query("sql", query).to_arrow()
print(table.schema.field("n").type, table.column("n").to_pylist())

polars_frame = db.query("sql", query).to_polars()
print(polars_frame["n"].dtype, polars_frame["n"].to_list())

pandas_frame = db.query("sql", query).to_dataframe()
print(pandas_frame["n"].dtype)
float64 [7.0, nan]
[True, None] ['x', None]
int64 [7, None]
Int64 [7, None]
float64

A column without a gap is one numpy array of one type, such as int64 for integers and float64 for floats. The columns of the result are the names of all rows, because a document has no fixed set of properties. columns=["n", "other"] reads exactly those names, and a row that lacks one reads as missing. An empty result gives {} from to_columns(). The result does not depend on batch_size.

Dates differ by reader. to_columns() and to_arrow() carry milliseconds: datetime64[ms] and timestamp[ms], and a DATE is its midnight. to_dataframe() keeps the microseconds that Result.get returns.

db.command("sql", "CREATE DOCUMENT TYPE Stamp")
db.insert_many("Stamp", [{"t": dt.datetime(2026, 10, 3, 12, 34, 56, 123456)}])
stamp_query = "SELECT t FROM Stamp"
print(
    db.query("sql", stamp_query).to_columns()["t"].dtype,
    db.query("sql", stamp_query).to_arrow().schema.field("t").type,
)
print(db.query("sql", stamp_query).to_dataframe()["t"].iloc[0].to_pydatetime())
datetime64[ms] timestamp[ms]
2026-10-03 12:34:56.123456

A missing library does not fail the same way everywhere. to_columns() returns None when numpy is missing and to_arrow() returns None when pyarrow is missing, so that a caller can fall back to another reader. to_polars() and to_dataframe() raise ImportError. Check for None before you use the result of the first two.

Differences between the two builds

The calls above, the types and the results are the same on arcadedb and arcadedb-jvm, and so is how to_columns() and to_arrow() get the rows: both read the result from the engine as packed columns, in requests of at most batch_size rows, so batch_size sets how many rows cross at a time. One thing differs, the message of a missing pandas: the JVM package adds the uv add hint to the ImportError of to_dataframe(), and the native package raises the plain ImportError. Another difference is how insert_arrow and insert_dataframe pass a timestamp or date column to the engine, which is described on Bulk loads and commit size. The stored values are the same on both packages.