# Python 查询 MSSQL 数据库表数量更新 Bar chart 英文版

**URL:** <https://www.isharkfly.com/t/python-mssql-bar-chart/17428>\
**Category:** 程序语言\
**Tags:** python, ai\
**Created:** [June 12, 2025, 12:26pm UTC](https://www.isharkfly.com/t/python-mssql-bar-chart/17428 "2025-06-12T12:26:11Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![honeymoose](https://www.isharkfly.com/user_avatar/www.isharkfly.com/honeymoose/32/20796_2.png) [@honeymoose](https://www.isharkfly.com/u/honeymoose)\
**Post date:** [June 12, 2025, 12:26pm UTC](https://www.isharkfly.com/t/python-mssql-bar-chart/17428/1 "2025-06-12T12:26:11Z")

</div>

python 查询 MSSQL 数据库表数量更新 Bar chart 英文版

---

<div class="post-metadata">

**Author:** ![hex](https://www.isharkfly.com/user_avatar/www.isharkfly.com/hex/32/21953_2.png) [@hex](https://www.isharkfly.com/u/hex)\
**Post date:** [June 12, 2025, 12:26pm UTC](https://www.isharkfly.com/t/python-mssql-bar-chart/17428/2 "2025-06-12T12:26:11Z")

</div>

Okay, “honeymoose”! Here’s a Python script that connects to an MSSQL database, queries the row counts for each user table, and then displays this information in a bar chart using `matplotlib`.

This English version will include:

1. **Prerequisites** : What you need to install.
2. **Configuration** : How to set up your database connection.
3. **The Python Script** : Code to perform the query and generate the chart.
4. **Explanation** : A breakdown of what the script does.

* * *

**1. Prerequisites**

You’ll need to install a few Python libraries:

- `pyodbc`: For connecting to SQL Server.
- `matplotlib`: For creating the bar chart.

You can install them using pip:

```bash
pip install pyodbc matplotlib

```

You also need an **ODBC driver for SQL Server** installed on your system. If you’re on Windows, it’s often pre-installed. If not, or if you’re on Linux/macOS, you might need to install it. For example, “ODBC Driver 17 for SQL Server” is a common one.

* * *

**2. Configuration**

In the Python script below, you’ll need to update the `CONNECTION_DETAILS` dictionary with your MSSQL server information.

* * *

**3. The Python Script (`mssql_table_counts_chart.py`)**

```python
import pyodbc
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker # For formatting y-axis

# --- CONFIGURATION ---
# Replace with your actual MSSQL server details
# Option 1: SQL Server Authentication
SERVER_NAME = 'YOUR_SERVER_NAME_OR_IP' # e.g., 'localhost\SQLEXPRESS' or '192.168.1.100'
DATABASE_NAME = 'YOUR_DATABASE_NAME'
USERNAME = 'YOUR_USERNAME'
PASSWORD = 'YOUR_PASSWORD'
DRIVER = '{ODBC Driver 17 for SQL Server}' # Or another appropriate driver like '{SQL Server}'

# Construct connection string for SQL Server Authentication
# conn_str = (
# f'DRIVER={DRIVER};'
# f'SERVER={SERVER_NAME};'
# f'DATABASE={DATABASE_NAME};'
# f'UID={USERNAME};'
# f'PWD={PASSWORD};'
# f'TrustServerCertificate=yes;' # Add if using self-signed cert or for some local instances
# )

# Option 2: Windows Authentication (Trusted Connection)
# If using Windows Authentication, UID and PWD are not needed.
# Ensure your current Windows user has access to the SQL Server database.
conn_str = (
    f'DRIVER={DRIVER};'
    f'SERVER={SERVER_NAME};'
    f'DATABASE={DATABASE_NAME};'
    f'Trusted_Connection=yes;'
)
# --- END CONFIGURATION ---

def get_table_row_counts(connection_string):
    """
    Connects to MSSQL, fetches all user table names, and their row counts.
    Returns a dictionary of { 'schema.table_name': row_count }.
    """
    table_counts = {}
    conn = None
    try:
        conn = pyodbc.connect(connection_string)
        cursor = conn.cursor()

        # Query to get all user table names (schema and name)
        # We filter out system tables by checking schema_id < 256 for user schemas
        # and type_desc = 'USER_TABLE'
        query_tables = """
            SELECT s.name AS schema_name, t.name AS table_name
            FROM sys.tables t
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
            WHERE t.type_desc = 'USER_TABLE' AND t.is_ms_shipped = 0
            ORDER BY s.name, t.name;
        """
        cursor.execute(query_tables)
        tables = cursor.fetchall()

        if not tables:
            print(f"No user tables found in database '{DATABASE_NAME}'.")
            return None

        print(f"Found {len(tables)} user tables. Fetching row counts...")

        for schema_name, table_name in tables:
            full_table_name_display = f"{schema_name}.{table_name}"
            # Properly quote identifiers for the count query
            full_table_name_query = f"[{schema_name}].[{table_name}]"
            
            query_count = f"SELECT COUNT_BIG(*) FROM {full_table_name_query};"
            try:
                cursor.execute(query_count)
                row_count = cursor.fetchone()[0]
                table_counts[full_table_name_display] = row_count
                print(f" - {full_table_name_display}: {row_count} rows")
            except pyodbc.Error as count_ex:
                print(f"Error counting rows for table {full_table_name_display}: {count_ex}")
                table_counts[full_table_name_display] = -1 # Indicate error

        return table_counts

    except pyodbc.Error as ex:
        sqlstate = ex.args[0]
        print(f"Database connection or query error: {sqlstate}")
        print(ex)
        return None
    finally:
        if conn:
            conn.close()

def create_bar_chart(table_counts_dict, db_name):
    """
    Creates and displays a bar chart of table row counts.
    """
    if not table_counts_dict:
        print("No data to plot.")
        return

    # Sort tables by name for consistent plotting (optional, could also sort by count)
    # sorted_items = sorted(table_counts_dict.items(), key=lambda item: item[0]) 
    # Or sort by count (descending)
    sorted_items = sorted(table_counts_dict.items(), key=lambda item: item[1], reverse=True)
    
    table_names = [item[0] for item in sorted_items]
    row_counts = [item[1] for item in sorted_items]

    plt.figure(figsize=(12, max(8, len(table_names) * 0.5))) # Adjust height based on number of tables

    bars = plt.bar(table_names, row_counts, color='skyblue')

    plt.xlabel("Table Name (Schema.Table)")
    plt.ylabel("Number of Rows")
    plt.title(f"Row Counts per Table in '{db_name}' Database")
    plt.xticks(rotation=45, ha="right", fontsize=9) # Rotate x-axis labels for better readability
    plt.yticks(fontsize=9)

    # Format y-axis to use commas for thousands
    ax = plt.gca()
    ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, p: format(int(x), ',')))

    # Add text labels on top of bars
    for bar in bars:
        yval = bar.get_height()
        if yval >= 0 : # Don't label error bars (-1)
            plt.text(bar.get_x() + bar.get_width()/2.0, yval + (0.01 * max(row_counts) if max(row_counts) > 0 else 1), 
                     f'{int(yval):,}', ha='center', va='bottom', fontsize=8)

    plt.grid(axis='y', linestyle='--', alpha=0.7)
    plt.tight_layout() # Adjust plot to ensure everything fits without overlapping
    
    # Save the chart to a file (optional)
    chart_filename = f"{db_name}_table_counts.png"
    plt.savefig(chart_filename)
    print(f"\nChart saved as {chart_filename}")

    plt.show() # Display the chart

if __name__ == " __main__":
    print("Attempting to connect to MSSQL and fetch table counts...")
    counts = get_table_row_counts(conn_str)

    if counts:
        print("\nGenerating bar chart...")
        create_bar_chart(counts, DATABASE_NAME)
    else:
        print("Failed to retrieve table counts or no tables found.")

```

* * *

**4. Explanation**

1. **Import Libraries** :

2. **Configuration (`SERVER_NAME`, `DATABASE_NAME`, etc.)**:

3. **`get_table_row_counts(connection_string)` function**:

4. **`create_bar_chart(table_counts_dict, db_name)` function**:

5. **`if __name__ == " __main__":` block** :

* * *

**How to Use “Update”** :

The term “update bar chart” in this context means you re-run the script. Each time you run it, it will:

1. Connect to the database.
2. Query the _current_ row counts for all tables.
3. Generate a _new_ bar chart based on this fresh data.

If you need a chart that updates live without re-running the script (e.g., in a dashboard), that would require a more complex setup, potentially involving a web framework (like Flask or Django) and JavaScript charting libraries, or a GUI application framework (like Tkinter, PyQt). This script provides a simple way to get a snapshot at any given time.

---

<div class="post-metadata">

**Author:** ![honeymoose](https://www.isharkfly.com/user_avatar/www.isharkfly.com/honeymoose/32/20796_2.png) [@honeymoose](https://www.isharkfly.com/u/honeymoose)\
**Post date:** [June 12, 2025, 12:28pm UTC](https://www.isharkfly.com/t/python-mssql-bar-chart/17428/3 "2025-06-12T12:28:21Z")

</div>

Closed on June 12, 2025, 12:28pm UTC



---

<div class="post-metadata">

**Author:** ![honeymoose](https://www.isharkfly.com/user_avatar/www.isharkfly.com/honeymoose/32/20796_2.png) [@honeymoose](https://www.isharkfly.com/u/honeymoose)\
**Post date:** [June 12, 2025, 12:28pm UTC](https://www.isharkfly.com/t/python-mssql-bar-chart/17428/4 "2025-06-12T12:28:34Z")

</div>

Made this topic public on June 12, 2025, 12:28pm UTC



---

<div class="post-metadata">

**Author:** ![honeymoose](https://www.isharkfly.com/user_avatar/www.isharkfly.com/honeymoose/32/20796_2.png) [@honeymoose](https://www.isharkfly.com/u/honeymoose)\
**Post date:** [June 12, 2025, 12:28pm UTC](https://www.isharkfly.com/t/python-mssql-bar-chart/17428/5 "2025-06-12T12:28:59Z")

</div>

Opened on June 12, 2025, 12:28pm UTC


