[egenix-users] ms sql server examples?

[Steve Holden]

Lyons, Andrew Myers wrote:

> Hi,
>
> I'm trying to use mxodbc on unix with a ms sql server running on 
> windows 2k server, as part of some medical research.  I learn best by 
> example, and would greatly appreciate if anyone could send me a sample 
> python script that has any relevance to my situation. 
> [andrew.m.lyons at vanderbilt.edu]
>
I don't know whether this will server, it's a copy of a program I use to 
copy the contents of a remote database to a local one. At least it shows 
you mx.ODBC in action. Others may have more illuminating examples for you.

regards
 Steve

-------------- next part --------------
#
# FraudCopy.py: gets a local copy of the OnePlus database
#
# XXX NOTE: this program is EXTREMELY FRAGILE: it's a modified copy of
# XXX       dbImport.py solely to copy the contents of the fraud table
#
import mx.DateTime as dt

import mx.ODBC.Windows
def RMinit():
    conn = mx.ODBC.Windows.DriverConnect("""Driver={SQL Server};Server=THEHEART;Database=GPWeb;Uid=USERNAME;Pwd=PASSWORD;""")
    # Note that this uses a DSN-less way of connection
    curs = conn.cursor()
    return conn, curs

import db

import sys
#
# Open connections to both databases
#
RMconn, RMcurs = RMinit()  # Gets a connection and a cursor on a DB
LCconn, LCcurs = db.init() # Very like RMinit but on a local DB copy

#
# Check that table structures agree in both databases
#
strerr = 0
tables = ["Fraud"]
for tbl in tables:
    print "CHECKING", tbl
    sql = "SELECT * FROM %s WHERE 0=1" % tbl
    RMcurs.execute(sql)
    LCcurs.execute(sql)
    if RMcurs.description != LCcurs.description:
        strerr += 1
        print "... STRUCTURE CLASH!"
        for rowrm, rowlc in zip(RMcurs.description, LCcurs.description):
            if rowrm != rowlc:
                print "remote:", rowrm
                print "local :", rowlc
    columns = [d[0] for d in LCcurs.description]
    fields = { "Fraud": columns}
    idtables = {}

if strerr:
    print strerr, "errors in structure check"
    sys.exit("Terminating!")

#
# Zero the existing tables in the local copy
#
for tbl in tables:
    print "DELETING", tbl
    LCcurs.execute("DELETE FROM %s" % tbl)
    LCconn.commit()

#
# Copy remote to local
for tbl in tables:
    stmtin = "SELECT %s FROM %s" % (", ".join(fields[tbl]), tbl)
    stmtout = "INSERT INTO %s (%s) VALUES (%s)" % \
                    (tbl, ", ".join(fields[tbl]), ", ".join(["?" for x in fields[tbl]]))
    print "INPUT: ", stmtin
    print "OUTPUT:", stmtout
    RMcurs.execute(stmtin)
    while 1:
        data = RMcurs.fetchmany(1000)
        if data:
            LCcurs.executemany(stmtout, data)
            LCconn.commit()
            sys.stdout.write(".")
            sys.stdout.flush()
        else:
            break

#
# Close database connections, dummy!
#
RMconn.close()
LCconn.close()