def createDatabase(): """ Crée la bdd, les tables et vide les tables si la bdd existe déjà """ file = getConfig("General", "home_dir") + "woocommerce.mdb" if (os.path.exists(file)): connection = pypyodbc.win_connect_mdb(file) #vide la bdd cur = connection.cursor() cur.execute("DELETE FROM woocommerce_produit").commit() cur.execute("DELETE FROM woocommerce_meta").commit() cur.execute("DELETE FROM woocommerce_commande").commit() cur.close() return connection else: pypyodbc.win_create_mdb(file) connection = pypyodbc.win_connect_mdb(file) #NumCommande est l'id de la commande fields = ''' Id_Commande LONG, NumCommande LONG, StatutCommande VARCHAR(20), Date_creation DATETIME, Prenom VARCHAR(25), Nom VARCHAR(25), Adresse VARCHAR(150), CodePostal VARCHAR(5), Ville VARCHAR(50), Tel VARCHAR(20), Email VARCHAR(50), Type_Paiement VARCHAR(30), Date_Paiement DATETIME, Date_Termine DATETIME, Commentaire VARCHAR(200) ''' connection.cursor().execute('CREATE TABLE woocommerce_commande (%s);' % fields).commit() fields = ''' Id_Produit INTEGER, Quantite BYTE, Id_Commande LONG ''' connection.cursor().execute('CREATE TABLE woocommerce_produit (%s);' % fields).commit() fields = ''' key_meta VARCHAR(30), valeur_meta VARCHAR(30), Id_Commande LONG ''' connection.cursor().execute('CREATE TABLE woocommerce_meta (%s);' % fields).commit() return connection
def read_db(): db_path = os.getcwd() + '/DB.mdb' db = pypyodbc.win_connect_mdb(db_path) sql = 'SELECT address_full\ FROM buildings;' cur = db.cursor() cur.execute(sql) rows = cur.fetchall() id_ = 1 driver = webdriver.Chrome( r'C:/Users/acer2/Desktop/Работа/licenseParse/test/chromedriver.exe') driver.get("https://yandex.ru/maps/") for row in rows: res = find_coord(row, driver)[0] print(res) if len(res) == 1: id_ += 1 continue lat = res[0] lon = res[1] #sql1 = f"INSERT INTO address_full (latitude, longitude)\ # VALUES({lat}, {lon})\ # WHERE ID = {id_};" sql1 = "UPDATE buildings SET latitude = ?, longitude = ? WHERE ID = ?;" cur.execute(sql1, (lat, lon, id_)) db.commit() id_ += 1 driver.close()
def parse_html(): db = pypyodbc.win_connect_mdb(dbpath) for roots, dirs, files in os.walk(htmlpath): for file in files: fpath = roots + '/' + file with open(fpath,encoding='utf-8')as f: text = f.read() html = json.loads(text) r_list = html['Data']['Sucess'] for dicts in r_list: journal_id = dicts['PYKM'] journal_name = dicts['C_NAME'] CBPT = dicts['Url2'] if CBPT == None: CBPT = '' url = dicts['Url1'] if url == None: url = '' issn = dicts['ISSN'] cn = dicts['CN'] sql = """insert into info(journal_id,journal_name,CBPT,官网,issn,cn) values(?,?,?,?,?,?)""" message = (journal_id, journal_name, CBPT, url, issn, cn) curser = db.cursor() curser.execute(sql,message) curser.commit() print('%s插入成功' % journal_name)
def update_db(DataBase, res_buildings, split_addr): db = pypyodbc.win_connect_mdb(DataBase) db.cursor().execute('CREATE TABLE buildings2(\ ID INT PRIMARY KEY,\ address_id INT,\ isDestroyed VARCHAR(5),\ address_full VARCHAR(255),\ latitude VARCHAR(255),\ longitude VARCHAR(255),\ FOREIGN KEY (address_id) REFERENCES address(ID));') db.cursor().execute('CREATE TABLE split_address2(\ ADR_ID INT,\ ind VARCHAR(50),\ country VARCHAR(50),\ region VARCHAR(255),\ locality VARCHAR(255),\ street_house VARCHAR(255),\ other VARCHAR(255),\ FOREIGN KEY (ADR_ID) REFERENCES address(ID));') ID = 1 res_buildings = list(res_buildings) for i in range(len(res_buildings)): db.cursor().execute("INSERT INTO buildings2 VALUES(?, ?, ?, ?, ?, ?)", (ID, res_buildings[i].split(';')[0], 'False', \ res_buildings[i].split(';')[1], res_buildings[i].split(';')[2], res_buildings[i].split(';')[3])) db.cursor().execute("INSERT INTO split_address2 VALUES(?, ?, ?, ?, ?, ?, ?)", (res_buildings[i].split(';')[0], split_addr[i][0]['ind'],\ 'Россия', split_addr[i][0]['region'], split_addr[i][0]['locality'], split_addr[i][0]['street_house'], split_addr[i][0]['other'])) ID += 1 db.commit()
def parsel_html(): db = pypyodbc.win_connect_mdb(dbpath) for roots, dirs, files in os.walk(html_path): for file in files: fpath = roots + '/' + file all_ = fpath.replace("E:\work\post_card\yb21\html\\", '').replace("/1.html", '') all_list = all_.split('\\') sheng = all_list[0] shi = all_list[1] qu = all_list[2] try: with open(fpath, encoding='gb18030') as f: text = f.read() except: with open(fpath, encoding='GB2312') as f: text = f.read() html = Selector(text, 'html') post = "" post_list = html.xpath("//tr/td/strong/a/text()").extract() for item in post_list: post += item + ';' sql = "insert into post_card(省,市,区县,邮编) values('%s','%s','%s','%s')" % ( sheng, shi, qu, post) curser = db.cursor() curser.execute(sql) curser.commit() print('%s插入成功' % qu)
def create_db(path_to_directory): db_path = path_to_directory + '/DB.mdb' if os.path.exists(db_path): os.remove(db_path) pypyodbc.win_create_mdb(db_path) db = pypyodbc.win_connect_mdb(db_path) return db
def onClickedButtonRead(self): path = r'C:\Users\VULCAN\Desktop\ten\acc1.accdb' # 数据库文件 strr = 'Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=' + (path) db = pypyodbc.win_connect_mdb(strr) curser = db.cursor() # curser.execute("select * from MSysAccessStorage order by id desc") # 查询的sql语句 sql = "SELECT * FROM sensors" curser.execute(sql) # 获取查询到的数据, 是以二维元组的形式存储的, 所以读取需要使用 data[i][j] 下标定位 ndata = curser.fetchall() # 打印测试 # print(ndata) model = QStandardItemModel() title = ['温度', 'ph值', '浊度', '电导率', '采集时间'] model.setHorizontalHeaderLabels(title) for row, linedata in enumerate(ndata): for col, itemdata in enumerate(linedata): item = QStandardItem( str(itemdata)) if itemdata != None else QStandardItem('') model.setItem(row, col, item) # tableView.setModel(model) self.tableWidget.setModel(model) curser.close() db.close()
def loadTLM(self): _ret = QtGui.QFileDialog.getOpenFileName(self, "Open TLM", "", "TLM (*.Tlm)") if (_ret == ""): return self.ui.lineEdit.setText(_ret) self.inputFile = _ret self.outputFile = self.inputFile+'3' if os.path.isfile(self.outputFile): _s = "new TLM3 " + self.outputFile + " already exists" QtGui.QMessageBox.information(self, 'TMV3', _s, QtGui.QMessageBox.Ok) return _conI = pypyodbc.win_connect_mdb(self.inputFile) _curI = _conI.cursor() _curI.execute('SELECT [tTitle],[tComment],[lLimitID] from Limits') _ret = _curI.fetchall() print(_ret) self.tlmCount = 0 for _f in _ret: self.addItem(self.tlmCount,_f[0],_f[0],'1.0',_f[1]) _curI.execute('SELECT [dXValue],[dYValue] from LimitValues WHERE [lLimitID]={0}'.format(str(_f[2]))) _list = _curI.fetchall() self.valueLists.append(_list) self.tlmCount +=1 pass self.saveFlag = True
def mudel(self,mu,tablename): dbb = pypyodbc.win_connect_mdb(self.db1) cur = dbb.cursor() mulis=() alllis="where " if mu: for v,v1 in mu.items: if isinstance(mu[v],int) or isinstance(mu[v],float): mu=str(v)+"="+str(mu[v]) else: st=str(mu[v]) if re.search(self.re,st): st=re.sub(self.re,replace1,st) st=st mu = str(v)+"='"+st+"'" mulis = mulis+(mu,) alllis = alllis+(' and ').join(mulis) else: alllis="" x=" delete from %s " % tablename x=x+alllis print(x) req = cur.execute(x) print(req) cur.close() dbb.close() return req
def parse_index(): db = pypyodbc.win_connect_mdb(dbpath) dict_sum = dict() with open(indexpath, encoding='utf-8') as f: text = f.read() html = Selector(text, 'html') p = html.xpath("//div[@class='TRS_Editor']/p/text()").extract() for item in p: xx_zy = item.replace("\u3000", '') if xx_zy == "": continue xx = xx_zy.split(":")[0] zy_all = xx_zy.split(":")[1] zy_list = zy_all.split("、") for zy in zy_list: zy = zy.replace("(自定)", "") if zy in dict_sum.keys(): xx_ = dict_sum[zy] xx_ = xx_ + ';' + xx dict_sum[zy] = xx_ else: dict_sum[zy] = xx for key, value in dict_sum.items(): sql = "insert into data(学科名称,建设学校) values ('%s','%s')" % (key, value) curser = db.cursor() curser.execute(sql) curser.commit() print('%s插入成功' % key)
def update(self,whe2,list,tablename): dbb = pypyodbc.win_connect_mdb(self.db1) cur = dbb.cursor() req =False for i,whe in enumerate(whe2): s = () s1=() for k,v in whe.items(): if isinstance(v,int) or isinstance(v,float): s=s+(str(k)+"="+str(v),) else: s = s+(str(k)+"="+"'"+str(v)+"'",) for k1,v1 in list[i].items(): if isinstance(v1,int) or isinstance(v1,float): s1 = s1+(str(k1)+"="+str(v1),) else: s1 = s1+(str(k1)+"="+"'"+str(v1)+"'",) try: st ="update %s set %s where %s" % (tablename,','.join(s),' and '.join(s1)) print(st) cur.execute(st) req = True dbb.commit() except: dbb.rollback() req =False print("error") cur.close() dbb.close return req
def muselect(self,mu,tablename): dbb = pypyodbc.win_connect_mdb(self.db1) cur = dbb.cursor() mulis=() alllis="where " mure=() mure=mure+(tablename,) if mu: print(mu) for v,v1 in mu.items(): if isinstance(mu[v],int) or isinstance(mu[v],float): mu=str(v)+"="+str(mu[v]) else: st=str(mu[v]) #if re.search(self.re,st): #st=re.sub(self.re,replace1,st) mu = str(v)+"='"+st.replace('\'','\'\'')+"'" mulis = mulis+(mu,) alllis = alllis+(' and ').join(mulis) else: alllis="" x=" select * from %s " % tablename x=x+alllis print(x) try: req = cur.execute(x) info = cur.fetchall() except Exception as Argument: print (Argument) info=[] cur.close() dbb.close return info
def save(self, filename_path): self.preprocess() # self.df = self.df.head(2) # for testing if self.health_center == 'COD': try: self.df.MCMOnlyUnit.head(1) except AttributeError: self.append_text("You chose the Dorchester House Excel file for Codman.") raise elif self.health_center == 'DOR': try: self.df.MCMwithTransOnlyUnit.head(1) except AttributeError: self.append_text("You chose the Codman Excel file for Dorchester House.") raise self.check_moms_name(self.df) self.df.drop_duplicates(inplace=True) db = pypyodbc.win_connect_mdb(filename_path) c = db.cursor() try: write_to_ClientReport(self.df, c) write_to_ClientReportService(self.df, c, self.health_center) except pypyodbc.IntegrityError: self.append_text("Check the Excel file. An SSN for one of the rows may be missing, where it should not.") raise finally: db.close()
def __init__(self): self.headers = { "Accept": "*/*", "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36", } # 读取配置文件获取mdb数据库地址,链接数据库 self.DBPATH = toml.load('config.toml')['Dbpath'] self.db = pypyodbc.win_connect_mdb(self.DBPATH) # 读取配置文件获取代理列表 self.redisHost = toml.load('config.toml')['redisHost'] self.redisPort = toml.load('config.toml')['redisPort'] self.redisDb = toml.load('config.toml')['redisDb'] self.r = redis.StrictRedis(host=self.redisHost, port=self.redisPort, db=self.redisDb, decode_responses=True) self.proxieslist = list(self.r.smembers('proxy_acs')) # 读取mysql配置 self.DBHOST = toml.load('config.toml')['DBHost'] self.DBPORT = toml.load('config.toml')['DBPort'] self.DBUSER = toml.load('config.toml')['DBUser'] self.DBPWD = toml.load('config.toml')['DBPwd'] self.DB = toml.load('config.toml')['DB'] # 链接mysql数据库 self.conn = pymysql.connect(self.DBHOST, self.DBUSER, self.DBPWD, self.DB) # 创建详情url的队列 self.detail_url_que = Queue()
def __init__(self, mdbFile, pg_con_string): if isWindows(): self.ac_con=pypyodbc.win_connect_mdb(mdbFile) self.ac_cur = self.ac_con.cursor() self.pg_con = psycopg2.connect(pg_con_string) self.pg_cur = self.pg_con.cursor() self.SQL="SET client_encoding = 'UTF8';\n"
def CreateAccessDatabase(self, fname): pypyodbc.win_create_mdb(fname) connection = pypyodbc.win_connect_mdb(fname) connection.cursor().execute( 'CREATE TABLE t1 (id COUNTER PRIMARY KEY, name CHAR(25));').commit( ) connection.close()
def connectDB(self): driver = 'Driver={Microsoft Access Driver (*.mdb,*.accdb)};PWD=' + self.password + ";DBQ=" + self.db_name try: self.conn = pypyodbc.win_connect_mdb(driver) return True except: print("connection is false") return False
def conn(): filepath = util.get_current_path() + "\license.ini" conf = ConfigParser.ConfigParser() res = conf.read(filepath) mdb = conf.get("config", "dbpath") str = 'Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=' + mdb + ';' #str = 'Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=C:\Users\QN\PycharmProjects\inbody\LookinBody.mdb;' con = pypyodbc.win_connect_mdb(str) return con
def copy_table(src_mdb_path: str, src_table, dst_mdb_path: str, dst_table): columns = [] values = [] with pypyodbc.win_connect_mdb(src_mdb_path) as db: with db.cursor() as cursor: for row in cursor.columns(table=src_table): columns.append(row[3]) cursor.execute(f"select * from {src_table}") for row in cursor: values.append(row) with pypyodbc.win_connect_mdb(dst_mdb_path) as db: with db.cursor() as cursor: for row in values: sql = f"insert into {dst_table}({join_columns(columns)}) values({join_values(row)});" cursor.execute(sql) print(sql) print(columns)
def db_connect(**kwargs): ''' Takes DSN='connection name' or MDB='path to access file' returns a connection to the specified database. throws Keyerror if you provide the wrong thing. Throws pypyodbc connect error if connection string is not valid. ''' if 'DSN' in kwargs: return db.connect('DSN=' + kwargs['DSN'], autocommit=True) if 'MDB' in kwargs: try: return db.win_connect_mdb(kwargs['MDB']) except: pass #try creating it then connecting db.win_create_mdb(kwargs['MDB']) return db.win_connect_mdb(kwargs['MDB']) else: raise KeyError('Provide either a DNS keyword or MDB keyword')
def mdb_conn(db_name, password=""): """ 功能:创建数据库连接 :param db_name: 数据库名称 :param db_name: 数据库密码,默认为空 :return: 返回数据库连接 """ str = 'Driver={Microsoft Access Driver (*.mdb)};PWD' + password + ";DBQ=" + db_name conn = pypyodbc.win_connect_mdb(str) return conn
def get_db(): '''连接数据库''' if 'db' not in g: DRIVER = 'Microsoft Access Driver (*.mdb)' DBQ = current_app.config['DATABASE1'] PWD = '123456' #密码明示,应优化 pypyodbc.lowercase = False g.db = pypyodbc.win_connect_mdb('Driver={};DBQ={};PWD={}'.format( DRIVER, DBQ, PWD)) return g.db
def fetchFromDB(grade, department, entranceYear): try: conn = pypyodbc.win_connect_mdb('./XLDB.mdb') cur = conn.cursor() tree2.delete(*tree2.get_children()) query = 'SELECT * FROM Users' needsAnd = False vals = [] if grade.get() != 'all' or department.get() != 'all' or entranceYear.get() != 'all': query += ' WHERE' if grade.get() != 'all': needsAnd = True query += " Grade=?" vals.append(grade.get()) if department.get() != 'all': if needsAnd: query += " AND Department=?" vals.append(department.get()) else: query += " Department=?" vals.append(department.get()) needsAnd = True if entranceYear.get() != 'all': if needsAnd: query += " AND EntranceYear=?" vals.append(int(entranceYear.get())) else: query += " EntranceYear=?" vals.append(int(entranceYear.get())) query += ';' global usersStdnums usersStdnums = {} cur.execute(query, vals) data = cur.fetchall().copy() if data: selectLabel.configure( text='Successfully fetched data from database.', foreground='green') for row in data: tree2.insert('', 0, text=str(row[15]), iid=str(row[15]), tag='green') usersStdnums[str(row[15])] = str(row[15]) quotaLF2.state(['!disabled']) for widget in quotaLF2.winfo_children(): widget.state(['!disabled']) treeLF2.state(['!disabled']) for widget in treeLF2.winfo_children(): widget.state(['!disabled']) updateB.state(['!disabled']) else: messagebox.showinfo(title='No result', message='The database returned no result.') cur.close() conn.close() except Exception as er: messagebox.showinfo(title='No result', message='The database returned no result.')
def InitptableList(): dataDBfile = RootPath.decode('gbk') + '\\data\\data.mdb' ptableList = [] if os.path.exists(dataDBfile): conn = pypyodbc.win_connect_mdb( r'DRIVER={Microsoft Access Driver (*.mdb *.accbd)};DBQ=' + dataDBfile) cur = conn.cursor() sql = "select * from base_pricetable where deleted=False order by 材料".encode( 'gbk') cur.execute(sql) pricetableallu = cur.fetchall() if pricetableallu != []: typelist = [] for tup in cur.description: typelist.append(tup[0]) # 编码gbk for i in range(0, len(pricetableallu)): fjo = getfjo(i, typelist, pricetableallu) ptable = PriceTable() ptable.name = fjo['报价方案'.encode('gbk')] ptable.mat = fjo['材料'.encode('gbk')] ptable.price1 = fjo['价格1'.encode('gbk')] ptable.price2 = fjo['价格2'.encode('gbk')] ptable.bh = Nonetonumber(fjo['bh']) ptable.cost = fjo['加工费用'.encode('gbk')] ptable.price_exp1 = fjo['price_exp1'] ptable.price_exp2 = fjo['price_exp2'] ptableList.append(ptable) return ptableList
def getMaxData(dbpath): sql = 'select top 1 测量时间 as testdate,ID from 测量数据表1 order by ID desc' conn = 'Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=%s' % dbpath db = pypyodbc.win_connect_mdb(conn) cur = db.cursor() cur.execute(sql) values = cur.fetchall() cur.close() db.commit() db.close() return values
def InitErpList(): #erp.mdb and snimay.mdb erpDBfile = RootPath.decode('gbk') + '\\Plugins\\erp.mdb' gErpItemList = [] if not os.path.exists(erpDBfile): if not os.path.exists(RootPath.decode('gbk') + 'snimay.mdb'): return gErpItemList erpDBfile = RootPath.decode('gbk') + '\\Plugins\\snimay.mdb' if os.path.exists(erpDBfile): conn = pypyodbc.win_connect_mdb( r'DRIVER={Microsoft Access Driver (*.mdb *.accbd)};DBQ=' + erpDBfile) cur = conn.cursor() sql = "select * from mat order by id" cur.execute(sql) u1 = cur.fetchall() for u in u1: p = {} p['deleted'] = False p['id'] = u[0] p['name'] = u[1] p['mat'] = u[2] p['color'] = u[3] p['h'] = u[4] p['flag'] = u[5] p['myclass'] = u[6] p['myunit'] = u[7] gErpItemList.append(p) return gErpItemList
def read(self,sourceDB): _conq = pypyodbc.win_connect_mdb(sourceDB) _curq = _conq.cursor() _curq.execute('SELECT [tTitle],[tComment],[lLimitID] from LIMITS') x = _curq.fetchall() for n in x: _curq.execute('SELECT [dXValue],[dYValue] from LIMITVALUES where [lLimitID] = {0} ORDER by [dXValue]'.format(str(n[2]))) y = _curq.fetchall() print(x,y) _conq.close()
def __init__(self): self.dbpath = r'E:\work\verify_url\中外期刊信息库201900906 - 副本.mdb' self.output = r'E:\work\verify_url\many' self.headers = { 'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.835.163 Safari/535.1', } self.proxy = { "HTTP": "192.168.30.172:9172", } self.count = 0 self.db = pypyodbc.win_connect_mdb(self.dbpath) self.message_queue = Queue()
def __init__(self): r""" __init__重写,初始化 """ self.count = 0 self.baseurl = "https://ascelibrary.org" self.headers = { 'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.835.163 Safari/535.1', } # 读取配置文件获取数据库地址,链接数据库 self.DBPATH = toml.load('config.toml')['Dbpath'] self.db = pypyodbc.win_connect_mdb(self.DBPATH)
def show_columns_win(cls, path, table): conn = pypyodbc.win_connect_mdb(path) c = conn.cursor() try: column_obj = c.columns(table).fetchall() except Exception as e: # log something pass else: return [column[3] for column in column_obj] finally: c.close() conn.close()
def get_data_win(self): conn = pypyodbc.win_connect_mdb(self.db_path) c = conn.cursor() try: self.data = c.execute(self.create_query_string()).fetchall() except Exception as e: # log somthing here raise else: self.result = [(self.converter(i[0]), i[1]) for i in self.data] finally: c.close() conn.close()
def loadTKR(self): _ret = QtGui.QFileDialog.getOpenFileName(self, "Open TKR", "I:\Datensätze\Wirtz", "TKR (*.Tkr)") if (_ret == ""): return self.ui.lineEdit.setText(_ret) self.inputFile = _ret self.outputFile = self.inputFile+'3' if os.path.isfile(self.outputFile): _s = "new TKR3 " + self.outputFile + " already exists" QtGui.QMessageBox.information(self, 'TMV3', _s, QtGui.QMessageBox.Ok) return _conI = pypyodbc.win_connect_mdb(self.inputFile) _curI = _conI.cursor() _curI.execute('SELECT [tTitle],[tComment],[lAntennenKorrID] from AntennenKorr') _ret = _curI.fetchall() print(_ret) self.tkrCount = 0 for _f in _ret: self.addItem(self.tkrCount,"Antenna",_f[0],_f[1]) _curI.execute('SELECT [dXValue],[dYValue] from AntennenKorrValues WHERE [lAntennenKorrID]={0}'.format(str(_f[2]))) _list = _curI.fetchall() self.valueLists.append(_list) self.tkrCount +=1 pass _curI.execute('SELECT [tTitle],[tComment],[lKabelKorrID] from KabelKorr') _ret = _curI.fetchall() for _f in _ret: self.addItem(self.tkrCount,"Cable",_f[0],_f[1]) _curI.execute('SELECT [dXValue],[dYValue] from KabelKorrValues WHERE [lKabelKorrID]={0}'.format(str(_f[2]))) _list = _curI.fetchall() self.valueLists.append(_list) self.tkrCount +=1 pass _curI.execute('SELECT [tTitle],[tComment],[lAdapterKorrID] from AdapterKorr') _ret = _curI.fetchall() for _f in _ret: self.addItem(self.tkrCount,"Adapter",_f[0],_f[1]) _curI.execute('SELECT [dXValue],[dYValue] from AdapterKorrValues WHERE [lAdapterKorrID]={0}'.format(str(_f[2]))) _list = _curI.fetchall() self.valueLists.append(_list) self.tkrCount +=1 pass self.saveFlag = True
def getDcData(dbpath, processtime, tableid): sql = 'select 数据绑定号 as barcode,测量时间 as testdate,测量类型 as testType, 是否合格 as quality_type from 测量数据表1 where 测量时间>=#%s# and ID>%s' % ( processtime, tableid) conn = 'Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=%s' % dbpath db = pypyodbc.win_connect_mdb(conn) # 打开数据库连接 cur = db.cursor() cur.execute(sql) # cur.execute('select 数据绑定号 as barcode,测量时间 as testdate,测量类型 as testType, \ # 是否合格 as quality_type from 测量数据表1 where 测量时间>=? and ID>?',(processtime,tableid)) values = cur.fetchall() cur.close() db.commit() db.close() return values
def __init__(self, dbname, template='SingleChannelTemplate.mdb'): import os import shutil self.dbname = dbname shutil.copy(template, dbname) directory = os.path.splitext(dbname)[0] self.directory = directory try: os.makedirs(self.directory) except: pass self.connection = pypyodbc.win_connect_mdb(dbname) self.cursor = self.connection.cursor()
def parse_html(): db = pypyodbc.win_connect_mdb(dbpath) dict_max = dict() dict_mid = dict() with open('post.html', encoding='utf-8') as f: text = f.read() html = Selector(text, 'html') tr = html.xpath("//tr[@height='19']") for item in tr.xpath('string(.)').extract(): item = item.strip().replace('\n', '') card = re.findall('[0-9]{6}', item)[0] address = re.findall(r'[\u4e00-\u9fa5].*', item)[0] geshu = item.replace(card, '').replace(address, '') # # 空位2个为最大级别 if len(geshu) == 2: dict_max[card[0:2]] = address sheng = address shi = ' ' qu = ' ' all_ = card + '-' + sheng + '-' + shi + '-' + qu # # 空位3个为第二 if len(geshu) == 3: dict_mid[card[0:4]] = address max_ = card[0:2] sheng = dict_max[max_] shi = address qu = ' ' all_ = card + '-' + sheng + '-' + shi + '-' + qu if len(geshu) == 5: max_ = card[0:2] sheng = dict_max[max_] mid_ = card[0:4] try: shi = dict_mid[mid_] except: shi = sheng qu = address all_ = card + '-' + sheng + '-' + shi + '-' + qu # print(all_) all_list = all_.split('-') card = all_list[0] sheng = all_list[1].replace(' ', '') shi = all_list[2].replace(' ', '') qu = all_list[3] sql = "insert into post_card(省,市,区县,代码) values('%s','%s','%s','%s')" % ( sheng, shi, qu, card) curser = db.cursor() curser.execute(sql) curser.commit() print('%s插入成功' % card)
def read_access(): url = 'Driver={Microsoft Access Driver (*.mdb)};DBQ=' + r"C:\Users\weidiao\Desktop\ZhijianZ\ZhijianZ.mdb" conn = pypyodbc.win_connect_mdb(url) cur = conn.cursor() cur.execute("SELECT * FROM qiye") ans = [] for row in cur.fetchall(): it = dict() for i in range(len(row)): it[cur.description[i][0]] = row[i] ans.append(it) cur.close() conn.close() return ans
def main(): conn = pypyodbc.win_connect_mdb(config.PATH_MDB) # accdbは開けず、以下のエラーとなる # conn = pypyodbc.win_connect_mdb(config.PATH_ACCDB) # pypyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] # データベース '(不明)' を開くことができません。アプリケーションで認識できないデータベースであるか、 # またはファイルが破損しています。 ") cur = conn.cursor() cur.execute("select item_name from item") for c in cur.fetchall(): print(c[0]) #=> `hoge`, `fuga` cur.close() conn.close()
def updateDB(): try: a = int(credit2.get()) + int(maxCredit2.get()) + int(minCredit2.get()) + int(sheetCredit2.get()) + int(sheetMax2.get()) + int(discount2.get()) except: messagebox.showerror(title='Invalid input', message='Some of the entries of credits section are not valid.') else: selectionIIDs = [x for x in tree2.tag_has( 'green') if not tree2.get_children(x)] if selectionIIDs: conn = pypyodbc.win_connect_mdb('./XLDB.mdb') cur = conn.cursor() for item in selectionIIDs: val = [ int(credit2.get()), int(maxCredit2.get()), int(minCredit2.get()), int(sheetCredit2.get()), int(sheetMax2.get()), int(discount2.get()), usersStdnums[item] ] query = ''' UPDATE Users SET Credit=Credit + ?, maxCredit=maxCredit + ?, minCredit=minCredit + ?, paperCredit=paperCredit + ?, maxPaperCredit=maxPaperCredit + ?, Discount=Discount + ? WHERE userName=?; ''' cur.execute(query, val) cur.commit() messagebox.showinfo(title='Successful operation', message='All of the selected entries updated.') cur.close() conn.close() else: messagebox.showinfo(title='Empty selection', message='No one of the entries selected. Please select at least one.')
def sqlite3_from_mdb(mdbfilepath, tableName, model): # копіює всі дані з таблиці mdb в таблицю sqlite3 pypyodbc.lowercase = False connection = pypyodbc.win_connect_mdb(mdbfilepath) cursor = connection.cursor() fields = "*" condition = "" sqlcommand = "SELECT %s FROM %s %s" % (fields, tableName, condition) cursor.execute(sqlcommand) print('-'*50) attr = False while True: row = cursor.fetchone() if not row: break print('row=', row) m = model() if not attr: attr = True coldict, collist = rowAttributes(row) # print(coldict) # print(collist) for field in m.mdbFields: # print(field, m.mdbFields[field]) if row[coldict[m.mdbFields[field]]]: setattr(m, field, row[coldict[m.mdbFields[field]]]) for field in m.mdbFields: print('%20s | %s' % (field, getattr(m, field))) m.save() print('m.id=', m.id) print('-'*50) # input() print('-'*50) cursor.close() connection.commit() connection.close()
def __init__(self, bws_file, file_prefix, section_number=0, max_round=0): """Construct parser object.""" log.getLogger('init').debug('reading BWS file: %s', bws_file) with pypyodbc.win_connect_mdb(bws_file) as connection: cursor = connection.cursor() if max_round == 0: max_round = sys.maxint criteria_string = ' WHERE ' criteria_string += 'Section = %d' % section_number \ if section_number > 0 else '1 = 1' criteria_string += ' AND Round <= %d' % max_round self.__lineup_data = cursor.execute( 'SELECT * FROM RoundData' + criteria_string ).fetchall() bid_data = cursor.execute( 'SELECT * FROM BiddingData' + criteria_string ).fetchall() erased_boards = cursor.execute( 'SELECT * FROM ReceivedData ' + criteria_string + ' AND Erased' ).fetchall() log.getLogger('init').debug('parsing lineup data (%d entries)', len(self.__lineup_data)) self.__round_lineups = parse_lineup_data(self.__lineup_data) log.getLogger('init').debug('parsing bidding data (%d entries)', len(bid_data)) self.__bids = parse_bidding_data(bid_data, erased_boards) log.getLogger('init').debug('parsing prefix, filename = %s', file_prefix) self.__tournament_prefix = path.splitext( path.realpath(file_prefix))[0] log.getLogger('init').debug('prefix = %s', self.__tournament_prefix) self.__tournament_files_match = re.compile( re.escape(self.__tournament_prefix) + r'([0-9]{3})\.html') log.getLogger('init').debug('tournament files pattern: %s', self.__tournament_files_match.pattern) self.__map_board_numbers()
def addToDB(): if not (credit.get().isdigit() and maxCredit.get().isdigit() and minCredit.get().isdigit() and sheetCredit.get().isdigit() and sheetMax.get().isdigit() and discount.get().isdigit()): messagebox.showerror(title='Invalid input', message='Some of the entries of credits section are not valid.') selectionIIDs = [x for x in tree.tag_has( 'green') if not tree.get_children(x)] selection = [tree.item(x) for x in selectionIIDs] if selection: i = 0 j = 0 conn = pypyodbc.win_connect_mdb('./XLDB.mdb') cur = conn.cursor() mp = { 'B.Sc': 'bs', 'M.Sc': 'ms', 'Ph.D': 'phd' } depQuery = "SELECT DepartmentName FROM Departments;" cur.execute(depQuery) dep = cur.fetchall()[0][0] for item in selection: checkQuery = "SELECT * FROM Users WHERE userName=?;" l = [] l.append(str(usersDictionary[item['text']]['cn'][0])) cur.execute(checkQuery, l) if not len(cur.fetchall()): i += 1 val = [ usersDictionary[item['text']]['givenName'][0], # firstname usersDictionary[item['text']]['sn'][0], # lastname dep, # department mp[usersDictionary[item['text']]['dn'].split(',')[2][3:]], # grade int(discount.get()), # discount int(sheetCredit.get()), # paper_credit int(credit.get()), # credit int(minCredit.get()), # min_credit True, # enabled int('13' + usersDictionary[item['text']]['cn'][0][:2]), # entrance_year str(usersDictionary[item['text']]['cn'][0]), # username datetime.now(), # add_date int(maxCredit.get()), # max_credit int(sheetMax.get()) # max_paper_credit ] query = ''' INSERT INTO Users ( Name, Family, Department, Grade, Discount, paperCredit, Credit, minCredit, Enabled, EntranceYear, userName, addDate, maxCredit, maxPaperCredit ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ); ''' cur.execute(query, val) else: j += 1 cur.commit() cur.close() conn.close() if i == 0: messagebox.showinfo(title='Successful operation', message='All of the selected entries exist in the database. No entry added to database.') elif j == 0: messagebox.showinfo(title='Successful operation', message='All of the selected entries added to database.') else: messagebox.showinfo(title='Successful operation', message=str(i) + ' entries of ' + str(i + j) + ' entry added to database.') else: messagebox.showinfo(title='Empty selection', message='No one of the entries selected. Please select at least one.')
sma_temp = sma_temp + data["candles"][4000 - index- i][strC] return sma_temp/num n = 50 last_i = 0 spacer = 5 cntr = 0 last_ID = 0 LowerBand = [] UpperBand = [] Return_Avg = [] Return_SD = [] file_Name = "C:\Users\macky\OneDrive\Documents\Price_Database.mdb" conn = pypyodbc.win_connect_mdb(file_Name) cur = conn.cursor() #cur.execute(u"""CREATE TABLE Return_Data (ID INTEGER PRIMARY KEY, N Integer, R Double)""") #cur.commit() cur.execute("DELETE * FROM Return_Data") cur.commit p = 0 ID = 1 r = -0.002 for i in range(101,4500): aavg = 0.0 ssd = 0.0
"dive", "haulout", "haulout_orig", "summary", "tag_info", "uplink", ) # msdb_tables = ('ctd','deployments','dive','gps','haulout', # 'haulout_orig','sms','summary','tag_info','uplink') # If pandas/pickly object doesn't exist, create # if not os.path.isfile(bin_file): # Connect to MS Access database & create cursor connection = pypyodbc.win_connect_mdb(msdb_file) cursor = connection.cursor() # h5file = tables.openFile(bin_file, mode='w', title=msdb_name) h5file = h5py.File(bin_file, "w") for table in msdb_tables: print table + "1" # Append table to HDF5 File process_cursor(cursor, h5file, table) h5file.close() connection.close()
def convert(self): _t = time.gmtime(os.path.getmtime(self.inputFile)) self.tdsDate = time.strftime('%Y-%m-%d %H:%M:%S', _t) _conI = pypyodbc.win_connect_mdb(self.inputFile) _curI = _conI.cursor() try: _conz = lite.connect(self.outputFile) _curz = _conz.cursor() _conq = pypyodbc.win_connect_mdb(self.inputFile) _curq = _conq.cursor() _curq.execute('SELECT [tTitle],[tEUT],[tComment],[tVersion],[bNATO] from Plan') _plan = _curq.fetchall() print (_plan) _curz.execute("INSERT INTO Plan (Title,TMVVersion,Version,Date,NATO,Company,Comment) VALUES (?,?,?,?,?,?,?)", (str(_plan[0][0]), '2',str(_plan[0][3]),str(self.tdsDate),str(_plan[0][4]),'BSI',str(_plan[0][2]))) _conz.commit() _rowIDPlan = _curz.lastrowid print(_rowIDPlan) _blob = ''.join(self.rtFileData) _curz.execute("INSERT INTO Files (Title,Type,Data) VALUES (?,?,?)", (self.rtFileName, "Routine", _blob)) _blob = ''.join(self.drFileData) _curz.execute("INSERT INTO Files (Title,Type,Data) VALUES (?,?,?)", (self.drFileName, "Driver", _blob)) _conz.commit() _curq.execute('SELECT * from Routinen') _routinen = _curq.fetchall() for n in _routinen: print(n) _routineID = n[0] _curz.execute("INSERT INTO Plots (PlanID,Title,X1,X2,Y1,Y2,Log,Unit,Annotation,[Order],Comment) VALUES (?,?,?,?,?,?,?,?,?,?,?)", (str(_rowIDPlan), str(n[2]),str(n[3]),str(n[4]),str(n[5]),str(n[6]),str(int(n[8])),str('dBµV'),str(n[11]),str(n[16]),str(n[13]))) _conz.commit() _rowIDPlot = _curz.lastrowid print(_rowIDPlot) _curq.execute('SELECT [tTitle] FROM Limits WHERE [lRoutineID]={0}'.format(str(_routineID))) _limits = _curq.fetchall() # add version _lim = [] _e = '0','0' for _x in _limits: _lim.append((_x[0],'1.0')) _curz.execute("INSERT INTO Routines (PlotID,Title,Device1,SignalClass,[Order],Comment,Limits) VALUES (?,?,?,?,?,?,?)", (str(_rowIDPlot), self.rtFileName,self.drFileName,str(n[14]),str(n[16]),str(n[13]),str(_lim))) _conz.commit() _rowIDRoutine = _curz.lastrowid print(_rowIDRoutine) _curq.execute('SELECT * from Settings WHERE [lRoutineID]={0}'.format(str(_routineID))) _settings = _curq.fetchall() for i in _settings: _settingID = i[0] _curz.execute("INSERT INTO Settings (RoutineID,[Order],StartFreq,StopFreq,Title,Step,StepWidth,StepTime,Route) VALUES (?,?,?,?,?,?,?,?,?)", (str(_rowIDRoutine), str(int(i[23])),str(i[3]),str(i[4]),str(i[2]),'0','0',str(i[9]),str(i[17]))) _conz.commit() _rowIDSetting = _curz.lastrowid print(_rowIDSetting) _curz.execute("INSERT INTO [Commands] ([SettingID],[Order],[Command],[Parameter]) VALUES (?,?,?,?)", (str(_rowIDSetting),str('1'),str('set_StartFreq'),str(i[3]))) _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'2','set_StopFreq',str(i[4]))) _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'3','set_ResBW',str(i[5]))) _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'4','set_VidBW',str(i[6]))) _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'5','set_Attenuator',str(i[10]))) if n[15] == "FSET": _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'6','set_PreSelector',str(i[15]))) _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'7','set_PreAmplifier',str(int(i[11])))) else: _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'6','set_PreSelector',str(int(i[12])))) _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'7','set_PreAmplifier',str(int(i[13])))) _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'8','set_RefLevel',str(i[8]))) _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'9','set_Detector',str(i[22]))) _curz.execute("INSERT INTO Commands (SettingID,[Order],Command,Parameter) VALUES (?,?,?,?)", (str(_rowIDSetting),'10','set_StepTime',str(i[9]))) _conz.commit() _curq.execute("SELECT * FROM Traces WHERE [lSettingID]={0}".format(str(_settingID))) _traces = _curq.fetchall() for j in _traces: _curz.execute("INSERT INTO Traces (SettingID,StartFreq,StopFreq) VALUES (?,?,?)", (str(_rowIDSetting), str(int(j[2])),str(j[3]))) _conz.commit() # for n in x: # _curq.execute('SELECT [dXValue],[dYValue] from LIMITVALUES where [lLimitID] = {0} ORDER by [dXValue]'.format(str(n[2]))) # y = _curq.fetchall() # print (str(n[0])) # print (str(mDate)) # print (str(n[1])) # print(str(y)) # _curz.execute("INSERT INTO Limits (Title,Date,Comment,DataXY) VALUES (?,?,?,?)", (str(n[0]), str(mDate),str(n[1]),str(y))) # _conz.commit() # _conz.close() except Exception as _err: print(_err) QtGui.QMessageBox.information(self, 'TMV3', str(_err), QtGui.QMessageBox.Ok) return (False) return True
k = k+1 j = j+1 elif(len(word_entered) > len(passed_word)): word_entered = word_entered[0:j] + word_entered[(j+1):] else: word_entered = word_entered[0:j] + passed_word[k] + word_entered[(j+1):] k = k+1 j = j+1 print word_entered operations = operations + 1 probability = (float)(operations) / ((float)(length) * (float)(similar)) print (probability) probability_of_words.append(probability) conn = pypyodbc.win_connect_mdb('F:\\Dictionary') cur1 = conn.cursor() print("Words available in the dictionary") SQL = 'SELECT * FROM WORDS ORDER BY STRINGS' cur1.execute(SQL) for row in cur1.fetchall(): for field in row: print field word = raw_input("Enter word") word = word.upper() temp = "" temp = word[0]
def getAccessConn(path): conn = pypyodbc.win_connect_mdb(path) return conn
def CreateAccessDatabase(self, fname): pypyodbc.win_create_mdb(fname) connection = pypyodbc.win_connect_mdb(fname) connection.cursor().execute('CREATE TABLE t1 (id COUNTER PRIMARY KEY, name CHAR(25));').commit() connection.close()