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 _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 _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 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 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 _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 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 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 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 __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 _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 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)