def query (query_string, **command_properties): """Auxiliary function to serve as a quick-and-dirty wrapper round an ADO query """ command = EnsureDispatch ("ADODB.Command") command.ActiveConnection = connection () # # Add any client-specified ADO command properties. # NB underscores in the keyword are replaced by spaces. # # Examples: # "Cache_results" = False => Don't cache large result sets # "Page_size" = 500 => Return batches of this size # "Time Limit" = 30 => How many seconds should the search continue # for k, v in command_properties.items (): command.Properties (k.replace ("_", " ")).Value = v command.CommandText = query_string recordset, result = command.Execute () while not recordset.EOF: yield ADO_record (recordset) recordset.MoveNext ()
CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; data Source=%s" % \ DATABASE_FILEPATH if os.path.exists (DATABASE_FILEPATH): os.remove (DATABASE_FILEPATH) adox = Dispatch ("ADOX.Catalog") adox.Create (CONNECTION_STRING) adox = None db = Dispatch ('ADODB.Connection') db.Open (CONNECTION_STRING) print 'made it to here' try: ## db.Execute ('CREATE TABLE dtest (id INT, data INT)') ## db.Execute ('INSERT INTO dtest (id, data) VALUES (1, 2)') db.Execute ('CREATE TABLE combo(rowid INT, value2 INT, count2 INT, evt INT, evc INT, evh INT)') ## db.Execute ('INSERT INTO combo(rowid, value2, count2, evt, evc, evh) VALUES (1, 2, 3, 4, 5, 6)') ## print' it worked' for line in combofile: print 'iterating' items = line.split(',') first = items[0] second = items[1] third = items[2] forth = items[3] fifth = items[4] sixth = items[5] databasecode = 'INSERT INTO combo(rowid, value2, count2, evt, evc, evh) VALUES (%s, %s, %s, %s, %s, %s)' %(first, second, third, forth, fifth, sixth)
DATABASE_FILEPATH = r"c:\temp\test.accdb" CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; data Source=%s" % \ DATABASE_FILEPATH if os.path.exists(DATABASE_FILEPATH): os.remove(DATABASE_FILEPATH) adox = Dispatch("ADOX.Catalog") adox.Create(CONNECTION_STRING) adox = None db = Dispatch('ADODB.Connection') db.Open(CONNECTION_STRING) try: db.Execute('CREATE TABLE dtest (id INT, data INT)') db.Execute('INSERT INTO dtest (id, data) VALUES (1, 2)') except: print 'whoops' ## try: ## db.Execute ('SELECT id, newdata FROM dtest') ## except: ## print "FAILED as expected" ## else: ## print "SUCCEEDED unexpectedly" ## ## try: ## db.Execute ('SELECT id, data FROM dtest') ## except: ## print "FAILED unexpectedly" ## else: