def init_db_conn(connect_string, username, passwd): """initializes db connections""" global _CONN if not _CONN: global _CONNECT_STRING global _USERNAME global _PASSWD _CONNECT_STRING = connect_string _USERNAME = username _PASSWD = passwd dbinfo = connect_string try: if USE_JYTHON: print(dbinfo) _CONN = zxJDBC.connect(connect_string, username, passwd, 'com.informix.jdbc.IfxDriver') else: (dbname, dbuser, _) = connect_string.split('/', 3) dbinfo = 'db: %s:%s' % (dbname, dbuser) try: _CONN = odbc.odbc(connect_string) print(dbinfo) except KeyboardInterrupt: raise except: ex = sys.exc_info() s = 'Exception: %s: %s\n%s' % (ex[0], ex[1], dbinfo) print(s) return None return _CONN
def InsertCPK_DB(mac, pn, section, value): db = odbc.odbc("TESTlog/TEST/test") cursor = db.cursor() pdline = ReturnPdline_oracle() keys = '' vals = '' for key in value: print key sql = "IF NOT EXISTS (SELECT name FROM syscolumns WHERE (id IN (SELECT id \ FROM sysobjects WHERE name = 'CPKDATA')) AND (name = '%s')) ALTER TABLE CPKDATA ADD %s float" % ( key, key) cursor.execute(sql) keys += key + ',' vals += '%s,' % value[key][2] sql = "select limitl,limith from CPKSPEC where (pn='%s') and (testsection='%s') and (item='%s')\ order by orderid DESC" % (pn, section, key) cursor.execute(sql) data = cursor.fetchone() LSL = '%.2f' % value[key][0] USL = '%.2f' % value[key][1] if not data or data[0] <> float(LSL) or data[1] <> float(USL): sql = "insert into CPKSPEC (pn,testsection,item,limitl,limith) values ('%s','%s','%s',%s,%s)\ " % (pn, section, key, LSL, USL) cursor.execute(sql) sql = "insert into CPKDATA (%smac,pn,pdline,stationname,testsection) \ values(%s'%s','%s','%s','%s','%s')" % (keys, vals, mac, pn, pdline, Station, section) cursor.execute(sql) cursor.execute("select * from CPKDATA where mac = '%s'" % mac) data = cursor.fetchone() if data: return data[0] else: return 0
def Get(db,query): con = odbc.odbc('%s/DM/******' % db) cur = con.cursor() cur.execute(query) dbRes = cur.fetchall() return dbRes
def perform_inserts(full_type, datatype, constant, operator, trig_count, length='none', scale='none', qualifier='none', bin_op='none', coeff='none'): execsql("delete from T") for insert in insert_vals[full_type]: insert1 = convert_val(SQL, datatype, insert, qualifier) if constant != 'none': const = convert_val(SQL, datatype, constant, qualifier) dbc = odbc.odbc('vlboot/VIGILERT/VIGILERT') crsr = dbc.cursor() # create a cursor crsr.execute("create table tempT(field "+full_type+")") crsr.execute("insert into tempT values(" + insert1 + ")") if bin_op != 'none': crsr.execute("select * from tempT where field " +bin_op+ " " + `coeff` + " " +operator+ " " +const) else: crsr.execute("select * from tempT where field " +operator+ " " +const) found = 0 if crsr.fetchall() != []: # The operator evaluates the insert and constant to TRUE. truth = "echo 't"+`trig_count`+" should fire';" else: # The operator evaluates the insert and constant to FALSE. truth = "echo 't"+`trig_count`+" should not fire';" exectl(truth) # Execute the insert command. crsr.execute("insert into T values(" + insert1 + ")") process_updates() crsr.execute("drop table tempT") crsr.close() dbc.close()
def getConnection(self): if self._conn is None: try: self._conn = odbc.odbc(self.dsn) except Exception, e: if self._verbose: print "WinOdbcConnectionProvider.getConnection: Error=%s" % ( e)
def check(table): connect = odbc.odbc('oasis') db = connect.cursor() db.execute("SELECT id_title, id_num FROM oasis." + str(table)) result = db.fetchall() for i in result: print(i)
def setUp(self): self.tablename = "pywin32test_users" self.db_filename = None self.conn = self.cur = None try: # Test any database if a connection string is supplied... conn_str = os.environ["TEST_ODBC_CONNECTION_STRING"] except KeyError: # Create a local MSAccess DB for testing. self.db_filename = tempfile.NamedTemporaryFile().name + ".mdb" # Create a brand-new database - what is the story with these? for suffix in (".36", ".35", ".30"): try: dbe = EnsureDispatch("DAO.DBEngine" + suffix) break except pythoncom.com_error: pass else: raise TestSkipped("Can't find a DB engine") workspace = dbe.Workspaces(0) newdb = workspace.CreateDatabase(self.db_filename, constants.dbLangGeneral, constants.dbEncrypt) newdb.Close() conn_str = "Driver={Microsoft Access Driver (*.mdb)};dbq=%s;Uid=;Pwd=;" % ( self.db_filename, ) ## print 'Connection string:', conn_str self.conn = odbc.odbc(conn_str) # And we expect a 'users' table for these tests. self.cur = self.conn.cursor() ## self.cur.setoutputsize(1000) try: self.cur.execute("""drop table %s""" % self.tablename) except (odbc.error, odbc.progError): pass ## This needs to be adjusted for sql server syntax for unicode fields ## - memo -> TEXT ## - varchar -> nvarchar self.assertEqual( self.cur.execute("""create table %s ( userid varchar(25), username varchar(25), bitfield bit, intfield integer, floatfield float, datefield datetime, rawfield varbinary(100), longtextfield memo, longbinaryfield image )""" % self.tablename), -1, )
def fetch(self): self._conn = odbc.odbc('AndyAccounts') self.drawings = self._fetchtable('Drawings') self.sales = self._fetchtable('Sales', 'SaleID') # new feature added later self.expenses = self._fetchdicts('Expenses', 'ExpenseID') self._conn.close()
def Send(db,query): # try: con = odbc.odbc('%s/DM/******' % db) cur = con.cursor() cur.execute(query) # except: # print 'Error Occured'
def setUp(self): self.tablename = "pywin32test_users" self.db_filename = None self.conn = self.cur = None try: # Test any database if a connection string is supplied... conn_str = os.environ['TEST_ODBC_CONNECTION_STRING'] except KeyError: # Create a local MSAccess DB for testing. self.db_filename = tempfile.NamedTemporaryFile().name + '.mdb' # Create a brand-new database - what is the story with these? for suffix in (".36", ".35", ".30"): try: dbe = EnsureDispatch("DAO.DBEngine" + suffix) break except pythoncom.com_error: pass else: raise TestSkipped("Can't find a DB engine") workspace = dbe.Workspaces(0) newdb = workspace.CreateDatabase(self.db_filename, constants.dbLangGeneral, constants.dbEncrypt) newdb.Close() conn_str = "Driver={Microsoft Access Driver (*.mdb)};dbq=%s;Uid=;Pwd=;" \ % (self.db_filename,) ## print 'Connection string:', conn_str self.conn = odbc.odbc(conn_str) # And we expect a 'users' table for these tests. self.cur = self.conn.cursor() ## self.cur.setoutputsize(1000) try: self.cur.execute("""drop table %s""" %self.tablename) except (odbc.error, odbc.progError): pass ## This needs to be adjusted for sql server syntax for unicode fields ## - memo -> TEXT ## - varchar -> nvarchar self.assertEqual(self.cur.execute( """create table %s ( userid varchar(25), username varchar(25), bitfield bit, intfield integer, floatfield float, datefield datetime, rawfield varbinary(100), longtextfield memo, longbinaryfield image )""" %self.tablename),-1)
def openConnection(self): if not DBInteractor.conn: config = ConfigParser.ConfigParser() config.read(os.path.dirname(__file__) + '/DB_connection.ini') driver = config.get("Database","Driver") server = config.get("Database","Server") database = config.get("Database","Database") DBInteractor.conn = odbc.odbc('DRIVER=' + driver + ';SERVER=' + server + ';DATABASE=' + database + ';Trusted_Connection=yes') return DBInteractor.conn
def ReturnDevice_ID_oracle(): #db = pyodbc.connect('DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s'%('172.28.206.253','test','test','test')) db = odbc.odbc("TESTlog/TEST/test") cursor = db.cursor() sql = "select * from OPENQUERY(MESDB,'select DRIVER_PARAMETER from tgs_gateway_base where GATEWAY_DESC_E = ''TEST01''')" if '172.28.209' in mes_lan_ip: sql = "select * from OPENQUERY(MESDB,'select DRIVER_PARAMETER from tgs_gateway_base where GATEWAY_DESC_E = ''TEST02''')" cursor.execute(sql) for i in cursor.fetchone(): return str(i).split(';')[1].split(',').index(mes_lan_ip) + 1
def ReturnPdline_oracle(): #db = pyodbc.connect('DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s'%('172.28.206.253','test','test','test')) db = odbc.odbc("TESTlog/TEST/test") cursor = db.cursor() cursor.execute("select * from OPENQUERY(MESDB,'select pdline_name from sys_pdline where pdline_id = (select pdline_id from sys_terminal where \ TERMINAL_ID =(select max(TERMINAL_ID) from tgs_terminal_link\ where DEVICE_ID = %s and SERVER_ID = (select server_ID from tgs_server_base where SERVER_DESC_E = ''TGS_2F_ATE'')))')"\ %ReturnDevice_ID_oracle()) for i in cursor.fetchone(): return str(i)
def phoneItemNum(dbtrid): con = odbc.odbc('DM3/DM/*********') cur = con.cursor() itemPhoneQuery = "SELECT MAX(item_no) FROM DBTRPHON WHERE debtor_id = %s" % dbtrid cur.execute(itemPhoneQuery) try: itemNumber = cur.fetchone()[0]+1 except: itemNumber = -1 return itemNumber
def getConnection( databaseType, databaseName ): connection = None if databaseType == 'MySQL': import MySQLdb connection = MySQLdb.connect( db = databaseName ) elif databaseType == 'Access': import odbc connection = odbc.odbc( databaseName ) return connection
def _connect(self,host=None,user=None,password=None,database=None): if user == None and password == None and database == None: dsn = host else: dsn = 'dsn='+self.addq(host) if (user != None): dsn += '; uid='+self.addq(user) if (password != None): dsn += '; pwd='+self.addq(password) if (database != None): dsn += '; database='+self.addq(database) self._conn = odbc.odbc(dsn) self._conn.setautocommit(1)
def Connect(self): if not self.dsn: raise (NameError, "no database!") self.db = odbc.odbc("dsn=%s;uid=%s;pwd=%s" % (self.dsn, self.uid, self.pwd)) cur = self.db.cursor() if not cur: raise (NameError, "cursor wrong!") else: print("connected!") return cur
def _connect(self, host=None, user=None, password=None, database=None): if user == None and password == None and database == None: dsn = host else: dsn = 'dsn=' + self.addq(host) if (user != None): dsn += '; uid=' + self.addq(user) if (password != None): dsn += '; pwd=' + self.addq(password) if (database != None): dsn += '; database=' + self.addq(database) self._conn = odbc.odbc(dsn) self._conn.setautocommit(1)
def fetchPhones(dm, ssn): con = odbc.odbc('%s/DM/******' % dm) cur = con.cursor() dbtrid = findDebtorID(dm, ssn) if dbtrid: getPhones = "SELECT phone, descr, status FROM dbtrphon WHERE debtor_id = %d" % dbtrid cur.execute(getPhones) phoneResults = cur.fetchall() else: phoneResults = None return phoneResults
def findDebtorID(dm, ssn): con = odbc.odbc('%s/DM/*******' % dm) cur = con.cursor() findDBTR = "SELECT debtor_id FROM dbtr401_view WHERE ssn1 = '%s' " % ssn cur.execute(findDBTR) try: result = cur.fetchone()[0] print dm, '-', result except: result = None return result
def ConectaBanco(self, sBancoDeDados, sLogin, sSenha): import odbc #import MySQLdb try: #self.gConexao = MySQLdb.connect( host = 'localhost', # db = sBancoDeDados, user = sLogin, passwd = sSenha ) self.gConexao = odbc.odbc(sBancoDeDados) except: showModalErrorBox(self, MBOX_OK, 'Erro', Resource.STR_NCONBANCO) return False else: return True
def ConectaBanco(self, sBancoDeDados, sLogin, sSenha): import odbc #import MySQLdb try: #self.dbConexao = MySQLdb.connect( host = 'localhost', # db = sBancoDeDados, user = sLogin, passwd = sSenha ) self.dbConexao = odbc.odbc( sBancoDeDados ) except: self.MsgDlg(Resource.STR_NCONBANCO, gtk.MESSAGE_ERROR) return False else: return True
def IsDBServerAlive (self): "Check if the database server is running." info = self.info try: dataSourceArgument = info.odbcDataSource + "/" + info.userName + "/" + info.password dbConnection = odbc.odbc ( dataSourceArgument ) except: return FALSE else: dbConnection.close() dbConnection = None return TRUE
def ConectaBanco(self, sBancoDeDados, sLogin, sSenha): import odbc #import MySQLdb try: #self.gConexao = MySQLdb.connect( host = 'localhost', # db = sBancoDeDados, user = sLogin, passwd = sSenha ) self.gConexao = odbc.odbc(sBancoDeDados) except: wxMessageBox(Resource.STR_NCONBANCO, 'Erro', wxOK | wxICON_ERROR) return False else: return True
def Connect(self, connectionString): """ Connect to an ODBC database. The user should know the connection string format MS Access: r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=%s;" % filePath MS SQL: "DRIVER={SQL Server};SERVER=%s;UID=%s;PWD=%s;DATABASE=%s" % (server, UID, password, dbname) """ try: print(connectionString) self.conn = odbc.odbc(connectionString) self._GetCursor() except: print("Connect: Unexpected error:\n", sys.exc_info()) traceback.print_exc() self.conn = None
def Insert_WiFiKEY( mac, key_dic, log, Trigger=0 ): #key_dic,mode '0:send keys to each DB(SQL+ORACLE)|1:just send to ORACLE(MES)' db = odbc.odbc("TESTlog/TEST/test") SQL = db.cursor() SQL.execute("DECLARE @Transferor VARCHAR(500) EXEC SSIDSYNC '%s', '%s', '%s', '%s', '%s', %d,\ @Transferor OUTPUT SELECT @Transferor" %(mac,key_dic['SSID_PASSWORD'],key_dic['NETWORK_KEY'],\ key_dic['WPS_PIN'],key_dic['FON_KEY'],Trigger)) data = str(SQL.fetchone()[0]).strip() if data != 'ok': raise Except('ErrorCode(0005):' + data) else: log('WiFi keys insert to DB success.', 2)
def checkDates(bkpDate, DM, debtID): con = odbc.odbc('%s/DM/TURBODELTA' % DM) cur = con.cursor() query = "SELECT serv_date FROM debt401_view WHERE debt_id = %d" % debtID cur.execute() results = cur.fetchone() dmDate = results[0] convertDate = bkpDate.split('/') bkpDate = datetime.date(convertDate[2],convertDate[0],convertDate[1]) if bkpDate > dmDate: return True else: return False
def setUp(self): self.db_filename = None self.conn = self.cur = None try: # Test any database if a connection string is supplied... conn_str = os.environ['TEST_ODBC_CONNECTION_STRING'] except KeyError: # Create a local MSAccess DB for testing. self.db_filename = os.path.join(tempfile.gettempdir(), "test_odbc.mdb") if os.path.isfile(self.db_filename): os.unlink(self.db_filename) # Create a brand-new database - what is the story with these? for suffix in (".36", ".35", ".30"): try: dbe = EnsureDispatch("DAO.DBEngine" + suffix) break except pythoncom.com_error: pass else: raise RuntimeError, "Can't find a DB engine" workspace = dbe.Workspaces(0) newdb = workspace.CreateDatabase(self.db_filename, constants.dbLangGeneral, constants.dbEncrypt) newdb.Close() conn_str = "Driver={Microsoft Access Driver (*.mdb)};dbq=%s;Uid=;Pwd=;" \ % (self.db_filename,) self.conn = odbc.odbc(conn_str) # And we expect a 'users' table for these tests. self.cur = self.conn.cursor() try: self.cur.execute("""drop table pywin32test_users""") except (odbc.error, dbi.progError): pass self.assertEqual( self.cur.execute("""create table pywin32test_users ( userid varchar(5), username varchar(25), bitfield bit, intfield integer, floatfield float, datefield date, )"""), -1)
def setUp(self): self.db_filename = None self.conn = self.cur = None try: # Test any database if a connection string is supplied... conn_str = os.environ["TEST_ODBC_CONNECTION_STRING"] except KeyError: # Create a local MSAccess DB for testing. self.db_filename = os.path.join(tempfile.gettempdir(), "test_odbc.mdb") if os.path.isfile(self.db_filename): os.unlink(self.db_filename) # Create a brand-new database - what is the story with these? for suffix in (".36", ".35", ".30"): try: dbe = EnsureDispatch("DAO.DBEngine" + suffix) break except pythoncom.com_error: pass else: raise RuntimeError, "Can't find a DB engine" workspace = dbe.Workspaces(0) newdb = workspace.CreateDatabase(self.db_filename, constants.dbLangGeneral, constants.dbEncrypt) newdb.Close() conn_str = "Driver={Microsoft Access Driver (*.mdb)};dbq=%s;Uid=;Pwd=;" % (self.db_filename,) self.conn = odbc.odbc(conn_str) # And we expect a 'users' table for these tests. self.cur = self.conn.cursor() try: self.cur.execute("""drop table pywin32test_users""") except (odbc.error, dbi.progError): pass self.assertEqual( self.cur.execute( """create table pywin32test_users ( userid varchar(5), username varchar(25), bitfield bit, intfield integer, floatfield float, datefield date, )""" ), -1, )
def CheckHWandSWfromDB(*argv): ''' argv : dutid,terms,labels,Panel,Log,Config,flow,[Return]) ''' pn = argv[-3]('Base', 'PN').strip() hw, sw = map(strip, argv[-3]('Base', 'HSW').split('|')) db = odbc.odbc("TESTlog/TEST/test") cursor = db.cursor() sql = "SELECT HW,SW FROM VersionControl WHERE PN='%s' ORDER BY DATATIME DESC" % pn cursor.execute(sql) data = cursor.fetchone() if not data: raise Except("ErrorCode(0011):get hw and sw failed from db") if hw <> data[0] or sw <> data[1]: raise Except("ErrorCode(0010):Check hw and sw failed") argv[4]('check hw and sw finish from db', 2)
def Begin (self): "Open a vapi connection and a direct database connection." info = self.__info # Output the mode. if info.mode == vapi.VL_DATABLADE: modeString = "datablade" if info.driverOn: driverOnString = " with driver on" else: driverOnString = " with driver off" else: modeString = "middleware" driverOnString = "" print "\nBeginning VAPI session ("+modeString+driverOnString+").\n" # Initialize the direct database connection. try: dataSourceArgument = info.odbcDataSource + "/" + info.userName + "/" + info.password self.__dbConnection = odbc.odbc ( dataSourceArgument ) except: print "\nVL Session Error: Could not initialize odbc connection for "+dataSourceArgument return vapi.VL_ERROR # Open a cursor to the database connection. try: self.__dbCursor = self.__dbConnection.cursor() except: print "\nVL Session Error: Could not open an odbc cursor for "+dataSourceArgument+"." return vapi.VL_ERROR # Open a VAPI connection. if info.mode == vapi.VL_DATABLADE: result = self.__vapiConnection.Open(info.serverName, info.dbName, info.userName, info.password) if result == vapi.VL_ERROR: print "\nVL Session Error: Could not open a vapi connection for", info.serverName+"/"+info.dbName+"/"+info.userName+"/"+info.password else: # Start Informix tracing. We need this to # (a) see echos from fired triggers (when the driver is off) and to # (b) see the results of vl_gather_stats() when the driver is on. self.__dbCursor.execute(START_INFORMIX_TRACING_COMMAND) return result else: result = self.__vapiConnection.Open (info.host, info.vlServerPort) if result == vapi.VL_ERROR: print "\nVL Session Error: Could not open a vapi connection for", info.host+"/"+`info.vlServerPort` return result
def populate(self): # first time in a new day try: cursor = odbc.odbc(DATA_SOURCE).cursor() cursor.execute("SELECT [Chart Number], Name, Provider, [Start Time], Status, [Reason Code] \ FROM OHAPP WHERE Date = '%s'" % time.strftime('%Y-%m-%d')) patients = [] for doctor in DOCTORS: patients.append([]) chartnos = [] rejected = [] while True: record = cursor.fetchone() if not record: break if not record[0]: continue if record[5] in BAD_REASONS: continue if record[4] in ('3', '4'): continue elif record[4] == '6': status = 'gone' else: status = 'not yet' try: doctor = DOCTORS_BY_CODE[record[2]] except KeyError: continue if record[0] in chartnos: # duplicate appointment print record[2] rejected.append((record[0], record[1], DOCTORS[doctor], record[3])) continue else: chartnos.append(record[0]) patients[doctor].append(Patient(record[0], record[1], doctor, record[3], status)) for doctor in patients: doctor.sort(key=index_patient) self.patients = patients self.rejected = rejected except Exception, e: print "Patient Tracker has had a major error and can't start." print "Call Brian Goldman at (XXX) XXX-XXXX and he will fix it." logging.exception(datetime.now()) time.sleep(-1)
def _db(self): if self._name[0:5]=='odbc:': import odbc return odbc.odbc(self._name[5:]) elif self._name[0:7]=='sqlite:': import sqlite3 return sqlite3.connect(self._name[7:]) elif self._name[0:7]=='oracle:' and self._name.find('@')>0 and self._name.find('/')>0: rest,password = self._name[7:].split('/') username,database = rest.split('@') return self.__oracleConnection(username, password, database) elif self._name.find('@')>0 and self._name.find('/')>0: import cx_Oracle rest,password = self._name.split('/') username,database = rest.split('@') return self.__oracleConnection(username, password, database) return cx_Oracle.Connection(self._name)
def GetDB(DBparam=None, special=None): """ Get a DB connection DBparam == '__DEFAULT_DB__' provides connection to default DB based on the host machine name. This is really for the Live servers 'dbserv','webserv' Any machine in Altair will default to the live backup 'Groundsoft_DBSERV' special == 'special' means that the special param will hold the login info for the require DB """ global _debug global _askDBGagain name = r'knowlogicTestDB.db' conn = sqlite3.connect(name) return conn #Open a DB connection, Check to see if we are running this on the live site host = socket.gethostname() if host == 'AE415730': p = r'H:\Knowlogic\Documents\Trace\TraceDB.mdb' elif host == 'Main': p = r'F:\Knowlogic\Documents\Trace\TraceDB.mdb' elif host == 'Jeff-Laptop': p = r'I:\Knowlogic\Documents\Trace\TraceDB.mdb' else: p = r'E:\Knowlogic\Documents\Trace\TraceDB.mdb' DBselect = 'DB' if DBselect.lower() == 'special': DBselect = 'DB' p = special # DB Choices DBchoices = { "DB": r"DRIVER={Microsoft Access Driver (*.mdb)}; Dbq=%s;" % p, } print('help') return None try: login = DBchoices[DBselect] return odbc.odbc(login) except: print("GetDB: Unexpected error:\n", sys.exc_info()) traceback.print_exc() return None
def __init__(self): 'Connect to the database' dsn = config.databaseDSN self.connection1 = pyodbc.connect('dsn=' + dsn) self.connection2 = odbc.odbc('dsn=' + dsn) self.cursor1 = self.connection1.cursor() self.cursor2 = self.connection2.cursor() self.getBundleByLabel = { 'Agreements': self.getAgreementBundle, 'Companies': self.getCompanyBundle, 'Contacts': self.getContactBundle, 'Documents': self.getDocumentBundle, 'Marketing Projects': self.getMarketingProjectBundle, 'Marketing Targets': self.getMarketingTargetBundle, 'Patents': self.getPatentBundle, 'Remarks': self.getRemarkBundle, 'Technologies': self.getTechnologyBundle, }
def InsertUserData (dbname, dbuser, dbpwd, data): try: # Make sure the data has no special characters data = [ quote(data[0]), quote(data[1]), quote(data[2]), quote(data[3]), quote(data[4]), quote(data[5]), quote(data[6]) ] # First delete any current row for this user conn = odbc.odbc ("%s/%s/%s" % (dbname, dbuser, dbpwd)) cur = conn.cursor() sql = "delete from LunchInfo where user='******'" % (data[0]) cur.execute (sql) # Then insert a row with the new data sql = "insert into LunchInfo values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (data[0], data[1], data[2], data[3], data[4], data[5], data[6], GetTimeStamp()) cur.execute (sql) conn.close() conn, cur = (None, None) except: print '<font size="+1">SQL Error: %s</font>: %s<BR>' % sys.exc_info()[:2] print '<PRE>%s</PRE>' % (sql)
def main(): config = ConfigParser.ConfigParser() config.read('app-reindex.ini') application_id = config.get('DEFAULT', 'application_id') ft_index = config.get('DEFAULT', 'ft_index') retry_count = config.get('DEFAULT', 'retry_count') db_username = config.get('DEFAULT', 'db_username') db_password = config.get('DEFAULT', 'db_password') db_dsn = config.get('DEFAULT', 'db_dsn') throttle = config.getint('DEFAULT', 'throttle') last_indexed = config.getint('DEFAULT', 'last_indexed') try: connection = odbc.odbc('{0}/{1}/{2}'.format(db_dsn, db_username, db_password)) cursor = connection.cursor() except: print('There is a problem with the ODBC connection') print('Either it does not exist, or the login credentials are wrong') docs_to_index = get_records(application_id, cursor) for docnumber in docs_to_index: try: if docnumber[0] >= last_indexed: insert_record(docnumber[0], ft_index, retry_count, cursor) print('Inserted Document {0} into docsadm.needs_indexing'.format(docnumber[0])) time.sleep(throttle) else: # This skips because last_index is populated in populate.ini print('Skipping Document {0}. It has already been populated.'.format(docnumber[0])) continue except: # This skips because an entry already exists in docsadm.needs_indexing print('Document {0} not inserted for some reason.'.format(docnumber[0])) time.sleep(throttle) continue cursor.close() connection.close()
def query(sql, connect_as, pos=None, limit=100, caller=None): #global db print connect_as (user, db, pwd, host, port) = connect_as #(db,user)= ('MRR_BI','MRR_ETL_USER') assert sql, 'sql statement is not defined' assert db, 'Database is not set' assert user, 'User is not set' assert host, 'Host is not set' assert port, 'Port is not set' #if _db: db = _db #db1='MRR' #blog.log('Connecting to %s as %s ...' % (db, user),(0,0)) print 'Connecting to %s as %s ...' % (db, user) #mapswdbun2-vip.nam.nsroot.net cs = 'Driver={Microsoft ODBC for Oracle};SERVER=%s:%s/%s;UID=%s;PWD=%s;' % ( host, port, db, user, pwd) if 0: cs = 'Driver={Microsoft ODBC for Oracle};SERVER=mapswdbdn1.nam.nsroot.net:11150/%s;UID=%s;PWD=%s;' % ( db, user, pwd) if db == 'SMARTP_B': cs = 'Driver={Microsoft ODBC for Oracle};SERVER=mapmwdbpn1-vip.nam.nsroot.net:11150/%s;UID=%s;PWD=%s;' % ( db, user, pwd) if db == 'SMARTU1A': cs = 'Driver={Microsoft ODBC for Oracle};SERVER=mapswdbun1-vip.nam.nsroot.net:11150/%s;UID=%s;PWD=%s;' % ( db, user, pwd) print cs err = None rowcount = None headers = [] status = 0 out = [] #blog.log('Fetching %s...' % (caller[3:]),pos) try: odb = odbc.odbc(cs) except dbi.opError, e: print 'dbi.opError' status = 1 err = e
def __init__(self): self.results = {} print ('Connecting to Hazus database ...') self.db = odbc.odbc('hazus64') self.cursor = self.db.cursor() print ('Connected.') self.intensity_mat = np.array([]) # initialization is not necessary, just for clarification self.exposure_mat = np.array([]) # initialization is not necessary, just for clarification self.damage_mat = np.array([]) # initialization is not necessary, just for clarification self.significance_mat = np.array([]) # initialization is not necessary, just for clarification self.urgency_mat = np.array([]) # initialization is not necessary, just for clarification self.urgency_mat_total = np.array([]) # initialization is not necessary, just for clarification self.delta_lat = 0 self.delta_lon = 0 self.delta_x = 0 self.delta_y = 0 # SIGNIFICANCE COEFS self.POP_SIGNIFICANCE_COEF = 1.0 self.BRIDGES_SIGNIFICANCE_COEF = 1.0 self.SCHOOLS_SIGNIFICANCE_COEF = 1.0 # Index constants self.POP_L = 0 self.SCHOOLS_L = 1 self.BRIDGES_L = 2 self.AREA_POP = 4 self.LAT_POP = 5 self.LON_POP = 6 self.POP = 7 self.BUILT_YEAR_POP = 8 self.LAT_SCHOOLS = 6 self.LON_SCHOOLS = 7 self.NumStudent = 5 self.BUILT_YEAR_SCHOOL = 4 self.LAT_BRIDGES = 5 self.LON_BRIDGES = 6 self.BUILT_YEAR_BRIDGES = 3 self.TRAFFIC = 4
def GetLunchData (dbname, dbuser, dbpwd): # Run the query to get everyone's lunch plans conn = odbc.odbc ("%s/%s/%s" % (dbname, dbuser, dbpwd)) cur = conn.cursor() cur.execute ('select * from LunchInfo') results = CleanDbResults (cur.fetchall()) # Delete any row whose timestamp is over 12 hours old dataChanged = 0 for row in results: if (time.time() - float(row[7])) > (12.0 * 60.0 * 60.0): # Make sure to use the quoted username, since that's what's in the database sql = "delete from LunchInfo where user='******'" % (quote(row[0])) cur.execute (sql) dataChanged = 1 # Refresh the query if the data was changed if dataChanged: cur.execute ('select * from LunchInfo') results = CleanDbResults (cur.fetchall()) conn.close() conn, cur = (None, None) return results
def perform_join_inserts(full_type, datatype, constant, operator, trig_count, length='none', scale='none', qualifier='none', bin_op='none', coeff = 'none'): for insert in insert_vals[full_type]: for inserta in insert_vals[full_type]: insert1 = convert_val(SQL, datatype, insert, qualifier) insert2 = convert_val(SQL, datatype, inserta, qualifier) if constant != 'none': const = convert_val(SQL, datatype, constant, qualifier) if bin_op == '/' and float(inserta) == 0: insert2 = '1' dbc = odbc.odbc('vlboot/VIGILERT/VIGILERT') crsr = dbc.cursor() # create a cursor crsr.execute("create table tempR(foo "+full_type+")") crsr.execute("create table tempS(bar "+full_type+")") crsr.execute("insert into tempR values(" + `insert1` +")") crsr.execute("insert into tempS values(" + `insert2` +")") if bin_op != 'none': crsr.execute("select * from tempR, tempS where foo " +bin_op+ " bar " +operator+ " " +`const`) else: crsr.execute("select * from tempR, tempS where foo " +operator+ " bar") found = 0 if crsr.fetchall() != []: # The operator evaluates the insert and constant to TRUE. truth = "echo 't"+`trig_count`+" should fire';" else: # The operator evaluates the insert and constant to FALSE. truth = "echo 't"+`trig_count`+" should not fire';" exectl(truth) # Execute the insert command. crsr.execute("insert into R values(" +insert1+ ");") process_updates() crsr.execute("insert into S values(" +insert2+ ");") process_updates() crsr.execute("drop table tempR") crsr.execute("drop table tempS") execsql("delete from R") execsql("delete from S") crsr.close() dbc.close()
def __init__(self,dbname): self._DB = odbc.odbc(dbname) self._cur = self._DB.cursor()
def MainProcess(od, db, user, pas, file_name): try: s = od + '/' + user + '/' + pas conn = odbc.odbc(s) except: print "I am unable to connect to the database" cur = conn.cursor() cur.execute("select tablename from pg_tables where schemaname='public'") tables = cur.fetchall() print "Deleting tables..." for table in tables: if not ((table[0] == 'spatial_ref_sys') or (table[0] == 'geometry_columns')): cur.execute("DROP TABLE " + table[0] + " cascade;") f = file(file_name, 'r') fp = file('Pkeys.sql', 'w+') ff = file('Fkeys.sql', 'w+') tabs = [] print "Filling tables..." for line in f: #prochazime vstupni soubor line = line.replace('"', '\'') #nahradi " -> ' if line[1] == 'H': #if radek je HLAVICKA h = line.split(';')[0][2:] #ulozi nazev tabulky do 'h' elif line[1] == 'B': #if uvozujici radek BLOKU b = line.split(';')[0][2:] #ulozi nazev tabulky do 'b' sql = ProcessB(line) #vytvori SQL dotaz try: cur.execute(sql) #SQL dotaz vytvori prazdnou tabulku except: print 'create table ' + b tabs.append(b) #prida nazev tabulky do 'tabs' elif line[1] == 'D': #if radka obsahuje DATA d = line.split(';')[0][2:] try: cur.execute(ProcessD(line)) #SQL dotaz naplni tabulku except: pass for i in tabs: try: fp.write(set_Pkeys(i)) except: print "chyba Primary " + i, sys.exc_type, sys.exc_value for i in tabs: try: ff.write(set_Fkeys(i)) except: print "chyba Foreign " + i, sys.exc_type, sys.exc_value fp.close() ff.close() f.close() fp = file('Pkeys.sql', 'r') ff = file('Fkeys.sql', 'r') print "Primary Keys..." for line in fp: try: cur.execute(line) except: print line foo = 0 fline = '' print "Foreign Keys..." for line in ff: fline = fline + line foo = foo + 1 if foo % 2 == 0: try: cur.execute(fline) except: print line fline = '' fp.close() ff.close() print "Geometry HP..." AddGeometryL(db, 'hp', cur) print "Geometry OB..." AddGeometryL(db, 'ob', cur) print "Geometry DPM..." AddGeometryL(db, 'dpm', cur) print "Geometry PAR..." AddGeometryP(db, 'par', 'hp', cur) print "Geometry BUD..." AddGeometryP(db, 'bud', 'ob', cur) AddColumn(cur, 'par', 'drupoz_nazev', 'varchar(60)', 'drupoz', 'nazev', 'kod', 'drupoz_kod') AddColumn(cur, 'par', 'zpvypo_nazev', 'varchar(60)', 'zpvypo', 'nazev', 'kod', 'zpvypa_kod') SetUpParcelNumber(cur) SetUpBuildingNumber(cur) print "Complete."
def Conectar( self ) : return odbc.odbc('DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s' %self._dbnom )
import odbc import datetime # db connection connect = odbc.odbc('oasis') db = connect.cursor() db.execute("select ranking,title,(current_players), peak_today, date from oasis.player_count ") result = db.fetchall() db.execute("select title from oasis.player_count group by title") tt = db.fetchall() info = [] titles = [] for re in result: info.append({ 'current_players': re[2], 'ranking': re[0], 'title': re[1], 'date': re[4] }) for t in tt: titles.append(t[0]) weekday = { 'Monday': 0, 'Tuesday': 0, 'Wednesday': 0, 'Thursday': 0,
import sys from time import sleep import pygame from kame import Kame from frieza import Frieza import odbc db = odbc.odbc('adventureworks2012/billb/billb90') cursor = db.cursor() def check_keydown_events(event, ai_settings, screen, goku, kames): """Respond to keypresses.""" if event.key == pygame.K_RIGHT: goku.moving_right = True elif event.key == pygame.K_LEFT: goku.moving_left = True elif event.key == pygame.K_SPACE: fire_kame(ai_settings, screen, goku, kames) elif event.key == pygame.K_q: sys.exit() def check_keyup_events(event, goku): """Respond to key releases.""" if event.key == pygame.K_RIGHT: goku.moving_right = False elif event.key == pygame.K_LEFT:
# -*-coding:utf-8 -*- import odbc, dbi # 导入ODBC模块和驱动程序 import time db = odbc.odbc("addresses/scott/tiger") # 实例 cursor = db.cursor() # 产生cursor游标 cursor.execute("select *from address order by id desc") for col in cursor.description: # 显示行描述 print col[0], col[1] result = sursor.fetchall() for row in result: # 输出各字段的值 print row print row[1], row[2] timeTuple = time.localtime(row[3]) print time.strftime("%Y/%m/%d", timeTuple)
import odbc, dbi # 导入ODBC模块和驱动程序 import time db = odbc.odbc('addresses/scott/tiger') # 打开数据库连接 curser = db.cursor() # 产生cursor游标 curser.execute("select * from address order by id desc") for col in curser.description: # 显示行描述 print (col[0], col[1]) result = curser.fetchall() for row in result: # 输出各字段的值 print (row) print (row[1], row[2]) timeTuple = time.localtime(row[3]) print (time.strftime('%Y/%m/%d', timeTuple))
def fetch_patient_info(config_file, config_dict=None, extra_elems_dict=None, criteria_arg=''): """Fetch patient information from various source(configuration files, database) with a user-defined behavior. config_file: Configuration file name. config_dict: This dictionary is used to initialize the configparser.ConfigParser object. You can use this arguement to specify initial value of those keywords listed in `fields_options`. extra_elems_dict: Specify additional elements that are not existing in `fields_options`. criteria_arg: Criteria argument used to generate SQL statement. """ config = _read_config_file(config_file, config_dict, extra_elems_dict) if not config: raise RuntimeError('Failed to load configuration file') cached_cfg_parsers = {} def get_value_from_cfg(*, file, encoding, section, option): if file not in cached_cfg_parsers: config_parser = configparser.ConfigParser() config_parser.optionxform = str config_parser.read(file, encoding=encoding if encoding else _get_file_encoding(file)) cached_cfg_parsers[file] = config_parser return cached_cfg_parsers[file][section][option] ds = dicom.dataset.Dataset() info_res = [] info_callbacks = [] # The format of configuration information can be one of these three forms: # 1. Raw information(enclosed by square brackets); info_res.append(re.compile(r'^\[(.+)\]$')) info_callbacks.append(lambda keyword, matched_res: setattr(ds, keyword, matched_res.group(1))) # 2. Information that comes from a INI-likely configuration file(consist of four parts); info_res.append(re.compile(r''' ^(?P<file>.+?) # Part 1: configuration file path (?:\|(?P<encoding>.+))? # Part 2(if exists): the encoding of the configuration file \[(?P<section>.+)\] # Part 3: a section name enclosed by square brackets (?P<option>.+)$ # Part 4: option name ''', re.VERBOSE)) info_callbacks.append(lambda keyword, matched_res: setattr(ds, keyword, get_value_from_cfg(**matched_res.groupdict()))) # 3. Information that comes from a database(table name and field name are split by a dot); info_res.append(table_field_re_strict) info_from_db = dict() info_callbacks.append(lambda keyword, matched_res: info_from_db.update({keyword: matched_res.group()})) for keyword in config[fields_section]: value = config[fields_section][keyword] if value: for compiled_re, callback in zip(info_res, info_callbacks): matched_res = compiled_re.match(value) if matched_res: callback(keyword, matched_res) debug_print = lambda *args, **kwargs: print(*args, **kwargs) if __debug__ else None # Fetch patient information from database conn_str = _create_connection_string(config) debug_print(conn_str) with contextlib.closing(odbc.odbc(conn_str)) as connection: with contextlib.closing(connection.cursor()) as cursor: for sql, keywords in zip(*_create_sql_statements(config, info_from_db, criteria_arg)): debug_print(sql) debug_print(keywords) if sql and keywords: cursor.execute(sql) record = cursor.fetchone() if record: for keyword, value in zip(keywords, record): setattr(ds, keyword, _correct_value(keyword, value)) debug_print(ds) return ds
2) DNS: PRDWDB02 Server: PR-DWDB-02.EHPR.CORP.EVOLENTHEALTH.COM Databases: STAGING,PRELOAD,EVH_LOAD,LOGGING,IDA,IDA_RS Default Database: STAGING 3) DNS: IPE1PRDWDB002 Sever: IPE1ST-DWDB-002.EHPR.CORP.EVOLENTHEALTH.COM Databases: STAGING,PRELOAD,EVH_LOAD,LOGGING,IDA,IDA_RS Default Database: STAGING """ 'Import required libraries 'This is the interface between the database driver and Python import odbc """con = odbc.odbc('PRDWDB02/EVOLENTHEALTH\\PShirbhate/9960721599P$') """ 'Connection with windows authentification con = odbc.odbc('PRDWDB02') """ Connection without windows authentification import pyodbc con = pyodbc.connect('PRDWDB02') con = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};' + 'Server={PR-DWDB-02.EHPR.CORP.EVOLENTHEALTH.COM};' + 'port= 1433:'+ 'Database={STAGING};' + 'Uid= {PShirbhate};' + 'Pwd={9960721599P$};') """ import pandas as pd 'List Databases on the server databases = pd.read_sql_query("SELECT name FROM master.dbo.sysdatabases",con)
def connect(dsn, username=None, password=None): """Create a connection to the specified database. This utility method attempts to connect to the database named by dsn using the (optional) username and password provided. The method attempts to use a variety of connection frameworks (PyDB2, pyodbc, IBM's official DB2 driver, PythonWin's ODBC stuff and mxODBC) depending on the underlying platform. Note that the queries in the methods below are written to be agnostic to the quirks of the various connection frameworks (e.g. PythonWin's ODBC module doesn't correctly handle certain dates hence why all DATE and TIMESTAMP fields are CAST to CHAR in the queries below). """ logging.info('Connecting to database "%s"' % dsn) # Try the "official" IBM DB2 Python driver try: import ibm_db import ibm_db_dbi except ImportError: pass else: logging.info('Using IBM DB2 Python driver') if username is not None: return ibm_db_dbi.connect(dsn, username, password) else: return ibm_db_dbi.connect(dsn) # Try the PyDB2 driver try: import DB2 except ImportError: pass else: logging.info('Using PyDB2 driver') if username is not None: return DB2.connect(dsn, username, password) else: return DB2.connect(dsn) # Try the pyodbc driver try: import pyodbc except ImportError: pass else: logging.info('Using pyodbc driver') # XXX Check whether escaping/quoting is required # XXX Should there be a way to specify the driver name? Given that on # unixODBC the driver alias is specified in odbcinst.ini, and on # Windows with DB2 9+ one can have multiple DB2 ODBC drivers installed # with differentiating suffixes if username is not None: return pyodbc.connect( 'driver=IBM DB2 ODBC DRIVER;dsn=%s;uid=%s;pwd=%s' % (dsn, username, password)) else: return pyodbc.connect('driver=IBM DB2 ODBC DRIVER;dsn=%s' % dsn) # Try the PythonWin ODBC driver try: import dbi import odbc except ImportError: pass else: logging.info('Using PyWin32 odbc driver') if username is not None: # XXX Check whether escaping/quoting is required return odbc.odbc("%s/%s/%s" % (dsn, username, password)) else: return odbc.odbc(dsn) raise ImportError( 'Unable to find a suitable connection framework; please install PyDB2, pyodbc, PyWin32, or mxODBC' )
def insertNote(dbtrid,phone): con = odbc.odbc('DM3/DM/********') cur = con.cursor() note = "client Phone Added %s" % phone iNote = "INSERT INTO DBTRACT(debtor_id, item_no, act_date, user_id, comments,user_id_stamp,gmt_offset,act_time) VALUES(%d, NULL, '2012-04-04', 'TJK', '%s', 'TJK', 0 , '12:00:00')" % (dbtrid, note) cur.execute(iNote)
def start(self): ''' Create connection to server. ''' self._sql = odbc.odbc(self._dsn)
def init_db_conn(connect_string, username, passwd, show_connection_info, show_version_info=True): """initializes db connections, can work with PyGres or psycopg2""" global _CONN try: dbinfo = connect_string if show_connection_info: print(dbinfo) if USE_JYTHON: _CONN = zxJDBC.connect(connect_string, username, passwd, 'org.postgresql.Driver') elif '/' in connect_string: import odbc _CONN = odbc.odbc(connect_string) if show_connection_info: print(_CONN) elif connect_string.startswith('Driver='): import pyodbc # Driver={PostgreSQL};Server=IP address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword; # Driver={PostgreSQL};Server=isof-test64;Port=5435;Database=isof_stable;Uid=postgres;Pwd=postgres; _CONN = pyodbc.connect(connect_string) if show_connection_info: print(_CONN) else: # 'host:[port]:database:user:password' arr = connect_string.split(':') if len(arr) > 4: host = '%s:%s' % (arr[0], arr[1]) port = int(arr[1]) dbname = arr[2] user = arr[3] passwd = arr[4] elif len(arr) == 4: host = arr[0] port = -1 dbname = arr[1] user = arr[2] passwd = arr[3] else: raise exceptions.ImportError('Incorrect connect_string!\n\n%s' % (USAGE)) if port > 0: host = host.split(':')[0] sport = 'port=%d' % (port) else: sport = '' dsn = "host=%s %s dbname=%s user=%s password=%s" % (host, sport, dbname, user, passwd) if show_connection_info: print(dsn) dbinfo = 'db: %s:%s' % (host, dbname) use_pgdb = 0 try: import psycopg2 except: try: import pgdb use_pgdb = 1 except: raise exceptions.ImportError('No PostgreSQL library, install psycopg2 or PyGres!') if not _CONN: if show_connection_info: print(dbinfo) if use_pgdb: _CONN = pgdb.connect(database=dbname, host=host, user=user, password=passwd) if show_connection_info: print(_CONN) else: _CONN = psycopg2.connect(dsn) if show_connection_info: print(_CONN) if show_version_info: add_ver_info(connect_string, username) except: ex = sys.exc_info() s = 'Exception: %s: %s\n%s' % (ex[0], ex[1], dbinfo) print(s) return None return _CONN
def conn(): return odbc.odbc(CONNECTION_STRING)
def insertPhones(dbtrid, phone, desc, status): con = odbc.odbc('DM3/DM/********') cur = con.cursor() item_no = phoneItemNum(dbtrid) insertPhone = "INSERT INTO dbtrphon(debtor_id, item_no, phone, descr, status) VALUES (%d, %d, '%s', '%s', '%s')" % (dbtrid, item_no, phone, desc, status) cur.execute(insertPhone)
return unicode(result) #try: dbsx = sxDBStorage() dbsx.ConnectMaps() print "connect" for url in news_sources.keys(): #if news_sources[url] != "fires_eng": # continue try: fires_lenta = feedparser.parse(url) conn = odbc.odbc( "Driver={SQL Server Native Client 10.0};Server=___;Failover_PartnerPartner=___;Database=___;Uid=___;Pwd=___" ) cur = conn.cursor() for entry in fires_lenta.entries: str_date = time.strftime('%d/%m/%Y %H:%M:%S', entry.updated_parsed) print str_date type = news_sources[url] if hasattr(entry, 'georss_point'): coords = entry.georss_point.split(' ') entry.geo_long = coords[0] entry.geo_lat = coords[1] print "1" if hasattr(entry, 'geo_long'): print "2"
import odbc import sqlite3 as lite con1 = lite.connect('clientMatch.db') con2 = odbc.odbc('DM3/DM/*******') def createTable(): with con1: cur1 = con1.cursor() createTable = "CREATE TABLE clientDM3(dbtr_id INT, ssn INT, state TEXT, ran INT)" cur1.execute(createTable) print 'Table Created' def getDM3(): print 'Fetching Results' cur2 = con2.cursor() clientSelect = "SELECT debtor_id, ssn1, cur_state FROM dbtr401_view WHERE n_clt_id in ('6001','6002') AND ssn1 <> '' AND cur_state in ('nv','ca')" cur2.execute(clientSelect) results = cur2.fetchall() cur2.close() print 'Fetch Complete' return results def insertLite(results): for row in results: dbtrid = int(row[0]) ssn = row[1]