Feature: db.insert function

[MatthewAry]

Is your feature request related to a problem?

There is no easy way to insert multiple records. This can made easier by creating a new method specifically for performing insert operations.

Describe the solution

A new method could be added to the SDK which could have the following signature:

db.insert(table: str, data: Union[Dict, List[Dict]], options: Optional[?])

This would allow you to insert a singular record or a list of records.

Alternative methods

Currently the method that we are using is to either generate a prepared string to do an insert statement:

db.query(```
INSERT INTO company {
    name: 'SurrealDB',
    founded: "2021-09-10",
    founders: [person:tobie, person:jaime],
    tags: ['big data', 'database']
};```)

or

db.query(```
INSERT INTO person [
   {id: "person:jaime", name: "Jaime", surname: "Morgan Hitchcock"},
   {id: "person:tobie", name: "Tobie", surname: "Morgan Hitchcock"},
];```)

SurrealDB version

1.0.0+20230913.54aedcd for macos on x86_64

surrealdb.py version

surrealdb.py 0.3.1 for macOS on x86_64 using Python 3.11.2

Contact Details

No response

Is there an existing issue for this?

• [X] I have searched the existing issues

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[maxwellflitton]

Hey @MatthewAry thank you for the suggestion. We are working on the rust/python client and we also want to ensure that the features across all clients are going to be the same. This does not mean that a batch insert is a bad idea, we will put it to the team and work the feasibility of supporting the batch inserts in general. However, I appreciate that this is not helpful to you right as this time, so below is a function that will generate the SQL statement for a batch insert based off a list of dicts:

def construct_batch_insert(table_name, records):
    """
    Construct a batch insert SQL query for a custom SQL syntax as provided in the example.

    Args:
    table_name (str): The name of the table into which records will be inserted.
    records (list of dict): A list of dictionaries, each representing a row to insert.

    Returns:
    str: A SQL query string for batch insertion.
    """
    # Start the query with the table name
    query = f"INSERT INTO {table_name} [\n"

    # Add each record as a dictionary string
    record_strings = []
    for record in records:
        # Convert each record dictionary to a string in the format: {key: "value", key: "value"}
        record_str = ', '.join([f'{key}: "{value}"' for key, value in record.items()])
        record_strings.append(f"   {{{record_str}}}")

    # Join all record strings with a comma and a newline
    query += ',\n'.join(record_strings)

    # Close the bracket for the record list and end the query with a semicolon
    query += "\n];"

    return query

# Example usage
records = [
    {"id": "person:jaime", "name": "Jaime", "surname": "Morgan Hitchcock"},
    {"id": "person:tobie", "name": "Tobie", "surname": "Morgan Hitchcock"}
]

# Generate the query
query = construct_batch_insert("person", records)
print(query)

The code will give the following printout:

INSERT INTO person [
   {id: "person:jaime", name: "Jaime", surname: "Morgan Hitchcock"},
   {id: "person:tobie", name: "Tobie", surname: "Morgan Hitchcock"}
];

This means that your batch insert can be the following:

db.insert(construct_batch_insert("person", [
    {"id": "person:jaime", "name": "Jaime", "surname": "Morgan Hitchcock"},
    {"id": "person:tobie", "name": "Tobie", "surname": "Morgan Hitchcock"}
]))

Does this approach suit you for now while we work on incorporating batch inserts?

[MatthewAry]

This is great. Thank you for making this. I know that is not a native solution and that developing one is not feasible right now but this does meet my needs for now!