Python sqlite3.connect函数代码示例

	def iniciar (self) : # definicion del metodo
		menu = '1 : añadirDVD - 2 : editarDVD - 3 : buscarDVD - 4 : listaDVDs - 5 : borrarDVD - S : salir \n' # asigna cadena menu
		if os.path.exists ('./Escritorio/baseDatos.sql') : # condicion , si existe el fichero en la ruta indicada devuelve True
			baseDATOS_SQL = sqlite3.connect ('./Escritorio/baseDatos.sql') # abre el fichero creado con el modulo sqlite3 , si no existe lo crea 
		else : # si no existe ; False
			baseDATOS_SQL = sqlite3.connect ('./Escritorio/baseDatos.sql') # abre el fichero creado con el modulo sqlite3 , si no existe lo crea 
			cursor = baseDATOS_SQL.cursor () # crea el objeto que ejecuta las llamadas SQL 
			cursor.execute ('CREATE TABLE dvds (titulo TEXT UNIQUE NOT NULL,director TEXT NOT NULL,año INTEGER NOT NULL,duracion INTEGER NOT NULL)') # ejecuta las llamadas SQL en la base de datos del objeto -crea la tabla dvds con las columnas ; titulo , director , año ,duracion-
			baseDATOS_SQL.commit () # guarda los cambios en el fichero SQL del objeto
		while True : # bucle while continuo
			print (menu) # presenta el menu
			entradaTeclado = input ('entre opcion >> ') # espera entrada teclado 
			print ('\n') # salto de linea
			if entradaTeclado == '1' : # condicion , si se cumple
				self.añadirDVD (baseDATOS_SQL) # llama al metodo con el argumento indicado -objeto sqlite3-
			elif entradaTeclado == '2' : # condicion , si se cumple
				self.editarDVD (baseDATOS_SQL) # llama al metodo con el argumento indicado -objeto sqlite3-
			elif entradaTeclado == '3' : # condicion , si se cumple
				self.buscarDVD (baseDATOS_SQL) # llama al metodo con el argumento indicado -objeto sqlite3-
			elif entradaTeclado == '4' : # condicion , si se cumple
				self.listaDVDs (baseDATOS_SQL) # llama al metodo con el argumento indicado -objeto sqlite3-
			elif entradaTeclado == '5' : # condicion , si se cumple
				self.borrarDVD (baseDATOS_SQL) # llama al metodo con el argumento indicado -objeto sqlite3-
			elif entradaTeclado == 'S' or entradaTeclado == 's' : # condicion , si se cumple
				baseDATOS_SQL.close () # cierra el fichero de la base de datos
				sys.exit (1) # sale del script
			else : # ninguna de las opciones indicadas
				continue # vuelve al inicio del bucle

def alias_register(alias_desired):
    reg_string = "alias=" + alias_desired

    if full_ledger == 1:
        conn = sqlite3.connect('static/ledger.db')
    else:
        conn = sqlite3.connect('static/hyper.db')
    conn.text_factory = str
    c = conn.cursor()

    m.execute("SELECT timestamp FROM transactions WHERE openfield = ?;", (reg_string,))
    registered_pending = m.fetchone()

    c.execute("SELECT timestamp FROM transactions WHERE openfield = ?;", (reg_string,))
    registered_already = c.fetchone()

    if registered_already == None and registered_pending == None:
        alias_cb_var.set(0)
        send_confirm("0", address, "1", reg_string)

    else:
        top9 = Toplevel()
        top9.title("Name already registered")

        registered_label = Label(top9, text="Name already registered")
        registered_label.grid(row=0, column=0, sticky=N + W, padx=15, pady=(5, 0))
        dismiss = Button(top9, text="Dismiss", command=top9.destroy)
        dismiss.grid(row=3, column=0, sticky=W + E, padx=15, pady=(5, 5))

 def _getDb(self, channel):
     filename = plugins.makeChannelFilename(self.filename, channel)
     if filename in self.dbs:
         return self.dbs[filename]
     if os.path.exists(filename):
         db = sqlite3.connect(filename)
         db.text_factory = str
         self.dbs[filename] = db
         return db
     db = sqlite3.connect(filename)
     db.text_factory = str
     self.dbs[filename] = db
     cursor = db.cursor()
     cursor.execute("""CREATE TABLE karma (
                       id INTEGER PRIMARY KEY,
                       name TEXT,
                       normalized TEXT UNIQUE ON CONFLICT IGNORE,
                       added INTEGER,
                       subtracted INTEGER
                       )""")
     db.commit()
     def p(s1, s2):
         return int(ircutils.nickEqual(s1, s2))
     db.create_function('nickeq', 2, p)
     return db

