Каков наилучший способ расчета с двумя разными и независимыми таблицами?

Question

У меня есть две разные таблицы, расположенные в двух независимых БД, и я пытаюсь найти лучший способ сделать некоторые вычисления.

Первая таблица содержит столбцы: site (varchar 256), посещаемость сайта (целое число), тип сайта (varchar 256). Вторая таблица содержит: сайт (varchar 256), ежемесячная стоимость (float), тип сайта (varchar 256).

Пока у меня есть результаты запроса, но я нахожусь в тупике о том, как выполнить фактический анализ, который в основном состоит из умножения посещаемости сайта и ежемесячной стоимости (упрощенный пример), при этом используя сайт и тип сайта в качестве ключа.

Теперь, если бы это были две разные таблицы в одной и той же БД, я мог бы просто сделать простое соединение и идти своим путем. Однако это невозможно. Таким образом, мне интересно, что было бы наиболее питоническим способом справиться с этим.

Мозговой штурм с моей стороны состоял в том, чтобы экспортировать первый запрос в виде csv, затем создать временную таблицу во второй базе данных и просто выполнить там анализ. Если есть лучший способ сделать работу в Python, пожалуйста, дайте мне знать.

Мой код:

import pyodbc

conn = pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=xyx;DATABASE=xxy;UID=xyx;PWD=xyx')

cursor = conn.cursor()

cursor.execute("select * from sites;")

rows = cursor.fetchall()

with open('file.txt' , 'w') as f:
    for row in rows:
        csv.writer(f).writerows(row)

cursor.close()
del cursor
conn.close()

conn = pyodbc.connect(driver='{Vertica}', server='blah', database='yys', port=5433, 
    uid='zzx', pwd='zzx')

cursor = conn.cursor()

cursor.execute("select * from table1;")

with open('otherfile.txt' , 'w') as f:
    for row in rows:
        csv.writer(f).writerows(row)

cursor.close()
del cursor
conn.close()

Answer 1

Подумайте об использовании пакета анализа данных Python, панды, которые могут читать запросы из баз данных SQL в кадры данных. Затем объедините оба кадра данных по уникальным полям или объедините оба кадра данных, если столбцы совпадают по именам.

Кроме того, вы можете запустить другой сложный анализ (например, groupby, pivot_table) и вывести фрейм данных с помощью to_csv или to_sql в качестве новой таблицы в базе данных SQL.

import pandas as pd
import pyodbc

# FIRST DATA FRAME      
conn = pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};\                      
                        SERVER=xyx;DATABASE=xxy;UID=xyx;PWD=xyx')
ssqlsite_df = pd.read_sql("select * from sites;", conn)
conn.close()

# SECOND DATA FRAME
conn = pyodbc.connect(driver='{Vertica}', server='blah', \
                      database='yys', port=5433, uid='zzx', pwd='zzx')
vertsite_df = pd.read_sql("select * from table1;", conn)
conn.close()

# MERGE DATA FRAMES
finaldf = merge(ssqlsite_df, vertsite_df, on=['SiteName', 'TypeOfSite'])

# CONCATENATE/STACK DATA FRAMES 
# (ASSUMING COLUMNS ARE SAME NAME, ADJUST IN QUERY FIELD ALIASES)
finaldf = concat([ssqlsite_df, vertsite_df], axis=0)  

print(finaldf.head(10))    # FIRST TEN ROWS

# DATA ANALYSIS
# CALCULATED COLUMN
finaldf['SiteCost'] = finaldf['WebsiteTraffic'].convert_objects(convert_numeric=True) * \
                      finaldf['MonthlyCost'].convert_objects(convert_numeric=True)     
... 

# OUTPUT DATA FRAME
# CREATES NEW TABLE IF PERMISSION GRANTED. BE SURE TO RE-OPEN CONNECTION.
# finaldf.to_sql('SiteAnalysis', conn)  

finaldf.to_csv('C:\\Path\\To\\SiteAnalysis.csv')