mirror of
https://gitcode.com/huyuchengus/gitcode_knowledge.git
synced 2025-08-11 18:33:42 +00:00
1 line
14 KiB
Markdown
1 line
14 KiB
Markdown
|
<p>想做一个简单的数据库表的统计工具。</p> <br><p>用 AI，感觉整个人脑子都不好用了。</p> <br><p></p> <br><pre><code>import pyodbc<br>import pandas as pd<br>import matplotlib.pyplot as plt<br>import seaborn as sns<br>from datetime import datetime<br>import warnings<br>warnings.filterwarnings('ignore')<br><br>class MSSQLTableAnalyzer:<br> def __init__(self, server, database, username=None, password=None, trusted_connection=True):<br> """<br> Initialize MSSQL connection<br> <br> Args:<br> server: SQL Server name or IP<br> database: Database name<br> username: Username (optional if using Windows Authentication)<br> password: Password (optional if using Windows Authentication)<br> trusted_connection: Use Windows Authentication (default: True)<br> """<br> self.server = server<br> self.database = database<br> self.username = username<br> self.password = password<br> self.trusted_connection = trusted_connection<br> self.connection = None<br> <br> def connect(self):<br> """Establish connection to MSSQL database"""<br> try:<br> if self.trusted_connection:<br> # Windows Authentication<br> connection_string = f"""<br> DRIVER={<!-- -->{ODBC Driver 17 for SQL Server}};<br> SERVER={self.server};<br> DATABASE={self.database};<br> Trusted_Connection=yes;<br> """<br> else:<br> # SQL Server Authentication<br> connection_string = f"""<br> DRIVER={<!-- -->{ODBC Driver 17 for SQL Server}};<br> SERVER={self.server};<br> DATABASE={self.database};<br> UID={self.username};<br> PWD={self.password};<br> """<br> <br> self.connection = pyodbc.connect(connection_string)<br> print(f"✅ Successfully connected to {self.database} on {self.server}")<br> return True<br> <br> except Exception as e:<br> print(f"❌ Connection failed: {str(e)}")<br> return False<br> <br> def get_table_counts(self):<br> """Query all tables and their row counts"""<br> if not self.connection:<br> print("❌ No database connection established")<br> return None<br> <br> try:<br> # Query to get all user tables and their row counts<br> query = """<br> SELECT <br> t.TABLE_SCHEMA as [Schema],<br> t.TABLE_NAME as [Table_Name],<br> p.rows as [Row_Count]<br> FROM <br> INFORMATION_SCHEMA.TABLES t<br> INNER JOIN <br> sys.tables st ON st.name = t.TABLE_NAME<br> INNER JOIN <br> sys.partitions p ON st.object_id = p.object_id<br> WHERE <br> t.TABLE_TYPE = 'BASE TABLE'<br> AND p.index_id < 2<br> ORDER BY <br> p.rows DESC, t.TABLE_SCHEMA, t.TABLE_NAME<br> """<br> <br> df = pd.read_sql_query(query, self.connection)<br> print(f"📊 Found {len(df)} tables in database")<br> return df<br> <br> except Exception as e:<br> print(f"❌ Query failed: {str(e)}")<br> return None<br> <br> def create_bar_chart(self, df, max_tables=20, chart_type='horizontal'):<br> """<br> Create bar chart for table row counts<br> <br> Args:<br> df: DataFrame with table information<br> max_tables: Maximum number of tables to display<br> chart_type: 'horizontal' or 'vertical'<br> """<br> if df is None or df.empty:<br> print("❌ No data to display")<br> return<br> <br> # Prepare data for visualization<br> df_display = df.head(m
|