Python Connection to Multiple Servers with Different SQL Dialects?

Question

Question: How can I run SQL queries via Python to retrieve data from multiple servers that may have different SQL dialects (e.g. where one server dialect uses TOP X and another SAMPLE X)?

Currently I have some code that allows me to run a query against each server separately (which is insufficient):

    import pyodbc
    import pandas as pd
    cnxn = pyodbc.connect('DSN=dsn_name;UID=username;PWD=password')
    query = str("""
                SELECT * 
                FROM sqlserver1.tableA 
                """)
    df = pd.read_sql(query, cnxn)

Goal: What I would like to do is to join data from a different server, basically I would like to have an SQL query in Python that looks somewhat like this - handling different SQL dialects:

        query = str("""
                    SELECT * 
                    FROM sqlserver1.tableA as sq
                    INNER JOIN teradataserver2.tableB as tera
                            ON sq.id = tera.id
                    """)

.. P.S. I have tried a workaround like this:

        import pyodbc
        import pandas as pd
        cnxnServer1 = pyodbc.connect('DSN=dsn_name1;UID=username;PWD=password')
        cnxnServer2 = pyodbc.connect('DSN=dsn_name2;UID=username;PWD=password')

        queryServer1 = str("""
                    SELECT * 
                    FROM sqlserver1.tableA 
                    """)
        queryServer2 = str("""
                    SELECT * 
                    FROM teradataserver2.tableB 
                    """)

.. and then use a Pandas Merge. But this is too ineffective.

Note: I run Python 3.7

Did you try linked server which allow to execute the queries mentioned as your goal? — serge, Dec 17 '18 at 15:07
No, I have not. If that would achieve the goal, then I would be glad if you would share the solution. — dady7749, Dec 17 '18 at 15:26
What do you mean by *but this is too ineffective*? Slow? Errors? — Parfait, Dec 17 '18 at 18:58

Gord Thompson · Accepted Answer · 2021-04-05T13:28:30.107

If you'd rather not (or are unable to) mess with server configurations (e.g., to create a linked server) then you might be able to do something with SQLAlchemy's SQL Expression Language to create statements that are interpreted using the dialect of the SQLAlchemy engine object.

For example,

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select

cnxn_url = 'mssql+pyodbc://@SQLmyDb'
engine = create_engine(cnxn_url)
metadata = MetaData()
my_table = Table('my_table', metadata, autoload=True, autoload_with=engine)
stmt = select([my_table.c.id, my_table.c.txt])\
    .select_from(my_table)\
    .order_by(my_table.c.id)\
    .limit(2)
with engine.begin() as conn:
    print(conn.execute(stmt).fetchall())

will send a T-SQL statement to the SQL Server and return the results.

If we simply change the connection URL to

cnxn_url = 'mysql+pymysql://root:whatever@localhost/mydb'

then SQLAlchemy will send the appropriate MySQL statement to the MySQL server.

However, that may not be sufficient if you really want to join one table in database_A with another table in database_B. In that case you'd be looking at pulling the data from the various tables into a common environment like pandas DataFrames or tables in an in-memory SQLite database.

score 0 · Answer 2 · answered Dec 17 '18 at 15:47

You can add linked server to SQL Server for accessing teradataserver2 from sqlserver1. The following query can be used in context of connection to sqlserver1 (check name syntax for teradataserver2)

SELECT * 
FROM tableA as sq
     INNER JOIN [teradataserver2].mydb.dbo.tableB as tera
         ON sq.id = tera.id

Python Connection to Multiple Servers with Different SQL Dialects?

2 Answers2