def patent_hunter(option=0, patinfo=""):
    print 'option : ', option
    print 'patent info : ', patinfo
    assert option!=0 and patinfo!="" # option must not be zero
    writer = csv.writer(open('./result.csv','w'))
    psyinfo = '06304546'

    # Citation sqlite3
    if option == 'patnum' or option == '1':
        opensql = sqlite3.connect("/home/minyoon/citation.sqlite3")
    # Inventor sqlite3
    elif option == 'inventor' or option == '2':
        opensql = sqlite3.connect("/home/minyoon/full_disambiguation.sqlite3")

    if opensql!=None:
        cur = opensql.cursor()
        if option == 'patent' or option == '1':
            # list of patent that this patent is referencing
            #cur.execute("SELECT Citation FROM citation WHERE Patent=? LIMIT 50", (patinfo,))
            # list of patent that references this patent
            cur.execute("SELECT Patent FROM citation WHERE Citation=?", (patinfo,))
        elif option == 'inventor' or option == '2':
            cur.execute("SELECT Patent FROM invpat WHERE Lastname = ? Limit 50", (patinfo,))

        for entry in cur.fetchall():
            print entry
    return 1

 def init_db(self):
     db = os.path.join(self.local_path, "db")
     if os.access(db, os.R_OK):
         self.conn = sqlite3.connect(db)
         self.c = self.conn.cursor()
         self.c.execute(
             """
         select root_dir, backup_dir, freq, start_time
         from backup_unit"""
         )
         for row in self.c:
             task = Task(row[0], row[1], row[2], row[3])
             self.task_list.append([task] + list(row))
     else:
         self.conn = sqlite3.connect(db)
         self.c = self.conn.cursor()
         self.c.executescript(
             """
         create table backup_unit(
         root_dir varchar, backup_dir varchar, freq varchar, start_time varchar);
         
         create table snapshot_unit(
         root_dir varchar, snapshot varchar, cur_time varchar);
         
         create table log(
         user varchar, message varchar, time varchar);
         
         create table dir_state(
         root_dir varchar, cur_state varchar);
         """
         )

def initDB():
	#データベースにファイルを作る
	if not os.path.exists(u"data.db"):
		con = sqlite3.connect(u"data.db")
		con.execute(u"""
		create table Picture(
			path_orig varchar(128),
			path_link varchar(128),
			path_thumb1 varchar(128),
			path_thumb2 varchar(128),
			flag integer,
			last_update real,
			tags varchar(128),
			dir varchar(128),
			name varchar(128),
			primary key (path_orig)
		);
		""")
		con.execute(u"""
		create table Dir(
			path_orig varchar(128),
			flag integer,
			last_update varchar(128),
			date varchar(128),
			name varchar(128),
			fileNum integer,
			primary key (path_orig)
		);
		""")
	else:
		con = sqlite3.connect(u"data.db")
	con.close()

def mainpage(imagename=None):
    if request.method == 'GET':
        if imagename:
            imgname=(imagename,)
            con = sqlite3.connect('Image.db')
            cur = con.cursor()
            cur.execute("SELECT * FROM Image WHERE imgname=?", imgname)
            rows = cur.fetchall()
            if rows:
                for row in rows:
                    imgname = row[0]
                    imgdata = row[1]
                return render_template('paint.html', saved=imgdata)
            else:
                resp = Response("""<html> <script> alert("Image not found");document.location.href="/" </script> </html>""")
                return resp
        else:
            return render_template('paint.html')
    if request.method == 'POST':
        imgname=request.form['imagename']
        imgdata=request.form['string']
	data=(imgname, imgdata)
	con = sqlite3.connect("Image.db")
        cur=con.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS Image(imgname text, imgdata string)")
        cur.execute("INSERT INTO Image VALUES(?, ?)", data)
        con.commit()
        con.close()
        resp = Response("saved")
        return resp

    def test_new_geopackage(self):
        assert os.path.exists(self.gpkg_file)

        with sqlite3.connect(self.gpkg_file) as db:
            cur = db.execute('''SELECT name FROM sqlite_master WHERE type='table' AND name=?''',
                             (self.table_name,))
            content = cur.fetchone()
            assert content[0] == self.table_name

        with sqlite3.connect(self.gpkg_file) as db:
            cur = db.execute('''SELECT table_name, data_type FROM gpkg_contents WHERE table_name = ?''',
                             (self.table_name,))
            content = cur.fetchone()
            assert content[0] == self.table_name
            assert content[1] == 'tiles'

        with sqlite3.connect(self.gpkg_file) as db:
            cur = db.execute('''SELECT table_name FROM gpkg_tile_matrix WHERE table_name = ?''',
                             (self.table_name,))
            content = cur.fetchall()
            assert len(content) == 20

        with sqlite3.connect(self.gpkg_file) as db:
            cur = db.execute('''SELECT table_name FROM gpkg_tile_matrix_set WHERE table_name = ?''',
                             (self.table_name,))
            content = cur.fetchone()
            assert content[0] == self.table_name

