我们从Python开源项目中，提取了以下50个代码示例，用于说明如何使用sqlite3.connect()。
def __init__(self): # Path setting self.Fpath = "/tmp" self.DbPath = "/tmp/.db" # Web3 setting self.web3 = Web3(HTTPProvider('http://localhost:8545')) # DB setting self.conn = sqlite3.connect(self.DbPath+"/FileSign.db") self.c = self.conn.cursor() self.c.execute("create table if not exists AccountEhash(account text, Ehash text, PRIMARY KEY(account))") self.conn.commit() self.c.execute("create table if not exists SendLog(account text, Thash text)") self.conn.commit() self.c.execute("create table if not exists SignFhash(SignHash text, Fhash text, PRIMARY KEY(SignHash))") self.conn.commit() try: self.c.execute("insert into AccountEhash values ('admin','"+str(self.web3.eth.coinbase)+"')") self.conn.commit() except: pass
def insert(address, port, location='default', protocol='default'): uid = str(uuid.uuid5(uuid.NAMESPACE_DNS,address)) conn = sqlite3.connect(db) try: conn.execute("INSERT INTO PROXY VALUES (?, ?, ?, ?, ?)",[uid,address,port,location,protocol]) print('insert: '+str(address)) pass except sqlite3.IntegrityError as e: conn.execute("UPDATE PROXY SET ADDRESS = ?, PORT = ?, LOCATION = ?, PROTOCOL = ? WHERE UID=?",[address,port,location,protocol,uid]) print('update: '+str(address)) pass except sqlite3.ProgrammingError as e: #TODO pass finally: conn.commit() conn.close()
def retrieve_garden_from_db(self): # Builds a dict of dicts from garden sqlite db garden_dict = {} conn = sqlite3.connect(self.garden_db_path) # Need to allow write permissions by others conn.row_factory = sqlite3.Row c = conn.cursor() c.execute('SELECT * FROM garden ORDER BY owner') tuple_list = c.fetchall() conn.close() # Building dict from table rows for item in tuple_list: garden_dict[item[0]] = { "owner":item[1], "description":item[2], "age":item[3], "score":item[4], "dead":item[5], } return garden_dict
def getDevicesData(self): with self.lock: conn = sqlite3.connect(self.dbFile) c = conn.cursor() devicesData = {} for row in c.execute("SELECT devices.id, devices.hex, max(readings.time) FROM devices JOIN readings ON devices.id = readings.deviceId GROUP BY devices.id, devices.hex"): did = row[0] devDict = {"deviceId":str(row[1].encode('UTF-8')), "lastContact":row[2]} devDict["values"] = {} c2 = conn.cursor() for sensor in c2.execute("SELECT sensors.name, readings.numberValue, addresses.ip, addresses.protocol FROM readings JOIN sensors ON readings.sensorId = sensors.id JOIN addresses ON readings.addressId = addresses.id WHERE readings.time=? AND deviceId=?", (devDict["lastContact"],did)): devDict["address"] = sensor[2] devDict["protocol"] = sensor[3] devDict["values"][sensor[0]] = sensor[1] devicesData[devDict["deviceId"]] = devDict conn.close() return devicesData
def __init__(self, conn_or_path, calendar, daily_bar_reader, overwrite=False): if isinstance(conn_or_path, sqlite3.Connection): self.conn = conn_or_path elif isinstance(conn_or_path, str): if overwrite and exists(conn_or_path): try: remove(conn_or_path) except OSError as e: if e.errno != ENOENT: raise self.conn = sqlite3.connect(conn_or_path) else: raise TypeError("Unknown connection type %s" % type(conn_or_path)) self._daily_bar_reader = daily_bar_reader self._calendar = calendar
def __init__(self): conn = sqlite3_connect(':memory:') writer = SQLiteAdjustmentWriter(conn, None, None) empty = DataFrame({ 'sid': array([], dtype=uint32), 'effective_date': array([], dtype=uint32), 'ratio': array([], dtype=float), }) empty_dividends = DataFrame({ 'sid': array([], dtype=uint32), 'amount': array([], dtype=float64), 'record_date': array([], dtype='datetime64[ns]'), 'ex_date': array([], dtype='datetime64[ns]'), 'declared_date': array([], dtype='datetime64[ns]'), 'pay_date': array([], dtype='datetime64[ns]'), }) writer.write(splits=empty, mergers=empty, dividends=empty_dividends) super(NullAdjustmentReader, self).__init__(conn)
def connection(self, commit_on_success=False): with self._lock: if self._bulk_commit: if self._pending_connection is None: self._pending_connection = sqlite.connect(self.filename) con = self._pending_connection else: con = sqlite.connect(self.filename) try: if self.fast_save: con.execute("PRAGMA synchronous = 0;") yield con if commit_on_success and self.can_commit: con.commit() finally: if not self._bulk_commit: con.close()
def __init__(self, yeebot): self.yeebot = yeebot self.conn = sqlite3.connect('db/yee.db') self.cur = self.conn.cursor() self.cur.execute("CREATE TABLE IF NOT EXISTS raffles (raffle_id INTEGER PRIMARY KEY, is_live INTEGER," " initiator_id TEXT, initiator_name TEXT, time_started TIMESTAMP DEFAULT CURRENT_TIMESTAMP," " winner_id TEXT, winner_name TEXT, winnings_amount INTEGER)") self.cur.execute('CREATE TABLE IF NOT EXISTS raffle_entries (raffle_id INTEGER REFERENCES raffles(raffle_id),' ' user_id TEXT, user_name TEXT, is_winner INTEGER DEFAULT 0, UNIQUE (raffle_id, user_id))') self.conn.commit() # Check if there is already a live raffle in the database self.cur.execute('SELECT * FROM raffles WHERE is_live = 1') fetch = self.cur.fetchone() if fetch: self.is_live = 1 self.live_raffle_id = fetch[0] self.initiator = fetch[1] self.time_started = fetch[3] else: self.is_live = 0
def get_cookies(self): for cookie_file in self.cookie_files: with create_local_copy(cookie_file) as tmp_cookie_file: con = sqlite3.connect(tmp_cookie_file) cur = con.cursor() cur.execute('select host, path, isSecure, expiry, name, value from moz_cookies') for item in cur.fetchall(): yield create_cookie(*item) con.close() # current sessions are saved in sessionstore.js session_file = os.path.join(os.path.dirname(cookie_file), 'sessionstore.js') if os.path.exists(session_file): try: json_data = json.loads(open(session_file, 'rb').read().decode('utf-8')) except ValueError as e: print('Error parsing firefox session JSON:', str(e)) else: expires = str(int(time.time()) + 3600 * 24 * 7) for window in json_data.get('windows', []): for cookie in window.get('cookies', []): yield create_cookie(cookie.get('host', ''), cookie.get('path', ''), False, expires, cookie.get('name', ''), cookie.get('value', '')) else: print('Firefox session filename does not exist:', session_file)
def saveData(items): ipcount = 0 try: cx = sqlite3.connect(sys.path[0]+"/IPRange.db") cx.text_factory = str cu = cx.cursor() cu.execute("delete from ranges") cu.execute("update sqlite_sequence SET seq = 0 where name ='ranges'") for item in items: begin = item.split('-')[0] end = item.split('-')[1] count = item.split('-')[2] ipcount = ipcount + int(count) thisList = ip_range(begin,end) bThread(thisList) cu.execute("insert into ranges (begin,end,count) values (?,?,?)", (begin,end,count)) cx.commit() cu.close() cx.close() print '[*] Saved.Total '+ str(ipcount)+'.' except Exception, e: print e
def saveDetial(ip): try: cx = sqlite3.connect(sys.path[0]+"/IPS.db") cx.text_factory = str cu = cx.cursor() cu.execute("select * from ips where ip='%s'" % (ip)) if not cu.fetchone(): posData = getposition(ip) country = unicode(posData[0]) province = unicode(posData[1]) city = unicode(posData[2]) isp = unicode(posData[3]) print '[?] Saving '+ip cu.execute("insert into ips (ip,country,province,city,isp) values (?,?,?,?,?)", (ip,country,province,city,isp)) cx.commit() cu.close() cx.close() except Exception, e: print e
def save_data(): print '[*] ??????????...' save_count = 0 try: cx = sqlite3.connect(sys.path[0] + "/search.db") cx.text_factory = str cu = cx.cursor() print len(QUERY_LIST) for m in QUERY_LIST: cu.execute("select * from record where magnet=? or thunder=?", (m['magnet'], m['thunder'])) if not cu.fetchone(): cu.execute("insert into record (title,magnet,thunder,size) values (?,?,?,?)", (m['title'], m['magnet'], m['thunder'], m['size'])) cx.commit() print '[?] => Insert successly!' save_count = save_count + 1 else: print R + '[x] <= Found in database!' + W cu.close() cx.close() print '*' * 60 print '[?] ??????????%s?' % str(len(QUERY_LIST)) print '[?] ??????????%s?' % save_count except Exception, e: print e
def update_metadata(mbtiles_file, metadata): """ Update metadata key value pairs inside the MBTiles file with the provided metadata """ conn = sqlite3.connect(mbtiles_file) def upsert_entry(key, value): conn.execute("DELETE FROM metadata WHERE name='{}'".format(key)) conn.execute("INSERT INTO metadata VALUES('{}', '{}')".format(key, value)) for key, value in metadata.items(): upsert_entry(key, value) conn.commit() conn.close()
def connect_db(self): if os.path.exists(self.appSettings['basename']): if 'con' in dir(self): self.con.close() self.con = sqlite.connect(self.appSettings['basename']) else: if os.path.exists(os.path.abspath(os.curdir) + '/myBase.db'): createIt = False else: createIt = True self.appSettings['basename'] = 'myBase.db' self.con = sqlite.connect(self.appSettings['basename']) if createIt: with self.con: cur = self.con.cursor() #cur.execute("DROP TABLE IF EXISTS Items") cur.execute("CREATE TABLE shabl ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, keyword TEXT, code TEXT, datechange DATE)") #cur.execute("CREATE TABLE Items(Id INTEGER PRIMARY KEY, Name TEXT, Price INT)")
def main(): conn = sqlite3.connect('axfr.db') create_table(conn) csvdir = os.path.join(os.getcwd(), 'axfr') results = defaultdict(int) rolling = 0 for filename in os.listdir(csvdir): for subname, rectype in load_file(os.path.join(csvdir, filename)): results[(subname, rectype)] += 1 rolling += 1 if rolling > 100000: results = rollup_results(conn, results) rolling = 0 print("!") results = rollup_results(conn, results)
def run(self): print("Connection de %s %s" % (self.ip, self.port, )) r = self.clientsocket.recv(2048) r2 = str(datetime.now()) + "; " + r print (r) file = open(file_save, 'a') file.write(r2 + "\n") file.close() machine = r[0:6] niveau = r[6] ranger(machine,niveau) #rangement dans la base SQLite db = sqlite3.connect('wipOutillage.db') cursor = db.cursor() cursor.execute(""" INSERT INTO etat(machine, niveau, moment) VALUES(?, ?, ?)""", (machine, niveau, str(datetime.now()))) db.close() print("Client déconnecté...")
def establishDatabase(self): self.conn = sqlite3.connect(self.dbFilename) self.c = self.conn.cursor() # Always try to create the database; it's a no-op if already exists try: self.c.execute('''CREATE TABLE episodes (show, episode, quality, reencode, uncensored, westlive, UNIQUE (show, episode, quality, reencode, uncensored, westlive) ON CONFLICT ABORT)''') self.c.execute('''CREATE INDEX epidx ON episodes (show, episode, quality, reencode, uncensored, westlive)''') self.conn.commit() except: pass
def saveToSqlite(lesson_info): # ??lesson_info?????? name = lesson_info['name'] link = lesson_info['link'] des = lesson_info['des'] number = lesson_info['number'] time = lesson_info['time'] degree = lesson_info['degree'] # ???????????? con = sqlite3.connect("lesson.db") cur = con.cursor() sql = "insert into lesson_info values ('%s', '%s','%s','%s','%s','%s')" % (name, link, des, number, time, degree) cur.execute(sql) con.commit() # ?????
def saveToSqlite(lesson_info): # ??lesson_info?????? name = lesson_info['name'] link = lesson_info['link'] des = lesson_info['des'] time = lesson_info['time'] degree = lesson_info['degree'] # ???????????? con = sqlite3.connect("lesson.db") cur = con.cursor() #???????????????????? #cur.execute("create table lesson_xx(name varchar(10),link TEXT,des TEXT,time varchar(30),degree varchar(10))") sql = "insert into lesson_xx values ('%s', '%s','%s','%s','%s')" % (name, link, des, time, degree) cur.execute(sql) con.commit() # ?????
def RunningChain(message): print "RunningChain : "+message conn = sqlite3.connect(DbPath+"/chain.db") c = conn.cursor() c.execute("create table if not exists RunningChain(Ohash text)") conn.commit() # insert into RunningChain table c.execute("insert into RunningChain values('"+message+"')") conn.commit() # delete from AskResource table cmd = "ipfs object get "+message Object = subprocess.check_output(cmd, shell=True) JsonObject = json.loads(Object) Dhash = "TaiwanNumberOne" for x in JsonObject['Links']: if x['Name'] == 'description': Dhash = x['Hash'] c.execute("delete from AskResource where DescriptionHash = '"+Dhash+"'") conn.commit()
def __init_db__(self): # sqlite3 will not error on a non-existant file, but simply create a new one if not os.path.exists(self.db_filename): self.db_conn = sqlite3.connect(self.db_filename) with open(self.CREATE_SCHEMA_FILE, 'r') as schema: self.db_conn.execute(schema.read()) # We'll define the global Cidr self.db_conn.execute( "insert into registeredCidrs " + "(cidr, description, location, owner, expiration) " + "VALUES ('0.0.0.0/0', 'Global', 'Internet', 'aws-sg-mngr', {0});" .format(RegisteredCidr.DO_NOT_EXPIRE)) # self.db_conn.execute( # "insert into registeredCidrs(cidr, description) " + # "VALUES ('75.67.236.14/32', 'Company HQ');") self.db_conn.commit() else: self.db_conn = sqlite3.connect(self.db_filename)
def sharded_container(basedir, acct, cname, path, as_prefix=""): for prefix in prefixes(): new_acct, new_cname = compute_new_cname(acct, cname, prefix) new_cid = cid_from_name(new_acct, new_cname) new_path = basedir + '/' + new_cid[0:3] + '/' + new_cid + '.1.meta2' logging.debug("%s %s %s %s", new_path, new_acct, new_cname, new_cid) try: makedirs(dirname(new_path)) except OSError: pass try: from subprocess import check_call check_call(["/bin/cp", "-p", path, new_path]) with connect(new_path) as db: prune_database(db, new_cname, new_cid, ''.join([as_prefix, prefix])) print new_acct, new_cname, new_cid except Exception: from traceback import print_exc print_exc(file=stderr)
def __init__(self, path, threaded=True, timeout=None): """ >>> lock = SQLiteLockFile('somefile') >>> lock = SQLiteLockFile('somefile', threaded=False) """ LockBase.__init__(self, path, threaded, timeout) self.lock_file = unicode(self.lock_file) self.unique_name = unicode(self.unique_name) if SQLiteLockFile.testdb is None: import tempfile _fd, testdb = tempfile.mkstemp() os.close(_fd) os.unlink(testdb) del _fd, tempfile SQLiteLockFile.testdb = testdb import sqlite3 self.connection = sqlite3.connect(SQLiteLockFile.testdb) c = self.connection.cursor() try: c.execute("create table locks" "(" " lock_file varchar(32)," " unique_name varchar(32)" ")") except sqlite3.OperationalError: pass else: self.connection.commit() import atexit atexit.register(os.unlink, SQLiteLockFile.testdb)
def recommend_program(program_interest): edu_map = {'computer_science':0, 'commerce_business':1, 'engineering':2, 'humanities_lifesci':3, 'math_physics_statistics':4} for i in program_interest: if i in edu_map.keys(): program_interest[program_interest.index(i)] = edu_map.get(i) conn = sqlite3.connect('/Users/Rahul/Desktop/Side_projects/linkedin_recommend/db.sqlite3') c = conn.cursor() major_list = [] for interest in program_interest: sql = 'SELECT id FROM link_rec_allparsedprofile WHERE program_classification=?' c.execute(sql, (interest,)) y = c.fetchall() y = [i[0] for i in y] major_list.append(y) major_list = [y for i in major_list for y in i] return list(set(major_list))
def get_job_title(): """ Return's job title, company_name, profile_url for each profile in db """ conn = sqlite3.connect('/Users/Rahul/Desktop/Side_projects/linkedin_recommend/db.sqlite3') c = conn.cursor() c.execute('SELECT job FROM link_rec_alljobtitle') y = c.fetchall() job_title = [job[0].lower() for job in y] c.execute('SELECT loc FROM link_rec_alllocation') y = c.fetchall() company_name = [i[0].lower() for i in y] c.execute('SELECT url FROM link_rec_allparsedprofile') y = c.fetchall() profile_url = [i[0] for i in y] return job_title, company_name, profile_url
def clean_experience(profile_id, experience_list, header_list, connection, conn): if experience_list is None: return None else: company_name = [experience_list[i] for i in range(len(experience_list)) if i%2] job_title = [experience_list[i] for i in range(len(experience_list)) if i%2 == 0] assert len(company_name) == len(job_title) conn = sqlite3.connect('/Users/Rahul/Desktop/Main/Side_projects/linkedin_recommend/db.sqlite3') c = conn.cursor() # c.execute('SELECT id FROM link_rec_allparsedprofile ORDER BY id DESC LIMIT 1') c.execute('SELECT id FROM link_rec_alljobtitle ORDER BY id DESC LIMIT 1') original_id = c.fetchone() new_id = 0 if original_id is not None: new_id = original_id[0] + 1 for i in range(len(company_name)): connection.execute('INSERT INTO link_rec_alllocation (id, profile_id, loc) VALUES (?, ?, ?)', (new_id, profile_id, company_name[i],)) conn.commit() connection.execute('INSERT INTO link_rec_alljobtitle (id, profile_id, job) VALUES (?, ?, ?)', (new_id, profile_id, job_title[i],)) conn.commit() new_id += 1
def get_instance(self,instance_id): with sqlite3.connect(self.db_filename) as conn: cur = conn.cursor() cur.execute('SELECT * from main where instance_id=?',(instance_id,)) rows = cur.fetchall() if len(rows)==0: return None colnames = [description[0] for description in cur.description] d ={} for i in range(len(colnames)): if colnames[i] in self.json_cols_db: d[colnames[i]] = json.loads(rows[0][i]) else: d[colnames[i]]=rows[0][i] d = self.add_defaults(d) return d
def to_ieb(self, filename): gem_id_dict = {v:k for k,v in gem_skill_id_dict.items()} uid_cid_dict = {str(k):str(v) for k,v in sqlite3.connect(unit_db_dir).cursor().execute("SELECT unit_id, unit_number FROM unit_m").fetchall()} for i, x in enumerate(range(1243,1252),1): uid_cid_dict[str(x)] = str(2000+i) cid_uid_dict = {v:k for k,v in uid_cid_dict.items()} def card_dict(card): res = {'love':int(card.bond), 'rank':int(card.idolized)+1, 'level':int(card.level), 'unit_skill_level':int(card.skill.level), 'unit_id':cid_uid_dict[str(card.card_id)], 'removable':[gem_id_dict[gem.name] for gem in card.equipped_gems], 'unit_removable_skill_capacity':int(card.slot_num)} return res content = [0] + [card_dict(card) for card in self.card_list] if filename is not None: with open(filename, 'w') as fp: fp.write(json.dumps(content)) print('File saved to', filename) else: return json.dumps(content)
def init_database(self): # check if dir exists, create sqlite directory and set OS permissions to 777 sqlite_dir_path = os.path.join(self.game_dir,'sqlite') if not os.path.exists(sqlite_dir_path): os.makedirs(sqlite_dir_path) os.chmod(sqlite_dir_path, 0777) conn = sqlite3.connect(self.garden_db_path) init_table_string = """CREATE TABLE IF NOT EXISTS garden ( plant_id tinytext PRIMARY KEY, owner text, description text, age text, score integer, is_dead numeric )""" c = conn.cursor() c.execute(init_table_string) conn.close() # init only, creates and sets permissions for garden db and json if os.stat(self.garden_db_path).st_uid == os.getuid(): os.chmod(self.garden_db_path, 0666) open(self.garden_json_path, 'a').close() os.chmod(self.garden_json_path, 0666)
def update_garden_db(self, this_plant): # insert or update this plant id's entry in DB # TODO: make sure other instances of user are deleted # Could create a clean db function self.init_database() age_formatted = self.plant_age_convert(this_plant) conn = sqlite3.connect(self.garden_db_path) c = conn.cursor() # try to insert or replace update_query = """INSERT OR REPLACE INTO garden ( plant_id, owner, description, age, score, is_dead ) VALUES ( '{pid}', '{pown}', '{pdes}', '{page}', {psco}, {pdead} ) """.format(pid = this_plant.plant_id, pown = this_plant.owner, pdes = this_plant.parse_plant(), page = age_formatted, psco = str(this_plant.ticks), pdead = int(this_plant.dead)) c.execute(update_query) conn.commit() conn.close()
def insert_coins(self, userid, coins, mention=None): self.conn = sqlite3.connect(self.DB_NAME) if mention == None and self.bot != None: mention = self.bot.Client.get_user_info(userid) if mention != None: sql = "INSERT OR IGNORE INTO members (userid, coins, user_mention) VALUES(?,0,?);" self.conn.execute(sql, (userid, mention,)) params = (coins, mention, userid,) sql = "UPDATE members SET coins = coins + ?, user_mention = ? WHERE userid = ?;" elif mention == None: sql = "INSERT OR IGNORE INTO members (userid, coins) VALUES(?,0,?);" self.conn.execute(sql, (userid,)) params = (coins, userid,) sql = "UPDATE members SET coins = coins + ? WHERE userid = ?;" self.conn.execute(sql, params) self.conn.commit() self.conn.close()
def load_database(self): self.buster_database = {} conn = sqlite3.connect(self.database_name) c = conn.cursor() select_all = 'select * from user_table;' c.execute(select_all) rows = c.fetchall() print(rows) for row in rows: self.buster_database.update({row[self.user_name] : [row[self.creation_date], row[self.user_status], row[self.time_stamp]]}) if row[self.user_status] == 2: #whitelisted print('adding {} to whitelist'.format(row[self.user_name])) self.whitelisted_users.append(row[self.user_name]) if row[self.user_status] == 1: #blacklisted if row[self.creation_date] not in self.banned_dates: self.banned_dates.append(row[self.creation_date]) conn.close() return self.buster_database
def main(): cmd_options = _parse_cmd_args() if os.path.exists(cmd_options.data_file): answer = raw_input( u"???? '{}' ??????????(y/n)".format( cmd_options.data_file).encode("utf-8")) if answer == 'y': os.remove(cmd_options.data_file) else: exit(0) with sqlite3.connect(cmd_options.data_file) as conn: # ??? conn.executescript(CREATE_USER_TABLE) conn.executescript(CREATE_CAT_TABLE) # ???? cursor = conn.cursor() cursor.executemany(INSERT_USER, USER_DATA) cursor.executemany(INSERT_CAT, CAT_DATA) show_msg_and_exit(u"?????? '{}' ??????!".format(cmd_options.data_file), "green")
def __init__(self, config): if config.db == "mysql": pass # TODO: determine best production grade relational database to use elif config.db == "sqlite": self._dbConn = sqlite3.connect(os.environ["PIPELINES_DB"]) self._pipelinesDb = self._dbConn.cursor()
def __init__(self, config): if config.db == "mysql": pass # TODO: determine best production grade relational database to use #self._dbConn = MySQLdb.connect(host=config.db_host, user=config.db_user, passwd=config.db_password, db="pipelines", port=config.db_port) elif config.db == "sqlite": self._dbConn = sqlite3.connect(os.path.join(os.path.dirname(config.path), "isb-cgc-pipelines.db")) self._pipelinesDb = self._dbConn.cursor()
def __init__(self, path=None): self.db_path = path if path is None: if 'UNIT_STATE_DB' in os.environ: self.db_path = os.environ['UNIT_STATE_DB'] else: self.db_path = os.path.join( os.environ.get('CHARM_DIR', ''), '.unit-state.db') self.conn = sqlite3.connect('%s' % self.db_path) self.cursor = self.conn.cursor() self.revision = None self._closed = False self._init()
def setup_db(dbpath=DBPATH): conn = sqlite3.connect(dbpath) cur = conn.cursor() # Drop tables if they exist cur.execute("DROP TABLE IF EXISTS rst_nodes") cur.execute("DROP TABLE IF EXISTS rst_relations") cur.execute("DROP TABLE IF EXISTS docs") cur.execute("DROP TABLE IF EXISTS perms") cur.execute("DROP TABLE IF EXISTS users") cur.execute("DROP TABLE IF EXISTS projects") cur.execute("DROP TABLE IF EXISTS logging") cur.execute("DROP TABLE IF EXISTS settings") conn.commit() # Create tables cur.execute('''CREATE TABLE IF NOT EXISTS rst_nodes (id text, left real, right real, parent text, depth real, kind text, contents text, relname text, doc text, project text, user text, UNIQUE (id, doc, project, user) ON CONFLICT REPLACE)''') cur.execute('''CREATE TABLE IF NOT EXISTS rst_relations (relname text, reltype text, doc text, project text, UNIQUE (relname, reltype, doc, project) ON CONFLICT REPLACE)''') cur.execute('''CREATE TABLE IF NOT EXISTS docs (doc text, project text, user text, UNIQUE (doc, project, user) ON CONFLICT REPLACE)''') cur.execute('''CREATE TABLE IF NOT EXISTS users (user text, UNIQUE (user) ON CONFLICT REPLACE)''') cur.execute('''CREATE TABLE IF NOT EXISTS projects (project text, guideline_url text, UNIQUE (project) ON CONFLICT REPLACE)''') cur.execute('''CREATE TABLE IF NOT EXISTS logging (doc text, project text, user text, actions text, mode text, timestamp text)''') cur.execute('''CREATE TABLE IF NOT EXISTS settings (setting text, svalue text, UNIQUE (setting) ON CONFLICT REPLACE)''') conn.commit() conn.close()
def import_document(filename, project, user, dbpath=DBPATH): """parse and import an RS3 file into the local rstWeb SQLite database. Parameters ---------- filename : unicode path to the RS3 file to be imported project : unicode name of an existing project that the file will be imported into user : str name of the user associated with that project """ conn = sqlite3.connect(dbpath) cur = conn.cursor() doc=os.path.basename(filename) rel_hash = {} rst_nodes = read_rst(filename, rel_hash) if isinstance(rst_nodes,basestring): return rst_nodes # First delete any old copies of this document, if they are already imported delete_document(doc,project) for key in rst_nodes: node = rst_nodes[key] cur.execute("INSERT INTO rst_nodes VALUES(?,?,?,?,?,?,?,?,?,?,?)", (node.id,node.left,node.right,node.parent,node.depth,node.kind,node.text,node.relname,doc,project,user)) #user's instance cur.execute("INSERT INTO rst_nodes VALUES(?,?,?,?,?,?,?,?,?,?,?)", (node.id,node.left,node.right,node.parent,node.depth,node.kind,node.text,node.relname,doc,project,"_orig")) #backup instance for key in rel_hash: rel_name = key rel_type = rel_hash[key] cur.execute("INSERT INTO rst_relations VALUES(?,?,?,?)", (rel_name, rel_type, doc, project)) cur.execute("INSERT INTO docs VALUES (?,?,?)", (doc,project,user)) cur.execute("INSERT INTO docs VALUES (?,?,'_orig')", (doc,project)) conn.commit() conn.close()
def get_rst_doc(doc, project, user, dbpath=DBPATH): """Return database representation of the given RS3 file. Parameters ---------- doc : unicode filename of an already imported RS3 file project : unicode name of the project that the RS3 file was imported into user : unicode user name, e.g. 'admin' or 'local' Returns ------- rows : list(tuple(unicode, float, float, unicode, float, unicode, unicode, unicode, unicode, unicode, unicode)) A database representation of an RST tree / RS3 file. Each row contains these columns: - id (int ID of the EDU/segment/span, starting at 1, converted to unicode - left (int ID of leftmost EDU that this span covers, converted to float) - right (int ID of rightmost EDU that this span covers, converted to float) - parent (int ID of the parent segment/span of this one, converted to unicode) - depth ???, seems to be always 0.0 - kind: 'edu', 'span' or 'multinuc' - contents: text of the EDU (if it is one, otherwise u'') - relname: RST relation name (w/ appended '_m' for multinuc and '_r' for 'rst') - doc: RS3 file name - project: name of the project the file belongs to - user: user name """ conn = sqlite3.connect(dbpath) with conn: cur = conn.cursor() cur.execute("SELECT id, left, right, parent, depth, kind, contents, relname, doc, project, user FROM rst_nodes WHERE doc=? and project=? and user=? ORDER BY CAST(id AS int)", (doc,project,user)) rows = cur.fetchall() return rows
def get_rst_rels(doc, project, dbpath=DBPATH): """Return a list RST relations defined for the given document. The relations are not fetched directly from the file but from the local SQLite database into which the original file was parsed/imported. Note: RST relations have are appended with '_m' if they are of type 'multinuc' and with '_r' in case of 'rst' relation types. Parameters ---------- doc : unicode filename of an already imported RS3 file project : unicode name of the project that the RS3 file was imported into Returns ------- rows : list(tuple(unicode, unicode)) A list of (RST relation name, RST relation type) tuples, e.g. (u'restatement-mn_m', u'multinuc'). """ conn = sqlite3.connect(dbpath) with conn: cur = conn.cursor() cur.execute("SELECT relname, reltype FROM rst_relations WHERE doc=? and project=? ORDER BY relname", (doc,project)) rows = cur.fetchall() return rows
def generic_query(sql, params, dbpath=DBPATH): conn = sqlite3.connect(dbpath) with conn: cur = conn.cursor() cur.execute(sql, params) rows = cur.fetchall() return rows
def open(self, name): """ >>> """ try: self.conn = sqlite3.connect(name) self.cursor = self.conn.cursor() except sqlite3.Error: print("Error connecting to database!.")
def setup_database(): dbFile = Path(DB_FILE) # connection to database file conn = sqlite3.connect(DB_FILE) # database cursor object dbCommentRecord = conn.cursor() # check to see if database file exists if not dbFile.is_file(): dbCommentRecord.execute('''CREATE TABLE comments(comment text)''') conn.commit() return conn, dbCommentRecord
def __init__(self): """ Connects to barometer.db and sets up an sql cursor. """ self.conn = sqlite3.connect(DATABASE_FILE) self.cursor = self.conn.cursor()
def getDB(connType): if connType == DBTYPE_MYSQL: conn = MySQLdb.connect (host = DBHOST, user = DBUSER, passwd = DBPASS, db = DBNAME) else: conn = sqlite3.connect ("{}.db".format(DBNAME)) return conn
def get_conn(self): if not self.conn: self.conn = sqlite3.connect(self.manifest_file) conn = self.conn self.conn = None yield conn conn.rollback() self.conn = conn
def init_data(): global db_telebot db_telebot = sqlite3.connect(telesettings_file,check_same_thread=False) db_telebot.text_factory = str cursor_telebot = db_telebot.cursor() cursor_telebot.execute("PRAGMA journal_mode = WAL") cursor_telebot.execute("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, notify INTEGER, latitude REAL, longitude REAL, radius INTEGER, ignored TEXT, nick TEXT, silence TEXT)") db_telebot.commit()
def init_account_db(): global db_accs db_accs = sqlite3.connect(fpath_accs,check_same_thread=False) db_accs.text_factory = str cursor_accs = db_accs.cursor() cursor_accs.execute("CREATE TABLE IF NOT EXISTS accounts(user BLOB PRIMARY KEY, access_token BLOB, access_expire_timestamp INTEGER, api_url BLOB, auth_ticket__expire_timestamp_ms INTEGER, auth_ticket__start BLOB, auth_ticket__end BLOB)") cursor_accs.execute("PRAGMA journal_mode = WAL") db_accs.commit()
def init_data(): global db_data db_data = sqlite3.connect(fpath_data,check_same_thread=False) cursor_data = db_data.cursor() cursor_data.execute("CREATE TABLE IF NOT EXISTS spawns(spawnid INTEGER PRIMARY KEY, latitude REAL, longitude REAL, spawntype INTEGER, pokeid INTEGER, expiretime INTEGER, fromtime INTEGER, profile INTEGER)") cursor_data.execute("PRAGMA journal_mode = WAL") db_data.commit()
