Python sqlite.connect函数代码示例

 def _getDb(self, channel):
     try:
         import sqlite
     except ImportError:
         raise callbacks.Error, 'You need to have PySQLite installed to ' \
                                'use Karma.  Download it at ' \
                                '<http://pysqlite.org/>'
     filename = plugins.makeChannelFilename(self.filename, channel)
     if filename in self.dbs:
         return self.dbs[filename]
     if os.path.exists(filename):
         self.dbs[filename] = sqlite.connect(filename)
         return self.dbs[filename]
     db = sqlite.connect(filename)
     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 makeDb(self, filename):
     if os.path.exists(filename):
         return sqlite.connect(filename)
     db = sqlite.connect(filename)
     cursor = db.cursor()
     cursor.execute(
         """CREATE TABLE keys (
                       id INTEGER PRIMARY KEY,
                       key TEXT UNIQUE ON CONFLICT IGNORE,
                       locked BOOLEAN
                       )"""
     )
     cursor.execute(
         """CREATE TABLE factoids (
                       id INTEGER PRIMARY KEY,
                       key_id INTEGER,
                       added_by TEXT,
                       added_at TIMESTAMP,
                       fact TEXT
                       )"""
     )
     cursor.execute(
         """CREATE TRIGGER remove_factoids
                       BEFORE DELETE ON keys
                       BEGIN
                         DELETE FROM factoids WHERE key_id = old.id;
                       END
                    """
     )
     db.commit()
     return db

 def _getDb(self, channel):
     try:
         import sqlite
     except ImportError:
         raise callbacks.Error, 'You need to have PySQLite installed to ' \
                                'use QuoteGrabs.  Download it at ' \
                                '<http://pysqlite.org/>'
     filename = plugins.makeChannelFilename(self.filename, channel)
     def p(s1, s2):
         return int(ircutils.nickEqual(s1, s2))
     if filename in self.dbs:
         return self.dbs[filename]
     if os.path.exists(filename):
         self.dbs[filename] = sqlite.connect(filename,
                                             converters={'bool': bool})
         self.dbs[filename].create_function('nickeq', 2, p)
         return self.dbs[filename]
     db = sqlite.connect(filename, converters={'bool': bool})
     self.dbs[filename] = db
     self.dbs[filename].create_function('nickeq', 2, p)
     cursor = db.cursor()
     cursor.execute("""CREATE TABLE quotegrabs (
                       id INTEGER PRIMARY KEY,
                       nick TEXT,
                       hostmask TEXT,
                       added_by TEXT,
                       added_at TIMESTAMP,
                       quote TEXT
                       );""")
     db.commit()
     return db

 def _getDb(self, channel):
     try:
         import sqlite
     except ImportError:
         raise callbacks.Error, 'You need to have PySQLite installed to ' \
                                'use Poll.  Download it at ' \
                                '<http://pysqlite.org/>'
     filename = plugins.makeChannelFilename(self.filename, channel)
     if filename in self.dbs:
         return self.dbs[filename]
     if os.path.exists(filename):
         self.dbs[filename] = sqlite.connect(filename)
         return self.dbs[filename]
     db = sqlite.connect(filename)
     self.dbs[filename] = db
     cursor = db.cursor()
     cursor.execute("""CREATE TABLE polls (
                       id INTEGER PRIMARY KEY,
                       question TEXT UNIQUE ON CONFLICT IGNORE,
                       started_by INTEGER,
                       open INTEGER)""")
     cursor.execute("""CREATE TABLE options (
                       id INTEGER,
                       poll_id INTEGER,
                       option TEXT,
                       UNIQUE (poll_id, id) ON CONFLICT IGNORE)""")
     cursor.execute("""CREATE TABLE votes (
                       user_id INTEGER,
                       poll_id INTEGER,
                       option_id INTEGER,
                       UNIQUE (user_id, poll_id)
                       ON CONFLICT IGNORE)""")
     db.commit()
     return db

