使用Pyodbc方法优化脚本

Question

为了获取一些稍后需要用Matlab处理的数据，我使用python脚本从一系列50多个相同的数据库（即共享相同的表结构）中提取数据

我可以用下面的代码做到这一点。然而，为了避免创建空的文本文件（因为其中一些数据库根本没有相关数据），我首先运行查询只是为了检查它是否返回空，然后我被迫再次运行它以获取数据本身并将其写入文件。

import thesis,pyodbc
# SQL Server settings
drvr = '{SQL Server Native Client 10.0}'
host = 'POLIVEIRA-PC\MSSQLSERVER2008'
user = 'username'
pswd = 'password'
# Establish a connection to SQL Server
cnxn = pyodbc.connect(driver=drvr, server=host, uid=user, pwd=pswd) # Setup connection
# Prepare condition
tags = thesis.sensors().keys()
condition = ' WHERE Tag_ID=' + tags[0]
for tag in tags[1:]:
    condition += ' OR Tag_ID=' + tag
# Extract data from each database
for db in thesis.db_list():
    # Prepare query
    table = '[' + db + '].dbo.tBufferAux'
    query  = 'SELECT Data, Tag_ID, Valor FROM ' + table + condition + ' ORDER BY Data ASC'
    # Check if query's output is empty
    if not cnxn.cursor().execute(query).fetchone():
        print db, 'has no records!'
        continue # If so, jump to next database
    # Otherwise, save query's output to text file
    filename = 'Dataset_' + db + '.txt'
    filepath = thesis.out_dir() + filename
    with open(filepath,'w') as file:
        for record in cnxn.cursor().execute(query):
            file.write(str(record.Data) + ' ' + str(record.Tag_ID) + ' ' + str(record.Valor) + 'n')
# Close session
cnxn.cursor().close()
cnxn.close()

尽管这段代码运行良好，大约在20秒内完成，但我很好奇是否有任何方法可以通过避免重复执行查询来优化这个脚本，即避免两次调用cnxn.cursor().execute(query)。

顺便说一句，我对Python和SQL都很陌生，所以如果你能在我的代码中发现一些错误或不被视为良好的做法并告诉我，我将不胜感激。

Answer 1

首先，我建议您查看pymssql，它有一些pyodbc没有的好功能。

其次，我更强烈地建议研究SqlServerbcp或SSIS。它们是为这类事情而构建的，并且将比使用python更高效。

第三，如果所有的dbs都在同一台服务器上，那么实际上可以使用master.sys.databases在T-SQL中完成整个工作，并将工作推送到服务器。

考虑到这一点：

import thesis,pyodbc
# SQL Server settings
drvr = '{SQL Server Native Client 10.0}'
host = 'POLIVEIRA-PC\MSSQLSERVER2008'
user = 'username'
pswd = 'password'
# Establish a connection to SQL Server
cnxn = pyodbc.connect(driver=drvr, server=host, uid=user, pwd=pswd) # Setup     connection
# Prepare condition
tags = thesis.sensors().keys()
condition = ' WHERE Tag_ID=' + tags[0]
for tag in tags[1:]:
    condition += ' OR Tag_ID=' + tag
# Extract data from each database
for db in thesis.db_list():
    # Prepare query
    table = '[' + db + '].dbo.tBufferAux'
    query  = 'SELECT Data, Tag_ID, Valor FROM ' + table + condition + '    ORDER BY Data ASC'
    # Check if query's output is empty
    cursor = cnxn.cursor()
    cursor.execute(query)
    if cursor.rowcount == 0:
        print db, 'has no records!'
    else:
        filename = 'Dataset_' + db + '.txt'
        filepath = thesis.out_dir() + filename
        with open(filepath,'w') as file:
            while cursor.fetchone():
                file.write(str(record.Data) + ' ' + str(record.Tag_ID) + ' ' + str(record.Valor) + 'n')
# Close session
cnxn.close()

一些更具风格的东西：

1. 尽可能避免继续，如果其他用于控制流量
2. Pyodbc游标可以执行多个查询并持久化。您不需要每次执行查询时都创建一个新的查询
3. 光标"记住"它执行的最后一个查询
4. 我怀疑如果你使用空格分隔的文件，你会很难过。。。我从经验说起…：(
5. 当光标超出范围时，它们会自动关闭，所以如果不需要的话，cursor.close（）