Feature: db.insert function
MatthewAry opened this issue · 2 comments
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?
- I have searched the existing issues
Code of Conduct
- I agree to follow this project's Code of Conduct
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?
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!