def db1():
    import sqlite
    conn = sqlite.connect('languagedb') # create file if it doesn't exist
    conn.execute("create table if not exists language_strings(id, language, string)") #create table
    data = []

    for x in xrange (0,1000):
        str1 = (u'STRING#' + str(x))
        data += [(x, u'ENG', str1)] # data for table

    #print data
    conn.executemany("insert into language_strings(id, language, string) values (?,?,?)", data) #record to DB
    conn.commit() # save
    conn.close() # close

    conn = sqlite.connect('languagedb') # open DB
    for row in conn.execute("select * from language_strings"): # regular request
        print row[0], row[1], row[2]
        print '==='

        #conn.row_factory = sqlite3.Row # create the fabric f ROW
        #cur = conn.cursor() # create a cursore
        #cur.execute("select * from person")
        #for row in cur:
        #    print row['firstname'], row[1]
    conn.close()  # close DB

 def _getDb(self, channel):
     try:
         import sqlite
     except ImportError:
         raise callbacks.Error, \
               'You need to have PySQLite installed to use this ' \
               'plugin.  Download it at <http://pysqlite.org/>'
     if channel in self.dbs:
         return self.dbs[channel]
     filename = plugins.makeChannelFilename(self.filename, channel)
     if os.path.exists(filename):
         self.dbs[channel] = sqlite.connect(filename)
         return self.dbs[channel]
     db = sqlite.connect(filename)
     self.dbs[channel] = db
     cursor = db.cursor()
     cursor.execute("""CREATE TABLE factoids (
                       key TEXT PRIMARY KEY,
                       created_by INTEGER,
                       created_at TIMESTAMP,
                       modified_by INTEGER,
                       modified_at TIMESTAMP,
                       locked_at TIMESTAMP,
                       locked_by INTEGER,
                       last_requested_by TEXT,
                       last_requested_at TIMESTAMP,
                       fact TEXT,
                       requested_count INTEGER
                       )""")
     db.commit()
     return db

    def create_sqlite(self, file_path, sql_file, autocommit=0):
        if os.path.exists(file_path):
            print >>sys.stderr, "sqlite db already exists", os.path.abspath(file_path)
            return False
        db_dir = os.path.dirname(os.path.abspath(file_path))
        if not os.path.exists(db_dir):
            os.makedirs(db_dir)

        self.sdb = sqlite.connect(file_path, isolation_level=None)    # auto-commit
        self.cur = self.sdb.cursor()
        
        f = open(sql_file)
        sql_create_tables = f.read()
        f.close()
        
        sql_statements = sql_create_tables.split(';')
        for sql in sql_statements:
            self.cur.execute(sql)
        
        self._commit()
        self.sdb.close()
        
        self.sdb = sqlite.connect(file_path)    # auto-commit
        self.cur = self.sdb.cursor()
        
        return True

def TEST(output=log):
    LOGGING_STATUS[DEV_SELECT] = 1
    LOGGING_STATUS[DEV_UPDATE] = 1

    print "------ TESTING MySQLdb ---------"
    rdb = MySQLdb.connect(host="localhost", user="root", passwd="", db="testdb")
    ndb = MySQLdb.connect(host="localhost", user="trakken", passwd="trakpas", db="testdb")
    cursor = rdb.cursor()

    output("drop table agents")
    try:
        cursor.execute("drop table agents")  # clean out the table
    except:
        pass
    output("creating table")

    SQL = """

    create table agents (
       agent_id integer not null primary key auto_increment,
       login varchar(200) not null,
       unique (login),
       ext_email varchar(200) not null,
       hashed_pw varchar(20) not null,
       name varchar(200),
       auth_level integer default 0,
       ticket_count integer default 0)
       """

    cursor.execute(SQL)
    db = odb_mysql.Database(ndb)
    TEST_DATABASE(rdb, db, output=output)

    print "------ TESTING sqlite ----------"
    rdb = sqlite.connect("/tmp/test.db", autocommit=1)
    cursor = rdb.cursor()
    try:
        cursor.execute("drop table agents")
    except:
        pass
    SQL = """
    create table agents (
       agent_id integer primary key,
       login varchar(200) not null,
       ext_email varchar(200) not null,
       hashed_pw varchar(20),
       name varchar(200),
       auth_level integer default 0,
       ticket_count integer default 0)"""
    cursor.execute(SQL)
    rdb = sqlite.connect("/tmp/test.db", autocommit=1)
    ndb = sqlite.connect("/tmp/test.db", autocommit=1)

    db = odb_sqlite.Database(ndb)
    TEST_DATABASE(rdb, db, output=output, is_mysql=0)

 def opendb(self):
     """
     Öffnet eine DB Schnittstelle
     Benötigt für sqlite
     """
     if self.backend == "sqlite":
         if not os.path.exists(config.index_file):
             # Erstellt die Tabelle
             file(config.index_file, "w")
             self.con = sqlite.connect(config.index_file)
             self.cur = self.con.cursor()
             self.cur.execute('CREATE TABLE pds(file VARCHAR(100), keywords VARCHAR(50))')
         else:
             self.con = sqlite.connect(config.index_file)
             self.cur = self.con.cursor()