def regendb2():

    conn = sqlite3.connect('list2.db')
    c = conn.cursor()
    c.execute("select * from statics")
    if os.path.exists('list3.db')==False:
        conn2 = sqlite3.connect('list3.db')
        c2 = conn2.cursor()
        c2.execute("CREATE TABLE statics (id,name,sex,class,major,college,collegeid)")
    else:
        conn2 = sqlite3.connect('list3.db')
        c2 = conn2.cursor()
    
    for result in c.fetchall():
        studentid=result[0]
        studentname=result[1]
        studentsex=result[2]
        studentclass=result[3]
        studentmajor=result[4]
        studentcollege=result[5]
        collegeid=result[6]
        if studentsex=="男".decode("utf8"):
            ensex=0
        elif studentsex=="女".decode("utf8"):
            ensex=1
        c2.execute("insert into statics values ('"+studentid+"','"+studentname+"','"+str(ensex)+"','"+studentclass+"','"+studentmajor+"','"+studentcollege+"','"+str(collegeid)+"')")

    conn2.commit()
    c2.close()
    conn2.close() 
    conn.commit()
    c.close()
    conn.close() 

	def __init__(self,string):

		if not ( os.path.isfile("{0}/{1}.db".format(self.LOCAL,string)) ):
			self.conn = sqlite3.connect(string + ".db")
			self.c = self.conn.cursor()
			self.c.execute(''' CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT, name text, cpf text, data_nascimento date)''')
	
			self.conn.commit()
			self.dbname = string
		
		elif os.path.isfile( "{0}/{1}.db".format(self.LOCAL,string) ):
			self.conn = sqlite3.connect(string + ".db")
			self.c = self.conn.cursor()
			self.dbname = string

		elif os.path.isfile( "{0}/appdb.db".format(self.LOCAL) ):
			self.conn = sqlite3.connect("appdb.db")
			self.c = self.conn.cursor()
			self.dbname = "appdb"

		else:
			self.conn = sqlite3.connect("appdb.db")
			self.c = self.conn.cursor()
			self.c.execute(''' CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT, name text, cpf text, data_nascimento date)''')
	
			conn.commit()
			self.dbname = "appdb"

 def open(self):
     if os.path.exists(self.filename):
         db = sqlite3.connect(self.filename, check_same_thread = False)
         db.text_factory = str
         self.db = db
         return
     
     db = sqlite3.connect(self.filename, check_same_thread = False)
     db.text_factory = str
     self.db = db
     cursor = self.db.cursor()
     cursor.execute("""CREATE TABLE users (
                       id INTEGER PRIMARY KEY,
                       total_rating INTEGER,
                       created_at INTEGER,
                       pos_rating_recv_count INTEGER,
                       neg_rating_recv_count INTEGER,
                       pos_rating_sent_count INTEGER,
                       neg_rating_sent_count INTEGER,
                       nick TEXT UNIQUE ON CONFLICT REPLACE,
                       host TEXT)
                        """)
     cursor.execute("""CREATE TABLE ratings (
                       id INTEGER PRIMARY KEY,
                       rated_user_id INTEGER,
                       rater_user_id INTEGER,
                       created_at INTEGER,
                       rating INTEGER,
                       notes TEXT)
                       """)
     self._commit()
     return

 def __init__(self, path):
     conn = sqlite3.connect(path, check_same_thread=False)
     if os.path.exists(path) and os.path.isfile(path):
         self.conn = conn
     else:
         Log.error('Create sqlite3 file fail. use memory!')
         self.conn = sqlite3.connect(':memory:')

