Pysqlitmcp

🚀 PySqlitMCP

PySqlitMCP is a database operation toolset based on the FastMCP framework and the PySqlit library. It offers comprehensive SQLite database management capabilities, including database creation, table operations, data insertion, deletion, modification, and querying, backup and restoration, as well as data import and export.

✨ Features

Database Management

• Create a new database.
• Retrieve detailed database information.
• Backup the database to a specified file.
• Restore the database from a backup file.
• List all backup files.

Table Operations

• Create a new data table.
• Delete a specified data table.
• Obtain detailed information about a specified table.
• Count the total number of records in a table.
• List all tables in the database.

Data Operations

• Insert a single piece of data into a table.
• Insert multiple pieces of data into a table in batches.
• Query data in a table.
• Update data in a table.
• Delete data from a table.
• Execute custom SQL statements.

Data Import and Export

• Import data from a CSV file into a table.
• Export table data to a CSV file.

📦 Installation

Install with pip

uv pip install -r requirements.txt

Install with uv (Recommended)

uv sync

🚀 Quick Start

1. Ensure all dependencies are installed.
2. Run the main program:

   python main.py
   

📚 Documentation

1. Obtain database information to understand its structure before operations.
2. Check if a table exists before performing table operations.
3. Pay attention to data type matching during data operations.
4. Use batch operations for large amounts of data.
5. Backup the database before important operations.
6. Exercise caution when executing custom SQL statements to avoid incorrect operations. It is recommended to use the encapsulated methods.
7. You can specify the save path when creating a database.
8. You can specify the file path when importing or exporting data.
9. Always check the data format and quantity during batch operations to avoid incorrect operations.
10. If there are errors in the provided parameters or formats, error messages should be automatically prompted, and the correct usage should be provided.
11. Do not perform unauthorized operations on database tables.
12. Dynamically use prompt words to give prompts based on the passed parameters.

📄 API Documentation

Database Management Interfaces

create_database(db_name="test", db_path=None)

Create a new database.

Parameters:

• db_name (str, optional): The name of the database to be created, defaulting to "test".
• db_path (str, optional): The full path to save the database file. If not provided, the current working directory is used.

Returns:

• The full path of the database file.

get_database_info()

Retrieve detailed database information.

Returns:

• A dictionary containing detailed database information, including:
  • path: The path of the database file.
  • tables: A list of tables and their structural information.
  • table_count: The number of tables.

backup_database(backup_file)

Backup the database to a specified file.

Parameters:

• backup_file (str): The path of the backup file.

Returns:

• True if the backup is successful, False otherwise.

restore_database(backup_file)

Restore the database from a backup file.

Parameters:

• backup_file (str): The path of the backup file.

Returns:

• True if the restoration is successful, False otherwise.

list_backup_files()

List all backup files.

Returns:

• A list of backup file information, with each element containing the file name and creation time.

Table Operation Interfaces

create_tables(table_name, columnss, pk=None, unique=None, check=None)

Create a new data table.

Parameters:

• table_name (str): The name of the table to be created.
• columnss (list): A list of column definitions.
• pk (str/list, optional): The name or names of the primary key columns.
• unique (list, optional): A list of column names for unique constraints.
• check (str, optional): The check constraint condition.

Returns:

• True if the table is created successfully, False otherwise.

drop_tables(table_name)

Delete a specified data table.

Parameters:

• table_name (str): The name of the table to be deleted.

Returns:

• True if the table is deleted successfully, False otherwise.

get_table_info(table_name)

Obtain detailed information about a specified table.

Parameters:

• table_name (str): The name of the table to be queried.

Returns:

• A dictionary containing the table structure.

list_tables()

List all tables in the database.

Returns:

• A list of table names.

get_table_count(table_name)

Count the total number of records in a table.

Parameters:

• table_name (str): The name of the table to be counted.

Returns:

• The total number of records in the table.

Data Operation Interfaces

insert_data(table_name, data)

Insert a single piece of data into a table.

Parameters:

• table_name (str): The name of the target table.
• data (dict): The data to be inserted.

Returns:

• The ID of the newly inserted record if successful, -1 otherwise.

insert_multiple_data(table_name, datas)

Insert multiple pieces of data into a table in batches.

Parameters:

• table_name (str): The name of the target table.
• datas (list): A list of data to be inserted.

Returns:

• The number of successfully inserted records.

select_data(table_name, condition=None)

Query data in a table.

Parameters:

• table_name (str): The name of the table to be queried.
• condition (str, optional): The WHERE condition statement.

Returns:

• A list of query results.

update_data(table_name, data, condition)

Update data in a table.

Parameters:

• table_name (str): The name of the target table.
• data (dict): The data to be updated.
• condition (str): The WHERE condition statement.

Returns:

• The number of successfully updated records.

delete_data(table_name, condition)

Delete data from a table.

Parameters:

• table_name (str): The name of the target table.
• condition (str): The WHERE condition statement.

Returns:

• The number of successfully deleted records.

execute_sql_statement(sql)

Execute a custom SQL statement.

Parameters:

• sql (str): The SQL statement to be executed.

Returns:

• A list of query results (for SELECT statements) or the number of affected rows (for INSERT/UPDATE/DELETE statements).

Data Import and Export Interfaces

import_from_csv(table_name, import_file)

Import data from a CSV file into a table.

Parameters:

• table_name (str): The name of the target table.
• import_file (str): The path of the CSV file to be imported.

Returns:

• The number of successfully imported records.

export_to_csv(table_name, export_file)

Export table data to a CSV file.

Parameters:

• table_name (str): The name of the table to be exported.
• export_file (str): The path of the exported CSV file.

Returns:

• True if the export is successful, False otherwise.

🔧 Technical Details

• Python >= 3.11
• FastMCP >= 2.12.2
• PySqlit >= 0.2.2

📄 License

This project is licensed under the MIT License. For details, please refer to the LICENSE file.