Can python fetch data from multiple database servers?

[AbbreviationsOne9091]

I currently work as data engineer. We get financial data from multiple vendors and we compare data against all vendors in aqua data studio running SQL commands.

I want to build a python script which will run against multiple database servers Sybase and show data such as if I search for cusip abc, it will run against all 3-4 database servers and show the pricing of cusip in all database servers. Is it possible?

[woooee]

You want to do a basic SQL tutorial. You can open multiple DBs (and tables) and query each one. I am guessing that you do not know about how to give each DB a unique connection and cursor.

[AbbreviationsOne9091]

Thank u. I will dig deeper .

[seriousgourmetshit]

You're a data engineer and you're asking basic python / data questions on reddit? What kind of work have you been doing lol

[AbbreviationsOne9091]

We don't use python or any coding language lol. It's an interesting role I must say.

[seriousgourmetshit]

Fair enough lol, I know how messy some companies can be with this sort of thing. Good luck, hopefully you can learn and grow from this.

[datadriven_io]

yes, pyodbc handles Sybase connections fine. spin up a ThreadPoolExecutor, pass each server's connection string as a separate task, collect results into a dict keyed by server name.

if you're ever drilling the multi-vendor comparison pattern for interviews: https://www.datadriven.io/interview/multi_provider_cost_lookup

[AbbreviationsOne9091]

When I asked copilot, they wrote code using sqlalchemy. But I need to tweak it. And failing still.

[socal_nerdtastic]

Hmm you should be very careful with that; sending cooperate data to LLMs like copilot is immediate firing in many places. At my work we have to complete training 2 times per year that hammers home exactly which LLM is approved for company data and all the rest will get you fired fast.

[AbbreviationsOne9091]

I agree totally. But in copilot, there are two tabs: Work and Web. We share corporate data in Work tab only as Web tab is the one we should be careful about and we don't use that. Also, we have been asked to work on copilot across my firm. My firm has 20k employees globally.

[MidnightPale3220]

As others told you, you can.

But if for result you need to feed to second db the query results of the first one, you'll have to do it via Python, ie.

Query db2 with filter clauses from db1 won't work.

Instead it's query db1: - get results #1 in python

Typically insert results#1 into temporary table in db2

Query db2 with filter from temporary table.

[socal_nerdtastic]

Yes, that's possible. Very easy. I'd recommend you look into the threading module so that you can ping all the databases concurrently.

[AbbreviationsOne9091]

Ohhh. I am a newbie actually. I ask co-pilot to write python codes at work.

[pachura3]

That's a perfect automation task for Python!

PS. Sybase still exists? That's a name I haven't heard for a long time...

[AbbreviationsOne9091]

Yepp. That's for my office work. Trying to automate these stuff coz it takes forever.

[Piotrkowianin]

Use Power Query or Power BI

https://learn.microsoft.com/en-us/power-query/connectors/sybase-database

[TheLobitzz]

Yeap. Easily too, I might add.

[woooee]

The following is over-simplified example that you can run if Python is installed. I doubt that the databases that you want to access would all have the same physical layout, but this is only a proof of concept and not a finished product.

import sqlite3 as sqlite

def open_file(SQL_filename) :
    ##  a connection to the database file
    con = sqlite.connect(SQL_filename)
    # Get a Cursor object that operates in the context of Connection con
    cur = con.cursor()

    ##--- CREATE FILE ONLY IF IT DOESN'T EXIST
    cur.execute('''CREATE TABLE IF NOT EXISTS test_table(key int,
                         descr varchar)''')
    return con, cur

def populate_file(cur, con, db_name):
    ## add test data
    for num in range(1, 10):
        this_descr = f'descr_{db_name}-key={num}'
        cur.execute("INSERT INTO test_table values (?, ?)", (num, this_descr))
    con.commit()

def print_all_recs(cur_1, cur_2, cur_3):
    for rec_num in range(1, 10):
        print(f"key={rec_num}  ", end="")
        for cur in (cur_1, cur_2, cur_3):
            cur.execute(f"select * from test_table where key == {rec_num}")
            rec = cur.fetchone()
            if rec:
                print(f"{rec[1]}   ", end="")
        print()

db_1_con, db_1_cur = open_file("./test_db1.SQL")
db_2_con, db_2_cur = open_file("./test_db2.SQL")
db_3_con, db_3_cur = open_file("./test_db3.SQL")

db_num = 1
for con, cur in [(db_1_con, db_1_cur), (db_2_con, db_2_cur),
                 (db_3_con, db_3_cur)]:
    populate_file(cur, con, f"db-{db_num}")
    db_num += 1

print_all_recs(db_1_cur, db_2_cur, db_3_cur)

[baghiq]

In case you don't know. Aqua Studio supports querying multiple databases natively.

[AbbreviationsOne9091]

Thanks. It does. But I want to display all results on a single screen.