def searching(self,entry,model):
    lista_hist[:] = []
    lista_histfull[:] = []


    connb = sqlite3.connect(path + 'turbo.db')
    a = connb.cursor()    
    a.execute("SELECT id,nome,conteudo,data,tipo,ip FROM history where conteudo like '%" + entry.get_text() + "%' order by id desc")
    rows =  a.fetchall()
    for history in rows:
        if history[4] == "S":
            lista_hist.append([str(history[0]),history[1],cap(history[2],200),history[3],ico_send,history[5]])
        else:
            lista_hist.append([str(history[0]),history[1],cap(history[2],200),history[3],ico_receive,history[5]])

    connc = sqlite3.connect(path + 'turbo.db')
    c = connc.cursor()
    c.execute("SELECT id,nome,conteudo,data,tipo,ip FROM history where conteudo like '%" + entry.get_text() + "%' order by id desc")
    rowsc =  c.fetchall()
    for history in rowsc:
        if history[4] == "S":
            lista_histfull.append([str(history[0]),history[1],history[2].encode("utf-8"),history[3],ico_send,history[5]])
        else:
            lista_histfull.append([str(history[0]),history[1],history[2].encode("utf-8"),history[3],ico_receive,history[5]])

    connb.close()

    if len(model) != 0:
        for i in range(len(model)):
            iter = model.get_iter(0)
            model.remove(iter)                

    for i in range(len(lista_hist)):
        model.append(lista_hist[i])

 def add_cb(self, button,column,treesortable):
     nome = self.nometxt.get_text()
     connb = sqlite3.connect(path + 'turbo.db')
     a = connb.cursor()
     a.execute("SELECT nome FROM contacts WHERE upper(nome) = upper('" + nome + "')")
     contactsName =  a.fetchone()
     connb.close()
     if not contactsName:
         if nome != "nome":
             try:
                 IP = socket.gethostbyname(nome)
                 connc = sqlite3.connect(path + 'turbo.db')
                 c = connc.cursor()
                 c.execute("INSERT INTO contacts (nome,ip) VALUES (?,?)",(nome.upper(),IP))
                 connc.commit()
                 connc.close()
                 command = "mkdir " + path_attached + nome.upper()
                 os.system(command)
                 self.listmodel.append([nome.upper(),IP])     
                 treesortable.set_sort_column_id(1, Gtk.SortType.ASCENDING)          
             except socket.gaierror, err:    
                 msgerror = "I can't ping this host name!\\nPlease chech the host name and try again!";                      
                 command = "notify-send --hint=int:transient:1 \"TurboNote Gnome3\" \"" + (msgerror).decode('iso-8859-1').encode('utf8') + "\" -i " + path_icon + "turbo.png"                  
                 os.system(command)            
         else:
             msgerror = "No data Found!\\nPlease i need contact name and ip adress for save contact!";                      
             command = "notify-send --hint=int:transient:1 \"TurboNote Gnome3\" \"" + (msgerror).decode('iso-8859-1').encode('utf8') + "\" -i " + path_icon + "turbo.png"                  
             os.system(command)    

 def makeDb(self, filename):
     if os.path.exists(filename):
         db = sqlite3.connect(filename)
         if sys.version_info[0] < 3:
             db.text_factory = str
         return db
     db = sqlite3.connect(filename)
     if sys.version_info[0] < 3:
         db.text_factory = str
     cursor = db.cursor()
     cursor.execute("""CREATE TABLE keys (
                       id INTEGER PRIMARY KEY,
                       key TEXT UNIQUE ON CONFLICT REPLACE
                       )""")
     cursor.execute("""CREATE TABLE factoids (
                       id INTEGER PRIMARY KEY,
                       added_by TEXT,
                       added_at TIMESTAMP,
                       fact TEXT UNIQUE ON CONFLICT REPLACE,
                       locked BOOLEAN
                       )""")
     cursor.execute("""CREATE TABLE relations (
                       id INTEGER PRIMARY KEY,
                       key_id INTEGER,
                       fact_id INTEGER,
                       usage_count INTEGER
                       )""")
     db.commit()
     return db