SoFunction
Updated on 2025-04-07

How to use DeepSeek to get table information (table name and field name) in the database

Problem background

In testing or development, understanding the table structure of a database (including table names and field names) is a very important part, especially when we need to test the database-related functions or verify data. However, manually viewing the database structure can be time-consuming and error-prone. If it can passDeepSeekDirect interaction with the database and automatically obtaining table names and field information will greatly improve the testing efficiency.

This article will introduce how to use itDeepSeekModel combinationDatabase query, automatically generate table structure information (including table name and field name). In addition, it will also show how to automatically generate corresponding SQL queries through natural language descriptions, thereby realizing intelligent exploration of database structure.

Solution

In order to obtain the table information of the database (table name and field name), we can place theDeepSeek ModelandDatabase metadata queryCombined, the following steps are adopted:

  • Generate metadata query statements through natural language：
    Let DeepSeek generate SQL query statements based on the input natural language description, for example:

    • Enter: "Get all table names and field information"
    • Output:SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = 'public';

  • Execute SQL query：

    • Database libraries using Python (such assqlite3、psycopg2、pyodbcetc) Connect to the database.
    • Execute the SQL query generated by DeepSeek to obtain table name and field information.

  • Output table structure information：

    • Organize query results into easy-to-read formats (such as tables or JSON).

  • Generate natural language descriptions：

    • Using the DeepSeek model, table structure information is expressed in natural language, such as "Table users have 3 fields: id, name, email".

Implementation steps

The following are specific implementation plans and code examples.

1. Database metadata query

The metadata of the database is stored in the system table. The following are SQL statements for commonly used database query table information:

1.1 PostgreSQL

-- Get all table names
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public';

-- Get field information for the specified table
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'your_table_name';

1.2 MySQL

-- Get all table names
SHOW TABLES;

-- Get field information for the specified table
DESCRIBE your_table_name;

1.3 SQLite

-- Get all table names
SELECT name 
FROM sqlite_master 
WHERE type='table';

-- Get field information for the specified table
PRAGMA table_info(your_table_name);

2. DeepSeek generates query statement

Generating SQL statements through the DeepSeek model can simplify complex query logic. The following code shows how to call the locally deployed DeepSeek model and generate query statements based on the natural language description:

import requests

# Local DeepSeek service addressDEESEEK_API_URL = "http://localhost:11434/api/generate"

def generate_sql_query(prompt):
    """
     Call DeepSeek model to generate SQL query statements
     :param prompt: natural language description
     :return: SQL query statement generated by DeepSeek
     """
    payload = {
        "model": "deepseek-r1:1.5b",
        "prompt": prompt
    }
    headers = {"Content-Type": "application/json"}

    try:
        response = (DEESEEK_API_URL, json=payload, headers=headers)
        response.raise_for_status()
        result = ()
        return ("response", "").strip()
    except  as e:
        print(f"Call DeepSeek API fail: {e}")
        return None

# Example: Generate SQL to query all table namesprompt = "Generate a SQL statement (PostgreSQL) that queryes all table names"
sql_query = generate_sql_query(prompt)
print("The generated SQL query statement:")
print(sql_query)

Sample output：

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

3. Execute SQL query

The following code shows how to connect to a database using Python and execute a DeepSeek-generated SQL query.

3.1 Database connection and query

Taking PostgreSQL as an example, usepsycopg2The library connects to the database and executes the query:

import psycopg2

def execute_query(database_config, query):
    """
     Connect to the PostgreSQL database and execute queries
     :param database_config: database configuration dictionary (including host, dbname, user, password)
     :param query: SQL query statement
     :return: query result (list form)
     """
    try:
        # Create a database connection        conn = (
            host=database_config["host"],
            dbname=database_config["dbname"],
            user=database_config["user"],
            password=database_config["password"]
        )
        cursor = ()

        # Execute query        (query)
        results = ()

        # Close the connection        ()
        ()

        return results
    except Exception as e:
        print(f"Database query failed：{e}")
        return None

# Example: Query all table namesdatabase_config = {
    "host": "localhost",
    "dbname": "test_db",
    "user": "postgres",
    "password": "password"
}
query = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';"
tables = execute_query(database_config, query)
print("Table name in the database:")
print(tables)

4. Obtain table field information and integrate output

Use the SQL query generated by DeepSeek to obtain field information and organize the output.

def get_table_fields(database_config, table_name):
    """
     Get field information for the specified table
     :param database_config: database configuration dictionary
     :param table_name: table name
     :return: field information (list form)
     """
    query = f"SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '{table_name}';"
    fields = execute_query(database_config, query)
    return fields

# Example: Get the field information of table userstable_name = "users"
fields = get_table_fields(database_config, table_name)
print(f"surface {table_name} Field information：")
for field in fields:
    print(f"- Field name：{field[0]}，Data Type：{field[1]}")

5. Generate natural language descriptions in combination with DeepSeek

Finally, convert table structure information into natural language descriptions for quick understanding.

def describe_table_in_natural_language(table_name, fields):
    """
     Convert table structure information to natural language description
     :param table_name: table name
     :param fields: field information (list form)
     :return: natural language description
     """
    description = f"surface {table_name} have {len(fields)} Fields：\n"
    for field in fields:
        description += f"- {field[0]}（{field[1]}）\n"
    return description

# Example: Generate natural language descriptiondescription = describe_table_in_natural_language(table_name, fields)
print("Natural Language Description:")
print(description)

Sample output：

surface users have 3 Fields：
- id（integer）
- name（text）
- email（text）

Complete process example

  • Enter a natural language description, such as "get all table names in the database".
  • Call DeepSeek to generate SQL query statements.
  • Execute the generated SQL query to get the table name.
  • For each table name, call DeepSeek to generate a field query statement and obtain field information.
  • Organize table structure information into natural language descriptions or JSON output.

Extensibility

  • Supports multiple databases: By adjusting SQL query statements, it supports PostgreSQL, MySQL, SQLite and other databases.
  • Generate with test cases: Automatically generate test cases based on the table structure, such as verification field type, length, etc.
  • Deep integration toolchain: Integrate table structure information with a test framework (such as pytest) to dynamically generate data verification scripts.

Summarize

Deployed locallyDeepSeek-r1:1.5b model, combined with database query, the following functions can be realized:

  • Quickly generate SQL queries: Reduce the cost of writing SQL manually.
  • Automatically obtain table structure information: Efficiently obtain table name and field information.
  • Natural language description table structure: Easy to understand and communicate.

This intelligent table structure exploration method not only improves testing and development efficiency, but also provides a solid foundation for dynamic test case generation and data verification. If your testing work involves database operations, you might as well try this solution and feel the charm of the revolution in efficiency!

This is the end of this article about using DeepSeek to obtain table information in the database. For more related DeepSeek to obtain table information in the database, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!