def sql_open_connection(self): """Open a standard, non-autocommitting connection. pysqlite will automatically BEGIN TRANSACTION for us. """ # make sure the database directory exists # database itself will be created by sqlite if needed if not os.path.isdir(self.config.DATABASE): os.makedirs(self.config.DATABASE) db = os.path.join(self.config.DATABASE, "db") logging.getLogger("hyperdb").info("open database %r" % db) # set a 30 second timeout (extraordinarily generous) for handling # locked database if sqlite_version == 1: conn = sqlite.connect(db=db) conn.db.sqlite_busy_handler(self.sqlite_busy_handler) else: conn = sqlite.connect(db, timeout=30) conn.row_factory = sqlite.Row # sqlite3 wants us to store Unicode in the db but that's not what's # been done historically and it's definitely not what the other # backends do, so we'll stick with UTF-8 if sqlite_version == 3: conn.text_factory = str cursor = conn.cursor() return (conn, cursor)
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 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 _getDb(self, channel): try: import sqlite except ImportError: raise callbacks.Error, 'You need to have PySQLite installed to ' \ 'use Karma. Download it at ' \ '<http://code.google.com/p/pysqlite/>' 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 _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 _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 QuoteGrabs. Download it at ' \ '<http://code.google.com/p/pysqlite/>' 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 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 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 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 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 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="******", passwd="", db="testdb") ndb = MySQLdb.connect(host="localhost", user="******", 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 getConnection(self): if self.conn == None: try: self.conn = sqlite.connect(db=self.db_file) except Exception, e: log("Exception raised in sqlite.connect()", MANDATORY) raise
def insert(self, objeto): try: banco = sqlite.connect('banco.db') cursor = banco.cursor() insert = u'''insert into Registro (id, registro, tipo, nome, curso, data_registro, data_saida, status, observacoes) values (%d,%d,%d,"%s","%s","%s","%s",%d,"%s");''' % ( objeto['id'], objeto['registro'], objeto['tipo'], objeto['nome'], objeto['curso'], objeto['data_registro'], objeto['data_saida'], objeto['status'], objeto['observacoes'], ) cursor.execute(insert) banco.commit() return True except Exception, e: warning = QMessageBox.warning( None, "Database error", u"Erro de inserção no banco de dados. O erro foi:\n\n%s" % e, QMessageBox.Ok) return ~warning
def convert_from_old_db(config, source_file, destination_file, locations): # {{{ """ convert .gri database into .bd one """ log.info("Converting old database - it can take several minutes...") log.debug("Source file: %s", source_file) gutils.info( _( "Griffith will now convert your database to the new format. This can take several minutes if you have a large database." ) ) from sql import GriffithSQL from gutils import digits_only import os if not os.path.isfile(source_file): return False if "home" not in locations: log.error("locations doesn't contain home path, cannot convert old database") return False if open(source_file).readline()[:47] == "** This file contains an SQLite 2.1 database **": log.debug("SQLite 2.1 detected") try: import sqlite except ImportError: log.error("Old DB conversion: please install pysqlite legacy (v1.0)") gutils.warning(_("Old DB conversion: please install pysqlite legacy (v1.0)")) return False else: try: # Python 2.5 from sqlite3 import dbapi2 as sqlite except ImportError: # Python < 2.5 - try to use pysqlite2 from pysqlite2 import dbapi2 as sqlite if os.path.isfile(destination_file): # rename destination_file if it already exist i = 1 while True: if os.path.isfile("%s_%s" % (destination_file, i)): i += 1 else: break os.rename(destination_file, "%s_%s" % (destination_file, i)) try: old_db = sqlite.connect(source_file) except sqlite.DatabaseError, e: if str(e) == "file is encrypted or is not a database": print "Your database is most probably in SQLite2 format, please convert it to SQLite3:" print "$ sqlite ~/.griffith/griffith.gri .dump | sqlite3 ~/.griffith/griffith.gri3" print "$ mv ~/.griffith/griffith.gri{,2}" print "$ mv ~/.griffith/griffith.gri{3,}" print "or install pysqlite in version 1.0" gutils.warning(_("Your database is most probably in SQLite2 format, please convert it to SQLite3")) else: raise return False
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 get_db(self): if self.use_sqlite: import sqlite return sqlite.connect(self.sqlite_database) else: import MySQLdb return MySQLdb.connect(read_default_file=self.mysql_config)
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 setDb(self): import sqlite, os dbfile = os.path.join(os.curdir, pluginConf.database()) try: os.remove(dbfile) except: pass db = sqlite.connect(dbfile) cursor = db.cursor() cursor.execute('CREATE TABLE bans (' 'id INTEGER PRIMARY KEY,' 'channel VARCHAR(30) NOT NULL,' 'mask VARCHAR(100) NOT NULL,' 'operator VARCHAR(30) NOT NULL,' 'time VARCHAR(300) NOT NULL,' 'removal DATETIME,' 'removal_op VARCHAR(30),' 'log TEXT)') cursor.execute('CREATE TABLE comments (' 'ban_id INTEGER,' 'who VARCHAR(100) NOT NULL,' 'comment MEDIUMTEXT NOT NULL,' 'time VARCHAR(300) NOT NULL)') cursor.execute('CREATE TABLE sessions (' 'session_id VARCHAR(50) PRIMARY KEY,' 'user MEDIUMTEXT NOT NULL,' 'time INT NOT NULL)') cursor.execute('CREATE TABLE users (' 'username VARCHAR(50) PRIMARY KEY,' 'salt VARCHAR(8),' 'password VARCHAR(50))') db.commit() cursor.close() db.close()
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)
def update(self, objeto): try: banco = sqlite.connect('banco.db') cursor = banco.cursor() update = u'''update Solicitacao set ( "id"=%d, "nome"="%s", "curso"="%s", "data"="%s", "certidao"=%d, "declaracao"=%d, "diploma"=%d, "historico"=%d, "outros"=%d, "urgencia"=%d, "observacoes"="%s" ) where (registro = %d);''' % ( objeto['id'], objeto['nome'], objeto['curso'], objeto['data'], objeto['certidao'], objeto['declaracao'], objeto['diploma'], objeto['historico'], objeto['outros'], objeto['urgencia'], objeto['observacoes'], ) cursor.execute(update) banco.commit() return True except Exception, e: warning = QMessageBox.warning( None, "Database error", "Erro de atualização no banco de dados. O erro foi:\n\n%s" % e, QMessageBox.Ok) return ~warning
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 main(): connection = sqlite.connect(DB) get_all_types(connection) get_recycleable(connection, 15) get_synonyms(connection, 15) get_hazarduos_materials(connection, 0) connection.close()
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 drop_g(): con = sqlite.connect("g.sqlite") cur = con.cursor() cur.execute("drop table if exists hash_dir;") cur.close() con.commit() con.close()
def _connectDB(res): if config['SQLEngenie'] == "SQLite": if config['debug']: res.Messages.append("DEBUG: Connect to sqlite db: " + config['SQLiteBase']) SQLConnect = sqlite3.connect(config['SQLiteBase']) SQLConnect.isolation_level = None elif config['SQLEngenie'] == "MySQL": if config['debug']: res.Messages.append( "DEBUG: Connect to mysql db: %s, host: %s, user %s" % (config['SQLDB'], config['SQLHost'], config['SQLUser'])) SQLConnect = MySQLdb.connect(host=config['SQLHost'], user=config['SQLUser'], passwd=config['SQLPassword'], db=config['SQLDB'], charset='utf8') else: res.StatusOK = False res.Messages.append("ERROR: No select valid DB engenie") return None return SQLConnect
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 __init__(self, filename): import sqlite self.con = sqlite.connect(filename) self.cur = self.con.cursor() # create hits table if it does not exist self.cur.execute("SELECT name FROM sqlite_master WHERE type = 'table'") tables = self.cur.fetchall() if ("hits",) not in tables: self.cur.execute( """ CREATE TABLE hits ( query TEXT, subject TEXT, percid NUMBER, alignlen INT, mismatches INT, gaps INT, qstart INT, qend INT, sstart INT, send INT, evalue NUMBER, bitscore NUMBER ) """ )
def handle_url(bot, user, channel, url, msg): if not config: return ret = None urlid = "%s|%s" % (channel, url) con = sqlite.connect(os.path.join(sys.path[0], "urls.sqlite")) cur = con.cursor() cur.execute("SELECT * FROM urls WHERE id=%s", (urlid,)) if cur.rowcount: id, userhost, url, channel, timestamp = cur.fetchone() pastetime = datetime.datetime.fromtimestamp(timestamp) now = datetime.datetime.now() age = now - pastetime agestr = "" if age.days > 0: agestr += "%d days " % age.days secs = age.seconds hours, minutes, seconds = secs // 3600, secs // 60 % 60, secs % 60 if hours > 0: agestr += "%d h " % hours if minutes > 0: agestr += "%d m " % minutes if seconds > 0: agestr += "%d s" % seconds # don't alert for the same person if getNick(user) != getNick(userhost) and channel not in config.get("channels", []): ret = bot.say(channel, "%s: wanha. (by %s %s ago)" % (getNick(user), getNick(userhost), agestr)) else: cur.execute("INSERT INTO urls VALUES(%s, %s, %s, %s, %d)", (urlid, user, url, channel, int(time.time()))) con.commit() cur.close() con.close() return ret
def insert(self, objeto): try: banco = sqlite.connect('banco.db') cursor = banco.cursor() insert = u'''insert into Solicitacao (id, nome, curso, data, certidao, declaracao, diploma, historico, outros, urgencia, observacoes) values ( %d, "%s", "%s", "%s", %d, %d, %d, %d, %d, %d, "%s" );''' % ( objeto['id'], objeto['nome'], objeto['curso'], objeto['data'], objeto['certidao'], objeto['declaracao'], objeto['diploma'], objeto['historico'], objeto['outros'], objeto['urgencia'], objeto['observacoes'], ) cursor.execute(insert) banco.commit() return True except Exception, e: warning = QMessageBox.warning( None, "Database error", u"Erro de inserção no banco de dados. O erro foi:\n\n%s" % e, QMessageBox.Ok) return ~warning
def __init__(self, dbpath): """ コンストラクタ。 @param string dbpath SQLiteのDBファイルのパス """ self.__con = sqlite.connect(dbpath) self.__cur = self.__con.cursor()
def setUp(self): try: import sqlite from sqlite import DatabaseError self.database_available = True except ImportError: print """Module sqlite not available. Please install it if you want to use it. Use the following command (on Ubuntu systems): sudo apt-get install python-sqlite """ self.database_available = False try: #the database file must exist and the databases must be created self.db = sqlite.connect(test_shared.SQLITE_DATABASE_LOCATION) cursor = self.db.cursor() cursor.execute("select count(*) from srmPeptides_test") #print cursor.fetchall() except DatabaseError: print print "=" * 75 print """The sqlite database is not available. Please run the sqlite_setupdb.py script first.""" print "=" * 75 self.database_available = False
def flush(self): try: db = sqlite.connect(self._file) cu = db.cursor() for item in self._queue: cmd = item['cmd'] args = escape(item['args']) table = escape(self._table) if "set" == cmd: cu.execute("UPDATE %s SET value='%s' WHERE key='%s'" % (table, args[1], args[0])) elif "put" == cmd: cu.execute( "INSERT INTO %s (key, value) VALUES ('%s', '%s')" % (table, args[0], args[1])) elif "remove" == cmd: cu.execute("DELETE FROM %s WHERE key='%s'" % (table, args[0])) else: self._logger.warn( "Invalid queue item in database object '%s'" % (self._name)) db.commit() self._logger.info("Wrote %d changes to the database '%s'" % (len(self._queue), self._name)) db.close() JibotDatabase.flush(self) except: self._logger.exception("Failed to write to the database '%s'" % (self._name))
def createDB(self): import sqlite, os dbfile = os.path.join(Econf.datadir(), '%s.db' %Econf.database()) try: os.remove(dbfile) except: pass db = sqlite.connect(dbfile) cursor = db.cursor() cursor.execute('CREATE TABLE facts ('\ 'id INTEGER PRIMARY KEY,'\ 'author VARCHAR(100) NOT NULL,'\ 'name VARCHAR(20) NOT NULL,'\ 'added DATETIME,'\ 'value VARCHAR(200) NOT NULL,'\ 'popularity INTEGER NOT NULL DEFAULT 0);') cursor.execute('CREATE TABLE log ('\ 'id INTEGER PRIMARY KEY,'\ 'author VARCHAR(100) NOT NULL,'\ 'name VARCHAR(20) NOT NULL,'\ 'added DATETIME,'\ 'oldvalue VARCHAR(200) NOT NULL);') db.commit() cursor.close() db.close() self.getCallback().databases = {}
def __init__(self, filename): import sqlite self.con = sqlite.connect(filename) self.cur = self.con.cursor() # create hits table if it does not exist self.cur.execute("SELECT name FROM sqlite_master WHERE type = 'table'") tables = self.cur.fetchall() if ("hits",) not in tables: self.cur.execute(""" CREATE TABLE hits ( query TEXT, subject TEXT, percid NUMBER, alignlen INT, mismatches INT, gaps INT, qstart INT, qend INT, sstart INT, send INT, evalue NUMBER, bitscore NUMBER ) """)
def SQLiteTable(path, tablename, encoding=None): """Returns a SimpleDatabaseWrapper which wraps the SQLite database found at the given path.""" import sqlite wrapper = SimpleDatabaseWrapper(sqlite.connect(path), tablename, encoding) atexit.register(close_on_exit, wrapper) return wrapper
class Db(object): """ this class implements a database connection. it connects to the database on initialisation. """ def __init__(self, dbname=None, dbhost=None, dbuser=None, dbpasswd=None, dbtype=None, ddir=None, doconnect=True): self.datadir = ddir or getdatadir() self.datadir = self.datadir + os.sep + "db" + os.sep if hasattr(os, 'mkdir'): if not os.path.isdir(self.datadir): try: os.mkdir(self.datadir) except OSError: pass cfg = getmainconfig() self.dbname = dbname or cfg.dbname if not self.dbname: raise Exception("no db name") self.dbhost = dbhost or cfg.dbhost or "" self.dbuser = dbuser or cfg.dbuser or "" self.dbpasswd = dbpasswd or cfg.dbpasswd or "" self.connection = None self.timeout = 15 self.dbtype = dbtype or cfg.dbtype or 'sqlite' self.error = "" if doconnect: self.connect() def connect(self, timeout=15): """ connect to the database. """ self.timeout = timeout logging.debug("connecting to %s (%s)" % (self.dbname, self.dbtype)) if self.dbtype == 'mysql': try: import MySQLdb except ImportError, ex: logging.error(str(ex)) self.error = str(ex) return 0 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 sqlite3 self.connection = sqlite3.connect(self.datadir + os.sep + self.dbname, check_same_thread=False) except ImportError: import sqlite self.connection = sqlite.connect(self.datadir + os.sep + self.dbname)
def __init__(self, connectArgs): self.bindVariables = 0 if connectArgs.has_key('verbose') and connectArgs['verbose']: self.verbose = 1 del connectArgs['verbose'] else: self.verbose = 0 self.conn = sqlite.connect(**connectArgs)
def __init__(self, db): self._conn = sqlite.connect(db) self._curs = self._conn.cursor() self._columns = "" for column in DISPLAY_COLUMNS: self._columns = self._columns + column + ", " self._columns = self._columns[:-2] print('Connected to file')
def load (): try: connection = sqlite.connect('spamtable.db') cursor = connection.cursor() cursor.execute('CREATE TABLE notspamoccur (token STRING PRIMARY KEY, occurences FLOAT)') except sql_load_error as e: print ("error in loading") sys.exit(2)
def openDB(dbfile_path, lib, autocommit=0, busytimeout=5000.0): """ Open a SQLite database. @dbfile_path The path to store the database file. If dbfile_path=':memory:', create a db in memory. @lib Which wrapper for the SQLite API to use. lib=0: PySQLite; lib=1: APSW. See http://www.initd.org/tracker/pysqlite for more details @autocommit Set autocommit @busytimeout Set the maximum time, in milliseconds, that SQLite will wait if the database is locked. """ assert lib != None, 'lib cannot be None' thread_name = threading.currentThread().getName() if thread_name in SQLiteCacheDB.cursor_table: return SQLiteCacheDB.cursor_table[thread_name] if dbfile_path.lower() != ':memory:': db_dir, db_filename = os.path.split(dbfile_path) if not os.path.isdir(db_dir): os.makedirs(db_dir) #print >> sys.stderr, 'sqldb: connect db', lib, os.path.abspath(dbfile_path), busytimeout, threading.currentThread().getName(), len(SQLiteCacheDB.cursor_table) #print_stack() if autocommit: if lib == 0: con = sqlite.connect(dbfile_path, isolation_level=None, timeout=(busytimeout / 1000.0)) elif lib == 1: con = apsw.Connection(dbfile_path) else: if lib == 0: timeout = (busytimeout / 1000.0) #print "***** timeout", timeout con = sqlite.connect(dbfile_path, timeout=timeout) elif lib == 1: con = apsw.Connection(dbfile_path) if lib == 1: con.setbusytimeout(int(busytimeout)) #con.text_factory = sqlite.OptimizedUnicode # return str if it isn't unicode cur = con.cursor() SQLiteCacheDB.cursor_table[thread_name] = cur SQLiteCacheDB.commit_begined[thread_name] = False #print '**** openDB', thread_name, len(SQLiteCacheDB.cursor_table) return cur
def __init__(self, connectArgs): self.bindVariables = 0 if connectArgs.has_key('verbose') and connectArgs['verbose']: self.verbose=1 del connectArgs['verbose'] else: self.verbose=0 self.conn=sqlite.connect(**connectArgs)
def delete_func(del_date, del_desc, del_amt): '''Delete Function (L2)''' os.chdir(sqlite_path) # open connection to database try: cx = sqlite.connect(database) except sqlit.Error, errmsg: print "Can not open " + str(errmsg)
def connect(self): """The import statement is delayed so the library is loaded ONLY if this factory is really used.""" try: import sqlite3 return sqlite3.connect(self.db) except: import sqlite self.using_sqlite3 = False return sqlite.connect(self.db)
def load (): try: connection = sqlite.connect('spamtable.db') cursor = connection.cursor() cursor.execute('CREATE TABLE spamtable (token STRING PRIMARY KEY, spamprob FLOAT)') cursor.execute('CREATE TABLE emailcount (type STRING PRIMARY KEY, count DOUBLE DEFAULT 0)') except sql_load_error as e: print ("error in loading") sys.exit(2)
def dbGrab(): con = sqlite.connect("g.sqlite") cur = con.cursor() cur.execute("select h,dir from hash_dir") m = cur.fetchall() cur.close() con.commit() con.close() return m
def edit_func(new_date, new_desc, new_amt, old_date, old_desc, old_amt): '''Edit / Update function (L2)''' os.chdir(sqlite_path) # open connection to database try: cx = sqlite.connect(database) except sqlit.Error, errmsg: print "Can not open " + str(errmsg)