时间戳

首页 > 解决方案 > 使用 Python 查询不同服务器上的 100 多个 SQL 数据库

python - 使用 Python 查询不同服务器上的 100 多个 SQL 数据库

问题描述

我对 Python 很陌生，我在一个有很多具有相似表结构的数据库的环境中工作。我的目标是通过 Python 建立可以针对大量数据库运行 SQL 脚本的脚本。我现在有一个简单的脚本，但是它不可扩展。是否可以让 pyodbc 从包含我的数据库连接详细信息的 Excel 表中读取，并对所有 100 多个它们运行我的 SQL 查询？

下面是我正在运行的脚本示例。如您所见，我必须为每个数据库创建新的 cursor.execute 行

import csv
import pyodbc

conn1 = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};Server=SERVER;Database=Database;UID=user')
conn2 = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};Server=SERVER;Database=Database;UID=user')
conn3 = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};Server=SERVER;Database=Database;UID=user')

cursor1 = conn1.cursor()
cursor2 = conn2.cursor()
cursor3 = conn3.cursor()

sql = "SELECT * FROM Users"

sql2 = "SELECT * FROM Computers"

sql3 = "SELECT * FROM Offices"


rows = cursor1.execute(sql)
with open(r'C:\Users\xxx\PycharmProjects\RemoteSQLTest\Test\Database1\Database1\users.csv.', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([x[0] for x in cursor1.description])
    for row in rows:
        writer.writerow(row)

rows = cursor1.execute(sql2)
with open(r'C:\Users\xxx\PycharmProjects\RemoteSQLTest\Test\Database1\Database1\Computers.csv.', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([x[0] for x in cursor1.description])
    for row in rows:
        writer.writerow(row)

rows = cursor1.execute(sql3)
with open(r'C:\Users\xxx\PycharmProjects\RemoteSQLTest\Test\Database1\Database1\Offices.csv.', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([x[0] for x in cursor1.description])
    for row in rows:
        writer.writerow(row)

rows = cursor2.execute(sql)
with open(r'C:\Users\xxx\PycharmProjects\RemoteSQLTest\Test\Database2\Database2\users.csv.', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([x[0] for x in cursor2.description])
    for row in rows:
        writer.writerow(row)

rows = cursor2.execute(sql2)
with open(r'C:\Users\xxx\PycharmProjects\RemoteSQLTest\Test\Database2\Database2\Computers.csv.', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([x[0] for x in cursor2.description])
    for row in rows:
        writer.writerow(row)

rows = cursor2.execute(sql3)
with open(r'C:\Users\xxx\PycharmProjects\RemoteSQLTest\Test\Database2\Database2\Offices.csv.', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([x[0] for x in cursor2.description])
    for row in rows:
        writer.writerow(row)

rows = cursor3.execute(sql)
with open(r'C:\Users\xxx\PycharmProjects\RemoteSQLTest\Test\Database3\users.csv.', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([x[0] for x in cursor3.description])
    for row in rows:
        writer.writerow(row)

rows = cursor3.execute(sql2)
with open(r'C:\Users\xxx\PycharmProjects\RemoteSQLTest\Test\Database3\Computers.csv.', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([x[0] for x in cursor3.description])
    for row in rows:
        writer.writerow(row)

rows = cursor3.execute(sql3)
with open(r'C:\Users\xxx\PycharmProjects\RemoteSQLTest\Test\Database3\Offices.csv.', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([x[0] for x in cursor3.description])
    for row in rows:
        writer.writerow(row)

conn1.close()
conn2.close()
conn3.close()

    enter code here

标签： pythonexceldatabasepyodbc

解决方案

您可以使用一些循环和一些字符串格式来避免重复代码。对于具有名为 Sheet1" 的单个工作表的 Excel 文档，其中包含

            A
  +--------------------
1 | server
2 | localhost,49242
3 | 192.168.0.179,49242

这段代码

import pyodbc

tables = ['Users', 'Computers', 'Offices']

cnxn_Excel = pyodbc.connect(
    r'DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};'
    r'DBQ=C:\Users\Gord\Desktop\servers.xlsx;',
    autocommit=True
)
crsr_Excel = cnxn_Excel.execute("SELECT server FROM [Sheet1$]")

cnxn_string_SQL_template = (
    'DRIVER=ODBC Driver 17 for SQL Server;'
    'SERVER={};'
    'DATABASE=myDb;'
    'Trusted_Connection=yes;'
)
for row_Excel in crsr_Excel:
    server_name = row_Excel.server
    print(f'Server: {server_name}')
    cnxn_string_SQL = cnxn_string_SQL_template.format(server_name)
    print(f'   DEBUG_INFO: {cnxn_string_SQL}')
    cnxn_SQL = pyodbc.connect(cnxn_string_SQL)
    for table in tables:
        print(f'   Table: {table}')
        sql = f'SELECT * FROM [{table}]'
        print(f'      DEBUG_INFO: {sql}')
        crsr_SQL = cnxn_SQL.execute(sql)
        #
        # ... code to dump crsr_SQL to csv file goes here
        #
        crsr_SQL.close()
    cnxn_SQL.close()
crsr_Excel.close()
cnxn_Excel.close()

产生以下控制台输出：

Server: localhost,49242
   DEBUG_INFO: DRIVER=ODBC Driver 17 for SQL Server;SERVER=localhost,49242;DATABASE=myDb;Trusted_Connection=yes;
   Table: Users
      DEBUG_INFO: SELECT * FROM [Users]
   Table: Computers
      DEBUG_INFO: SELECT * FROM [Computers]
   Table: Offices
      DEBUG_INFO: SELECT * FROM [Offices]
Server: 192.168.0.179,49242
   DEBUG_INFO: DRIVER=ODBC Driver 17 for SQL Server;SERVER=192.168.0.179,49242;DATABASE=myDb;Trusted_Connection=yes;
   Table: Users
      DEBUG_INFO: SELECT * FROM [Users]
   Table: Computers
      DEBUG_INFO: SELECT * FROM [Computers]
   Table: Offices
      DEBUG_INFO: SELECT * FROM [Offices]

推荐阅读