def insert_to_db(bug_number,link,summary,description):


    if check_requirements(bug_number)==1:

        con = sqlite.connect('testdata/tests.db')

        cur=con.cursor()

        cur.execute("select * from tests where bug_number='%s'"%(bug_number))

        if cur.rowcount==0:
            cur.execute("insert into tests(bug_number,link,summary,description) values('%s','%s','%s','%s')"%(bug_number,link,summary,description))
            con.commit()
        else:
            print ""
            print "Regression test for bug %s is already registered."%(bug_number)
            print ""

        con.close()

    else:

        print ""
        print "It seems there is no module %s at bugs package.\nUnable to complete the regression test registration."%(bug_number)
        print ""

 def _loadDB(self, db):
     try:
         self.store = sqlite.connect(db=db)
         #self.store.autocommit = 0
     except:
         import traceback
         raise KhashmirDBExcept, "Couldn't open DB", traceback.exc_traceback

def connect_by_uri(uri):
    puri = urisup.uri_help_split(uri)
    opts = __dict_from_query(puri[QUERY])
    con = sqlite.connect(puri[PATH], client_encoding='utf8')
    if "timeout_ms" in opts:
        con.db.sqlite_busy_timeout(int(opts["timeout_ms"]))
    return con

def run_viewer(options,args):
	import sqlite
	time_format = options.tformat
	sql = "select * from msglog where rowid>"
	sql += "(select max(rowid) from msglog) - %s" % (options.tail,)
	run = 1
	last_id = 0
	failures = 0
	DB = sqlite.connect(options.db)
	cursor = DB.cursor()
	while run:
		if not os.path.exists(options.db):
			# Must do this because removing watched msglog
			#   causes all cpu to be used and machine crash.
			raise IOError('Database does not exist.  Exiting.')
		try:
			try:
				cursor.execute(sql)
				failures = 0
			except sqlite.OperationalError,e:
				if failures >= 10:
					print 'Got OperationalError 10+ times: %r' % e
				failures += 1
			except sqlite.DatabaseError,e:
				print 'Got DatabaseError "%s".  Retrying in 5 seconds.' % e
				time.sleep(5)
				reload(sqlite)
				continue
			for row in cursor:
				print_row(row,time_format)
				last_id = row[0]
			run = options.follow
			sql = 'select * from msglog where rowid>%s' % (last_id)

 def connect(self, dbname=None, dbhost=None, dbuser=None, dbpasswd=None, timeout=15, oldstyle=False):
     """ connect to the database. """
     self.dbname = dbname or self.config['dbname']
     self.dbhost = dbhost or self.config['dbhost']
     self.dbuser = dbuser or self.config['dbuser']
     self.dbpasswd = dbpasswd or self.config['dbpasswd']
     self.timeout = timeout
     self.oldstyle = oldstyle or self.config['dboldstyle']
     if self.dbtype == 'mysql':
         import MySQLdb
         self.connection = MySQLdb.connect(db=self.dbname, host=self.dbhost, user=self.dbuser, passwd=self.dbpasswd, connect_timeout=self.timeout, charset='utf8')
     elif 'sqlite' in self.dbtype:
         try:
             import sqlite
             self.connection = sqlite.connect(self.datadir + os.sep + self.dbname)
         except ImportError:
             import sqlite3
             self.connection = sqlite3.connect(self.datadir + os.sep + self.dbname, check_same_thread=False)
     elif self.dbtype == 'postgres':
         import psycopg2
         rlog(1000, 'db', 'NOTE THAT POSTGRES IS NOT FULLY SUPPORTED')
         self.connection = psycopg2.connect(database=self.dbname, host=self.dbhost, user=self.dbuser, password=self.dbpasswd)
     else:
         rlog(100, 'db', 'unknown database type %s' % self.dbtype)
         return 0
     rlog(10, 'db', "%s database ok" % self.dbname)
     return 1

    def create(self, admin_passwd, admin_email):
        """Create the contest. This also opens it.
        
        @param admin_passwd: Password for 'admin' account
        @param admin_email:  Emailid of 'admin' account
        """
        if True in [x.isspace() for x in self.name]:
            raise ValueError, 'contest_name must not contain any white space'
        
        # Create directories
        dirs = [self.directory]             # Top-level
        dirs.extend(self.dirs.values())     # others ..
        [os.mkdir(dr) for dr in dirs]

        # Init DB
        dbpath = join(self.dirs['repos'], self.name) # Only one database
        db = sqlite.connect(dbpath, autocommit=True)
        cursor = db.cursor()
        queries = _get_queries(file(join(paths.DATA_DIR, 'contest-sqlite.sql')))
        [cursor.execute(query) for query in queries]
        cursor.close()
        db.close()


        # Open the contest and add admin account to db
        self.open()
        # We use *_sync method as we don't use Twisted's reactor yet!!
        self.dbproxy.add_user_sync('admin', admin_passwd, 'Contest Admin', 
                              admin_email, USER_ADMIN)