def get_tanks(filename):
tank = re.compile('src="pics/vehicle/contour/(?P<country>\w*).(?P<tank>[\w-]*).png"')
con = sqlite3.connect(filename)
con.isolation_level = None
cur = con.cursor()
query = 'select frags from fights'
sqlite3.complete_statement(query)
cur.execute(query)
tanks = {
'usa': set(),
'ussr': set(),
'germany': set(),
'france': set(),
# 'china': set(),
# 'uk': set()
}
for frag in cur.fetchall():
for country, name in tank.findall(frag[0]):
if country in tanks: # add only countries with expert
tanks[country].add(name)
con.close()
return tanks
def process_sql_statement(self,
sql_statement,
messageId,
use_write_db=False):
query_id = self.data_manager.get_query_id()
query_start = time.time()
logging.info("ZmqReceiver: (%d) SQL received: %s", query_id,
sql_statement.rstrip())
if not sqlite3.complete_statement(sql_statement):
# Try adding a semicolon at the end.
sql_statement = sql_statement + ";"
if not sqlite3.complete_statement(sql_statement):
return self.sql_complete_callback(
{
"retval": 1,
"error_message": "Incomplete sql statement"
}, query_id, query_start)
# else continue
# Enqueue
d = self.data_manager.async_validate_and_route_query(
sql_statement, query_id, use_write_db=use_write_db)
d.addCallback(self.sql_complete_callback, query_id, query_start,
messageId)
def loadTreeData():
conn = sqlite3.connect(sqlite_file)
cursor = conn.cursor()
qry = open('SQLite Database/createTable.sql', 'r').read()
sqlite3.complete_statement(qry)
try:
cursor.executescript(qry)
except Exception as e:
MessageBoxW = ctypes.windll.user32.MessageBoxW
errorMessage = sqlite_file + ': ' + str(e)
MessageBoxW(None, errorMessage, 'Error', 0)
raise
qry = open('SQLite Database/insertTreeData.sql', 'r').read()
sqlite3.complete_statement(qry)
try:
cursor.executescript(qry)
except Exception as e:
MessageBoxW = ctypes.windll.user32.MessageBoxW
errorMessage = sqlite_file + ': ' + str(e)
MessageBoxW(None, errorMessage, 'Error', 0)
raise
conn.commit()
cursor.close()
conn.close()
def merge_load():
database_name = 'database.db'
dump_name = 'dump.sql'
database_path = path.join('.', database_name)
dump_path = path.join('.', dump_name)
if not path.isfile(dump_path):
print "Can't merge a database from ASCII sql if there is no ASCII sql."
return
con = sqlite3.connect(database_path)
c = con.cursor()
raw = ''
with open(dump_path, 'rU') as dumpfile:
for line in dumpfile:
split = line.split()
if line.startswith('INSERT INTO') and split[2].find('scans') >= 0:
values = line.find('VALUES(') + len('VALUES(')
comma = line.find(',', values)
raw += line[:values] + 'NULL' + line[comma:]
if want_to_execute(raw) and sqlite3.complete_statement(raw):
c.execute(raw)
raw = ''
if want_to_execute(raw) and sqlite3.complete_statement(raw):
c.execute(raw)
try:
con.commit()
except sqlite3.OperationalError, e:
print 'Received error: %s' % e
print 'Ignored said error.'
def getRandomWord(relativePathToUser, number, startswith, endswith):
conn = None
result = None
if number <= 0 or number > 10:
raise Exception(
"No! You can only give me a number between 1 and 10 :)")
try:
dbFile = os.path.join(Path.home(), relativePathToUser)
conn = sqlite3.connect(dbFile)
cur = conn.cursor()
with open(templateLocation + "random_word.sql") as word:
query = word.read()
sqlite3.complete_statement(query)
cur.execute(query, (startswith + "%", "%" + endswith, number))
record = cur.fetchall()
result = record
except Error as e:
print(e)
finally:
if conn:
conn.close()
return result
def process_sql_statement_for_text(self, sql_statement, request):
query_id = self.data_manager.get_query_id()
query_start = time.time()
logging.info("HttpReceiver: (%d) SQL received: %s", query_id,
sql_statement.rstrip())
if not sqlite3.complete_statement(sql_statement):
# Try adding a semicolon at the end.
sql_statement = sql_statement + ";"
if not sqlite3.complete_statement(sql_statement):
request.write("Incomplete sql statement")
request.finish()
return
# else it is now a complete statement
d = self.data_manager.async_validate_and_route_query(
sql_statement, query_id)
d.addCallback(self.sql_complete_callback_for_text, query_id,
query_start, request)
def load():
database_name = 'database.db'
dump_name = 'dump.sql'
database_path = path.join('.', database_name)
dump_path = path.join('.', dump_name)
if path.isfile(database_path):
try:
os.remove(database_path)
except:
print traceback.format_exc(sys.exc_info()[2])
return
if not path.isfile(dump_path):
print "Can't load a database from ASCII sql if there is no ASCII sql."
return
con = sqlite3.connect(database_path)
c = con.cursor()
raw = ''
with open(dump_path, 'rU') as dumpfile:
for line in dumpfile:
raw += line
if want_to_execute(raw) and sqlite3.complete_statement(raw):
c.execute(raw)
raw = ''
if want_to_execute(raw) and sqlite3.complete_statement(raw):
c.execute(raw)
try:
con.commit()
except sqlite3.OperationalError, e:
print 'Received error: %s' % e
print 'Ignored said error.'
def create_tables(con, dbfile, sqlfile):
if os.path.isfile(dbfile):
os.remove(dbfile)
query = open(sqlfile, 'r').read()
sqlite3.complete_statement(query)
try:
con.executescript(query)
con.commit()
except Exception as e:
error_message = dbfile + ': ' + str(e)
print(error_message)
con.close()
def load_db(con,filename):
wb = pd.ExcelFile(filename+'.xlsx')
for sheet in wb.sheet_names:
df=pd.read_excel(filename+'.xlsx', sheet_name=sheet)
df.to_sql(sheet, con, index=False, if_exists="append")
con.commit()
if __name__ == '__main__':
database_file = "clinetdb"
sql_file = "create_tables.sql"
con = sqlite3.connect(database_file+'.db')
create_tables(con, database_file+'.db', sql_file)
load_db(con, database_file)
def process_sql_statement(self, sql_statement, request):
query_id = self.data_manager.get_query_id()
query_start = time.time()
logging.info("HttpReceiver: (%d) SQL received: %s", query_id,
sql_statement.rstrip())
if not sqlite3.complete_statement(sql_statement):
# Try adding a semicolon at the end.
sql_statement = sql_statement + ";"
if not sqlite3.complete_statement(sql_statement):
response_meta = {
"response_op": "error",
"error_message": "Incomplete sql statement",
"identity": util.Identity.get_identity()
}
request.write(json.dumps(response_meta))
request.finish()
return
# else it is now a complete statement
d = self.data_manager.async_validate_and_route_query(
sql_statement, query_id)
d.addCallback(self.sql_complete_callback, query_id, query_start,
request)
def instanciarEsquemaBBDD(self):
# Nombre de la base de datos
DB_NAME = "datosArticulos.db"
# Archivo SQL con la definicion de las tablas
SQL_File_Name = "CrearTablas.sql"
# Se carga el archivo SQL a una variable y se eliminan los saltos de linea
TableSchema = ""
with open(SQL_File_Name, 'r') as SchemaFile:
TableSchema = SchemaFile.read().replace('\n', '')
# Se crea la nueva base de datos
conn = sqlite3.connect(DB_NAME)
curs = conn.cursor()
# Se lanza la consulta de creacion de tablas
sqlite3.complete_statement(TableSchema)
curs.executescript(TableSchema)
# Se cierra la conexion con la base de datos
curs.close()
conn.close()
print("[GESTORBBDD] Base de datos creada con éxito.")
def init_call_history_DB():
table_init_sql = """drop table if exists Call_Details;
create table Call_Details (
S_No integer primary key autoincrement,
Phone_Number text,
Modem_Date text,
Modem_Time text,
System_Date_Time text
);"""
#Connect or Create DB File
conn = sqlite3.connect(DB_NAME)
curs = conn.cursor()
#Create Tables
sqlite3.complete_statement(table_init_sql)
curs.executescript(table_init_sql)
#Close DB
curs.close()
conn.close()
print "SQLite3 Database initialized successfully"
def process_sql_statement(self, sql_statement, request):
query_id = self.data_manager.get_query_id()
query_start = time.time()
logging.info("HttpReceiver: (%d) SQL received: %s", query_id, sql_statement.rstrip())
if not sqlite3.complete_statement(sql_statement):
# Try adding a semicolon at the end.
sql_statement = sql_statement + ";"
if not sqlite3.complete_statement(sql_statement):
response_meta = { "response_op" : "error",
"error_message" : "Incomplete sql statement",
"identity" : util.Identity.get_identity() }
request.write(json.dumps(response_meta))
request.finish()
return
# else it is now a complete statement
d = self.data_manager.async_validate_and_route_query(sql_statement, query_id)
d.addCallback(self.sql_complete_callback, query_id, query_start, request)
def db_init(app):
databaseFile = app.config['DATABASE_URL']
sqlFile = app.config['DATABASE_SCHEMA']
# Delete the old table
if os.path.isfile(databaseFile):
os.remove(databaseFile)
# Create the tables
qry = open(sqlFile, 'r').read()
sqlite3.complete_statement(qry)
conn = db_connect(app)
cursor = conn.cursor()
try:
cursor.executescript(qry)
conn.commit()
print ("DB initialize successfully")
except Exception as e:
MessageBoxW = ctypes.windll.user32.MessageBoxW
errorMessage = databaseFile + ': ' + str(e)
MessageBoxW(None, errorMessage, 'Error', 0)
cursor.close()
conn.rollback()
raise
finally:
db_close(conn)
def create_database(huc: str, db_path: str, metadata: Dict[str, str], epsg: int, schema_path: str, delete: bool = False):
"""[summary]
Args:
huc (str): [description]
db_path (str): [description]
metadata (Dict[str, str]): [description]
epsg (int): [description]
schema_path (str): [description]
delete (bool, optional): [description]. Defaults to False.
Raises:
Exception: [description]
Returns:
[type]: [description]
"""
# We need to create a projection for this DB
db_srs = osr.SpatialReference()
db_srs.ImportFromEPSG(int(epsg))
metadata['gdal_srs_proj4'] = db_srs.ExportToProj4()
metadata['gdal_srs_axis_mapping_strategy'] = osr.OAMS_TRADITIONAL_GIS_ORDER
if not os.path.isfile(schema_path):
raise Exception('Unable to find database schema file at {}'.format(schema_path))
log = Logger('Database')
if os.path.isfile(db_path) and delete is True:
log.info('Removing existing SQLite database at {0}'.format(db_path))
os.remove(db_path)
log.info('Creating database schema at {0}'.format(db_path))
qry = open(schema_path, 'r').read()
sqlite3.complete_statement(qry)
conn = sqlite3.connect(db_path)
conn.execute('PRAGMA foreign_keys = ON;')
curs = conn.cursor()
curs.executescript(qry)
load_lookup_data(db_path, os.path.dirname(schema_path))
# Keep only the designated watershed
curs.execute('DELETE FROM Watersheds WHERE WatershedID <> ?', [huc])
# Retrieve the name of the watershed so it can be stored in riverscapes project
curs.execute('SELECT Name FROM Watersheds WHERE WatershedID = ?', [huc])
row = curs.fetchone()
watershed_name = row[0] if row else None
conn.commit()
conn.execute("VACUUM")
# Write the metadata to the database
if metadata:
[store_metadata(db_path, key, value) for key, value in metadata.items()]
return watershed_name
def init_sqlite():
conn = sqlite3.connect(DB_NAME)
curs = conn.cursor()
sqlite3.complete_statement(TableSchema)
curs.executescript(TableSchema)
curs.close()
conn.close()
def create_tables(self):
path = os.path.join(self.config['base_dir'], self.config['schema_path'])
qry = open(path, 'r').read()
sqlite3.complete_statement(qry)
conn = self.__setup_connection()
cursor = conn.cursor()
try:
cursor.executescript(qry)
except Exception as e:
print e
def insert_mock_data(self):
path = os.path.join(self.config['base_dir'], self.config['mock_candidates_path'])
qry = open(path, 'r').read()
sqlite3.complete_statement(qry)
conn = self.__setup_connection()
cursor = conn.cursor()
try:
cursor.executescript(qry)
except Exception as e:
print e
def create_table(self, conn):
SQL_File_Name = 'main/create_table_sql.sql'
TableSchema = ""
with open(SQL_File_Name, 'r') as SchemaFile:
TableSchema = SchemaFile.read().replace('\n', '')
try:
c = conn.cursor()
sqlite3.complete_statement(TableSchema)
c.executescript(TableSchema)
except Error as e:
self.logger.error(e)
def __init__(self, filename):
# Connect or create Database
self.db = sqlite3.connect(filename)
self.cursor = self.db.cursor()
# Creating table
sqlite3.complete_statement(self.creating_table)
self.cursor.executescript(self.creating_table)
# Close database
self.cursor.close()
def setup(path):
databaseFile = (path + 'warehouses.db')
sqlFile = (path + 'init_warehouse.sql')
# Delete the old table
#if os.path.isfile(databaseFile):
# os.remove(databaseFile)
# Create the tables
qry = open(sqlFile, 'r').read()
sqlite3.complete_statement(qry)
conn = sqlite3.connect(databaseFile)
cursor = conn.cursor()
try:
cursor.executescript(qry)
except Exception as e:
print(e)
cursor.close()
raise
# warehouses
cursor.execute('insert into warehouse (id, site) values (123, "uppsala")')
cursor.execute('insert into warehouse (id, site) values (456, "hanoi")')
# zones
cursor.execute('insert into zone (no_aisles, rows_per_aisle, position, warehouse_id, robot_id) values (2, 3, 0, 123, NULL)')
cursor.execute('insert into zone (no_aisles, rows_per_aisle, position, warehouse_id, robot_id) values (1, 3, 0, 456, NULL)')
# shelves
chars = ['A', 'B', 'C', 'D']
for c in chars:
for i in range(0,6):
query = 'insert into shelf (name, warehouse_id, package_id, arduino_id) values ("%c%d", 123, NULL, NULL)' %(c, i)
cursor.execute(query)
chars = ['A', 'B']
for c in chars:
for i in range(0,6):
query = 'insert into shelf (name, warehouse_id, package_id, arduino_id) values ("%c%d", 456, NULL, NULL)' %(c, i)
cursor.execute(query)
# arduinos
cursor.execute('insert into arduino (id, warehouse_id) values (1337, 123)')
# sensors
cursor.execute('insert into sensor (type, reading, arduino_id) values ("temperature", NULL, 1337)')
cursor.execute('insert into sensor (type, reading, arduino_id) values ("humidity", NULL, 1337)')
cursor.execute('insert into sensor (type, reading, arduino_id) values ("light", NULL, 1337)')
#save changes
conn.commit()
return conn
def table_create(connection):
#iterate through files in ./sql directory
for filename in os.listdir("./sql/"):
if filename.endswith(".sql"):
our_sql = open(os.path.join("./sql/", filename), 'r').read()
sqlite3.complete_statement(our_sql)
cursor = connection.cursor()
try:
cursor.executescript(our_sql)
except Exception as e:
print(e)
cursor.close()
else:
continue
def connect_database(): if os.path.exists(databaseFile): print "Exists" # establish connection with the database and make one if it does not exist conn = sqlite3.connect(databaseFile) # making a Cursor Object for executing sqlite3 commands global_module.db_cursor = conn.cursor() else : print "Server Databse Created" sql = open(sqlFile,'r').read() sqlite3.complete_statement(sql); conn = sqlite3.connect(databaseFile) global_module.db_cursor = conn.cursor(); global_module.db_cursor.executescript(sql); print type(global_module.db_cursor);
def connect_database():
if os.path.exists(databaseFile):
print "Exists"
# establish connection with the database and make one if it does not exist
conn = sqlite3.connect(databaseFile)
# making a Cursor Object for executing sqlite3 commands
global_module.db_cursor = conn.cursor()
else:
print "Server Databse Created"
sql = open(sqlFile, 'r').read()
sqlite3.complete_statement(sql)
conn = sqlite3.connect(databaseFile)
global_module.db_cursor = conn.cursor()
global_module.db_cursor.executescript(sql)
print type(global_module.db_cursor)
def createSQLite():
create_sql = open(
os.path.join(os.path.dirname(__file__), '../schema/sqlite.sql'),
'r').read()
sqlite3.complete_statement(create_sql)
conn = sqlite3.connect('system.db')
cursor = conn.cursor()
try:
cursor.executescript(create_sql)
except Exception as e:
print(e)
conn.close()
def execute_sql_query_manipulation(self, sQuery, tpValues=None):
""" returns the id of the inserted dataset if id is autoincremented
except of giving:
sQuery = "INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)"
you can also give
sQuery = "INSERT INTO stocks VALUES (?,?,?,?,?)"
tpValues = ('2017-01-05','BUY','RHAT',100,35.14)
"""
if not self.con:
raise Exception(
"You have to establish a connection to a database before you can execute sql queries."
)
if not l3.complete_statement(sQuery):
raise Exception("The sql query %s is no valid sql statement" %
sQuery)
try:
if not tpValues:
self.cur.execute(sQuery)
else:
self.cur.execute(sQuery, tpValues)
self.con.commit()
return self.cur.lastrowid
except l3.Error as e:
print("An error occurred: ", e.args[0])
self.rollback_sql_query()
def sqlite_userInputCommand_during_run(sqlite_db):
# A minimal SQLite shell for experiments
import sqlite3
con = sqlite3.connect(sqlite_db) #was ":memory:"
con.isolation_level = None
cur = con.cursor()
buffer = ""
print "Enter your SQL commands to execute in sqlite3."
print "Enter a blank line to exit."
while True:
line = raw_input()
if line == "":
break
buffer += line
if sqlite3.complete_statement(buffer):
try:
buffer = buffer.strip()
cur.execute(buffer)
if buffer.lstrip().upper().startswith("SELECT"):
print cur.fetchall()
except sqlite3.Error as e:
print "An error occurred:", e.args[0]
buffer = ""
con.close()
def procesar_query(direccion, datos):
print(f'Recibido de: <{direccion[0]}:{direccion[1]}>')
sql = datos.decode().strip()
print('Query: {}'.format(sql))
if sqlite3.complete_statement(sql):
try:
sqlite_cursor.execute(sql)
resultado = sqlite_cursor.fetchall()
if resultado:
mensaje = json.dumps(resultado)
else:
mensaje = 'Esta consulta no regresó resultados.'
except sqlite3.Error as e:
mensaje = e.args[0]
print('Error: {}'.format(mensaje))
else:
print('OK')
else:
mensaje = 'La/s sentencias sql no están completas.'
print('Error: {}'.format(mensaje))
salida = mensaje.encode()
return len(salida).to_bytes(4, byteorder='big') + salida
def insert2tb_predeservmatch(): #20171226
global conn
global curs
predeservice=[['ssh','SSH','tcp','22','0'], \
['www','HTTP','tcp','80','0'], \
['ldap','LDAP','tcp','389','0'], \
['tacacs','TACACS','tcp','49','1'], \
['snmp','SNMP','udp','161','0'], \
['snmptrap','SNMP','udp','161','0'], \
['tftp','TFTP','udp','69','0'], \
['ntp','NTP','udp','123','0'], \
['syslog','SYSLOG','udp','514','0'], \
['netbios-ns','NBNAME','udp','137','0'], \
['netbios-dgm','NBDS','udp','138','0'], \
['netbios-ssn','SMB','tcp','139','0'], \
['https','HTTPS','tcp','443','0'], \
['radius','RADIUS','udp','1812','0'], \
['telnet','TELNET','tcp','23','0'], \
['sqlnet','SQLNETv2','tcp','1521','0'], \
['ftp-data','FTP','tcp','20','0'], \
['ftp','FTP','tcp','21','0']]
query = "insert into predeservmatch(origservname,hillservname,proto,dstport1,newadd) values(?,?,?,?,?);"
for tmplist in predeservice:
if sqlite3.complete_statement(query):
curs.execute(query, tmplist)
conn.commit()
def customSQLQuery(cursor):
buffer = ""
while True:
line = input("\nSQL mondat(kilépés q-ra): ")
if line == "q":
break
fname = datetime.datetime.now().strftime(
"%Y%m%d_%Hh%Mm%Ss") + "_result.txt"
buffer += line
if sqlite3.complete_statement(buffer):
try:
buffer = buffer.strip()
cursor.execute(buffer)
if buffer.lstrip().upper().startswith("SELECT"):
result = cursor.fetchall()
print(result)
with open(fname, "w", encoding="utf8") as f:
f.write("%s\n" % buffer)
for element in result:
lstToStr = " '".join(
[str(i) + "';" for i in element])
f.write("'%s\n" % lstToStr)
print("\nA lekérdezés eredménye kiírásra került a " +
fname + " fájlba!")
except sqlite3.Error as e:
print("\nHiba: ", e.args[0])
buffer = ""
def db_shell(conn):
cur = conn.cursor()
buffer = ""
print("Enter your SQL commands to execute in sqlite3.")
print("Enter a blank line to exit.")
while True:
line = input('SQL> ')
if line == "":
break
buffer += line
if sqlite3.complete_statement(buffer):
try:
buffer = buffer.strip()
cur.execute(buffer)
if buffer.lstrip().upper().startswith("SELECT"):
for row in cur.fetchall():
print(row)
except sqlite3.Error as e:
print("An error occurred:", e.args[0])
buffer = ""
def sqlShell():
# A minimal SQLite shell for experiments
con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()
buffer = ""
print("Enter your SQL commands to execute in sqlite3.")
print("Enter a blank line to exit.")
while True:
line = input()
if line == "":
break
buffer += line
if sqlite3.complete_statement(buffer):
try:
buffer = buffer.strip()
cur.execute(buffer)
if buffer.lstrip().upper().startswith("SELECT"):
print(cur.fetchall())
except sqlite3.Error as e:
print("An error occurred:", e.args[0])
buffer = ""
con.close()
def lineReceived(self, line):
"""
When a line comes in from the client appened it to the working sql statement.
If its a complete statement (semicolon terminated), execute it.
"""
# Exit on lines with just ctrl-d and/or ctrl-c
m = re.search(r"^\s*[" + chr(4) + chr(3) + "]+\s*$", line, flags=re.IGNORECASE)
if m != None:
self.sendLine("Bye!")
return 1
# Add this line to the multi-line sql statement.
self.sql_statement += line + "\n"
# Do we have a complete sql statement?
if sqlite3.complete_statement(self.sql_statement):
query_id = self.factory.data_manager.get_query_id()
query_start = time.time()
logging.info("SqlReceiver: (%d:%d) SQL received: %s", self.connection_id, query_id, self.sql_statement.rstrip())
# Enqueue
d = self.factory.data_manager.async_validate_and_route_query(self.sql_statement, query_id)
d.addCallback(self.sql_complete_callback, query_id, query_start)
def sql_execute(Cursor, Statement, List): print "Is SQL", sqlite3.complete_statement(Statement), Statement try: Result = Cursor.execute(Statement, List) return [Result] except sqlite3.Error as e : return [False, e]
def main(database=":memory:"):
conn = connect(None, database)
buf = ""
print "Enter your SQL commands to execute in sqlite3."
try:
while True:
try:
line = raw_input("sqlite3 >> ")
except (KeyboardInterrupt, EOFError):
print
quit()
buf += line + " "
if sqlite3.complete_statement(buf):
execute_sql(conn, buf)
buf = ""
else:
cmd, args = buf.split(" ", 1)
if cmd in COMMANDS:
conn = COMMANDS[cmd](conn, args.strip())
buf = ""
finally:
conn.close()
def default(self, line):
self.sql_statement = self.sql_statement + ' ' + line.rstrip('\n')
if sqlite3.complete_statement(self.sql_statement):
match_set = re.search(r"^\s*set\s+(\S+)\s*=\s*'?(\S+?)'?\s*;\s*$",
self.sql_statement,
flags=re.IGNORECASE)
if match_set:
name = match_set.group(1)
value = match_set.group(2)
self.set_set(name, value)
else:
m = re.search(r"^\s*select\s+",
self.sql_statement,
flags=re.IGNORECASE)
m2 = re.search(r"^\s*show\s+",
self.sql_statement,
flags=re.IGNORECASE)
m3 = re.search(r"^\s*desc\s+",
self.sql_statement,
flags=re.IGNORECASE)
if m or m2 or m3:
self.send_query(self.sql_statement, self.use_write_db)
else:
print "Unrecognized statement: ", self.sql_statement
self.reset_multiline()
else:
self.multiline()
def call_sqlite():
con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()
buffer = ""
print "Enter your SQL commands to execute in sqlite3."
print "Enter a blank line to exit."
while True:
line = raw_input()
if line == "":
break
buffer += line
if sqlite3.complete_statement(buffer):
try:
buffer = buffer.strip()
cur.execute(buffer)
if buffer.lstrip().upper().startswith("SELECT"):
print cur.fetchall()
except sqlite3.Error as e:
print "An error occurred:", e.args[0]
buffer = ""
con.close()
def request_db(self, buffer):
conn = sqlite3.connect(self.sqlite_base)
conn.isolation_level = None
c = conn.cursor()
#print(' Executing:', buffer)
if sqlite3.complete_statement(buffer):
try:
buffer = buffer.strip()
c.execute(buffer)
if buffer.lstrip().upper().startswith("SELECT"):
fetch_all = c.fetchall()
if len(fetch_all) > 0:
conn.close()
return fetch_all
else:
conn.close()
return [(-1, )]
else:
conn.close()
except sqlite3.Error as e:
print("An error occurred:", e.args[0])
else:
print('Buffer statement not complete!')
def session_create_database():
# Relative file addresses
dbdumpfile = "db_dump.sqlite.sql"
print "entro"
if os.path.exists(dbdumpfile) == False:
return
# Set write permissions on the database file
os.chmod(db_filename, 0o666)
# Read the dump file
in_file = open(dbdumpfile, "r")
sqldump = in_file.read()
if len(sqldump) < 1:
return
'''
sqlite3.complete_statement(sql) returns True if the string sql
contains
one or more complete SQL statements terminated by semicolons.
It does not verify that the SQL is syntactically correct, only that
there are
no unclosed string literals and the statement is terminated by a
semicolon.
This can be used to build a shell for SQLite.
'''
if sqlite3.complete_statement(sqldump):
conn = sqlite3.connect(db_filename)
cursor = conn.cursor()
cursor.executescript(sqldump)
conn.close()
print "db creato!"
return
def perform(self, query, obj=None):
#Create a factory to return dictionary
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
conn = self.get_db()
conn.row_factory = dict_factory
c = conn.cursor()
try:
if lite.complete_statement(query):
if obj:
c.execute(query, obj)
else:
c.execute(query)
result = c.fetchall()
#possibly condition commit to only insert/update/delete.
conn.commit()
except lite.Error, e:
result = '%s' % e.args[0]
print query
def execute(self, this):
if self.fileopened:
ibuf = self.mainTree.get_widget('ExecInput').get_buffer()
inpu = ibuf.get_text(ibuf.get_start_iter(), ibuf.get_end_iter())
ebuf = self.mainTree.get_widget('ExecError').get_buffer()
erro = ebuf.get_text(ebuf.get_start_iter(), ebuf.get_end_iter())
ef = self.mainTree.get_widget('ExecField')
if not inpu.rstrip().endswith(";"):
inpu = inpu+';'
ibuf.insert(ibuf.get_end_iter(), ';')
if sqlite3.complete_statement(inpu):
try:
ef.remove(self.exectv)
self.exectv = False
except:
pass
try:
result = self.sql(inpu, None, False)
if inpu.lstrip().upper().startswith("SELECT"):
ls = False
i = 0
for row in self.cursor:
l = len(row)
list = []
for field in row:
list.append(str(field))
if not ls:
columns = [str] * l
ls = gtk.ListStore(*columns)
ls.append(list)
i += 1
if i == 0:
ebuf.set_text(_('Empty result'))
else:
cols = [""]*l
cells = [""]*l
labels = [""]*l
j = 0
for k in self.cursor.description:
labels[j] = str(k[0])
j += 1
self.exectv = gtk.TreeView(ls)
for i in range(0,l):
cols[i] = gtk.TreeViewColumn(labels[i])
cells[i] = gtk.CellRendererText()
cols[i].pack_start(cells[i])
cols[i].add_attribute(cells[i], 'text', i)
cols[i].set_sizing(gtk.TREE_VIEW_COLUMN_FIXED)
cols[i].set_fixed_width(100)
cols[i].set_resizable(True)
self.exectv.append_column(cols[i])
self.exectv.show()
ef.add(self.exectv)
self.reloadstructure()
self.reloadbrowse()
except sqlite3.Error, e:
ebuf.set_text(_('[SQLite Error] ')+e.args[0])
else:
ebuf.set_text(_('[geekSQLite Error] Not a complete statement!'))
def execute_query (self, query) :
if sqlite3.complete_statement(query):
try:
self.connx.execute (query)
io.Printer.showinfo ("Successfully executed query")
except sqlite3.Error as e:
io.Printer.showerr("An sqlite3 error occurred:", e.args[0])
exit ()
def ingest_sqlite_dump(cursor, filename):
sql = ''
for line in open(filename, 'rt').readlines():
sql += line
if sqlite3.complete_statement(sql):
sql = sql.strip()
if sql != 'COMMIT;':
cursor.execute(sql)
sql = ''
def prepare_cursor(self, db_name, q, opts):
'''Returns executed cursor'''
conn = self.connections[db_name]
cur = conn.cursor()
if sqlite3.complete_statement(q):
q = q.strip()
cur.execute(q, opts)
else:
raise ValueError('""%s"" is not a valid SQL Statement' % q)
return cur
def doExecute(self, data):
self.doUpdate(">>> "+data)
if sqlite3.complete_statement(data):
try:
data = data.strip()
self.cur.execute(data)
if data.lstrip().upper().startswith("SELECT"):
self.doUpdate(self.cur.fetchall())
except sqlite3.Error as e:
self.doUpdate("An error occurred:"+str(e.args[0]))
def add(filename, author, *trackers):
justBuildTableIfNonexistence("FILES")
justBuildTableIfNonexistence("FILES_TRACKERS")
connection = sqlite3.connect("share.db")
connection.isolation_level = None
cursor = connection.cursor()
hashValue = hashlib.md5()
hashValue.update(filename)
hashValue.update(author)
hash = hashValue.hexdigest()
sql_statement = "INSERT INTO FILES(filename, hash, author, chunk_info) VALUES('"+filename+"','"+hash+"','"+author+"', '');"
print "Going to execute()\n\t"+sql_statement
if sqlite3.complete_statement(sql_statement):
try:
sql_statement = sql_statement.strip()
cursor.execute(sql_statement)
#Get file_id
sql_statement = "SELECT f.file_id FROM files f WHERE hash='"+hash+"';"
print "Going to execute()\n\t"+sql_statement
cursor.execute(sql_statement)
file_id = cursor.fetchone()[0]
print type(file_id)
for each_tracker in trackers:
for each in each_tracker:
sql_statement = "INSERT INTO FILES_TRACKERS(file_id, tracker) VALUES("+str(file_id)+",'"+each+"');"
print "Going to execute()\n\t"+sql_statement
if sqlite3.complete_statement(sql_statement):
try:
sql_statement = sql_statement.strip()
cursor.execute(sql_statement)
except sqlite3.Error as e:
print "An error occurred:", e.args[0]
return None
return file_id
except sqlite3.Error as e:
print "An error occurred:", e.args[0]
connection.close()
return None
connection.close()
return None
def create_new_database(self):
import os
import sqlite3
from config import db_dir, db_filepath, experiment_spectrums_path, schema
log = ""
# Create 'data' directory
if not os.path.exists(db_dir):
os.makedirs(db_dir)
log += "[Created Directory: " + db_dir + "]" + "\n"
# Create spectrum database file
conn = sqlite3.connect(db_filepath)
log += "[Created Database File: " + db_filepath + " ]" + "\n"
# Create 'experiments' directory
if not os.path.exists(experiment_spectrums_path):
os.makedirs(experiment_spectrums_path)
log += "[Created Directory: " + experiment_spectrums_path + "]" + "\n"
# Create Cursor
cursor = conn.cursor()
# Execute SQL Script 'build_tables'
script = open(schema, 'r').read()
sqlite3.complete_statement(script)
try:
cursor.executescript(script)
except Exception as e:
cursor.close()
print "Table Already exists!"
log += "[Built Tables]" + "\n"
# Close DB connection
conn.close()
log += "[INIT COMPLETE]" + "\n"
return None, None
def sql_execute(self,query,params):
if sqlite3.complete_statement(query):
try:
curs = self.db_connection.cursor()
curs.isolation_level = None #for autocommit mode
with curs:
curs.executemany(query,params)
except (sqlite3.Error,sqlite3.IntegrityError) as e:
print "I cant accomplish connection to {0} database. Error {1}".format(db_name, e.message)
#curs.rollback() #This method rolls back any changes to the database since the last call to commit().
else:
curs.commite()
curs.close()
def justBuildTableIfNonexistence(tablename):
connection = sqlite3.connect("share.db")
connection.isolation_level = None
cursor = connection.cursor()
tablename = tablename.strip().upper()
sql_statement = "SELECT name FROM sqlite_master WHERE type='table' AND name='"+tablename+"';"
print "Going to test if the table '"+tablename+"' exists?"
if sqlite3.complete_statement(sql_statement):
try:
sql_statement = sql_statement.strip()
cursor.execute(sql_statement)
if sql_statement.lstrip().upper().startswith("SELECT"):
if len(cursor.fetchall()) == 0:
print tablename+" does not exist, so now going to create one."
if tablename == "FILES":
sql_statement = "CREATE TABLE "+tablename+"( file_id INTEGER PRIMARY KEY, filename TEXT, hash TEXT, author TEXT, chunk_info TEXT);"
elif tablename == "FILES_TRACKERS":
sql_statement = "CREATE TABLE "+tablename+"( files_trackers_id INTEGER PRIMARY KEY, file_id INTEGER, tracker TEXT, FOREIGN KEY(file_id) REFERENCES files(file_id));"
else:
sql_statement = "INVALID"
if sql_statement != "INVALID":
print "Going to execute"
print "\t"+sql_statement
if sqlite3.complete_statement(sql_statement):
try:
sql_statement = sql_statement.strip()
connection.execute(sql_statement)
except sqlite3.Error as e:
print "An error occurred:", e.args[0]
else:
print "Table '"+tablename+"' has not been defined.\n"
connection.close()
return True
else:
return False
except sqlite3.Error as e:
print "An error occurred:", e.args[0]
connection.close()
def process_sql_statement_for_text(self, sql_statement, request):
query_id = self.data_manager.get_query_id()
query_start = time.time()
logging.info("HttpReceiver: (%d) SQL received: %s", query_id, sql_statement.rstrip())
if not sqlite3.complete_statement(sql_statement):
# Try adding a semicolon at the end.
sql_statement = sql_statement + ";"
if not sqlite3.complete_statement(sql_statement):
request.write("Incomplete sql statement")
request.finish()
return
# else it is now a complete statement
d = self.data_manager.async_validate_and_route_query(sql_statement, query_id)
d.addCallback(self.sql_complete_callback_for_text, query_id, query_start, request)
def incomplete(self, statement):
"""Concatenate clauses until a complete statement is made."""
self.statement += statement
if self.statement.count(';') > 1:
print ('An error has occurerd: ' +
'You may only execute one statement at a time.')
print('For the statement: %s' % self.statement)
self.statement = ''
if sqlite3.complete_statement(self.statement):
#the statement is not incomplete, it's complete
return False
else:
#the statement is incomplete
return True
def process_sql_statement(self, sql_statement, messageId, use_write_db=False):
query_id = self.data_manager.get_query_id()
query_start = time.time()
logging.info("ZmqReceiver: (%d) SQL received: %s", query_id, sql_statement.rstrip())
if not sqlite3.complete_statement(sql_statement):
# Try adding a semicolon at the end.
sql_statement = sql_statement + ";"
if not sqlite3.complete_statement(sql_statement):
return self.sql_complete_callback( { "retval" : 1,
"error_message" : "Incomplete sql statement" },
query_id, query_start )
# else continue
# Enqueue
d = self.data_manager.async_validate_and_route_query(sql_statement, query_id, use_write_db=use_write_db)
d.addCallback(self.sql_complete_callback, query_id, query_start, messageId)
def executemany_sql(conn, query, params):
if conn and sqlite3.complete_statement(query):
conn.row_factory = sqlite3.Row
try:
with conn as c:
curs = c.cursor()
curs.executemany(query, params)
c.commit()
except (sqlite3.IntegrityError,):
print("IntegrityError")
else:
print("fault")
return None
def executemany_sql(conn, query,params):
if conn and sqlite3.complete_statement(query):
#conn.row_factory = sqlite3.Row
conn.row_factory = str
try:
with conn as c:
curs = c.cursor()
curs.executemany(query,params)
c.commit()
except sqlite3.IntegrityError:
print "some basa"
else: print "bad"
return None
def prepare_cursor(self, db_name, query, options=None):
'''Returns executed cursor'''
conn = self.connections[db_name]
cur = conn.cursor()
if sqlite3.complete_statement(query):
try:
query = query.strip()
if options==None:
cur.execute(query)
else:
cur.execute(query, options)
except sqlite3.Error as e:
print 'Bad news: ', str(e)
else:
raise ValueError('""%s"" is not a valid SQL Statement' % query)
return cur
def do_select(self,query):
if not query:
print error('Must specify a query statement, refer standard '+bcolors.BOLD+'SQL SELECT')
return
if query.strip().startswith('**'):
query = 'select '+ query+';'
self.showObject(query)
return
query = 'select '+ query+';'
if sqlite3.complete_statement(query):
try:
query = query.strip()
self.cur.execute(query)
TablePrinter(self.cur.fetchall())
except Exception as e:
print error('Query Error: ' + e.args[0])
def default(self, arg):
''' Run the command to SQLite
'''
# make sure we have a whole query
query = arg.strip()
if sqlite3.complete_statement(query):
try:
self.cur.execute(query)
# display results
results = pp(self.cur)
if results:
print results
except sqlite3.Error, e:
# SQLite returned an error
print 'An error occurred:', e.args[0]
except:
def createSQLite(csvFile, dbFile, tableName):
with open(csvFile, 'r') as f:
dialect = csv.Sniffer().sniff(f.read(1024))
f.seek(0)
csvReader = csv.DictReader(f, delimiter = dialect.delimiter)
# create sqlite db
conn = sqlite3.connect(dbFile)
c = conn.cursor()
# Create a table creation query
# Get the first row, and base the data types for the columns on this row
row = csvReader.next()
keys = row.keys()
fielddefs = []
for key in keys:
if key == None:
raise Exception('The number of column names is not equal to the number of columns.')
if key == 'id':
raise Exception("""Please pick a different name for the "id" column.
It is used interally by this script to generate a unique
identifier for each row.""")
if row[key].isdigit():
coltype = 'INTEGER'
elif CsvToSqlite.is_float(row[key]):
coltype = 'REAL'
else:
coltype = 'TEXT'
fielddefs.append('"%s" %s' % (key, coltype))
q = 'CREATE TABLE IF NOT EXISTS "%s" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, %s)' % (tableName, ','.join(fielddefs))
if not sqlite3.complete_statement(q):
err = 'Query %s is not valid. This may mean you are using reserved words for the column or table names.' % q
raise Exception(err)
c.execute(q)
insertQuery = 'INSERT INTO %s (%s) VALUES (%s)' % (tableName, ','.join(keys), ','.join([':' + x for x in keys]));
c.execute(insertQuery, row)
for row in csvReader:
c.execute(insertQuery, row)
print('\tWriting %s' % dbFile)
conn.commit()
def execute_sql(conn, query, params):
res = []
if conn and sqlite3.complete_statement(query):
conn.row_factory = sqlite3.Row
try:
with conn as c:
curs = c.cursor()
curs.execute(query, params)
res.extend(curs.fetchall())
c.commit()
except (sqlite3.IntegrityError,):
print("IntegrityError")
else:
print("fault")
return res
def execute_query(self, sql):
"""Execute a valid SQL query.
We first check to see if it is valid SQL, and if it isn't, we pass through a sqlite3.Error which is to be handled from the caller.
Upon successful validation, we execute the statement. If an error occurs, we pass the error back to the caller."""
if not sqlite3.complete_statement(sql.encode('utf_8')):
raise DatabaseError(u"The statement you provided isn't valid SQL.")
if "update" in sql or "drop" in sql or "insert" in sql:
raise DatabaseError(
u"Incorrect method; please use either\"execute_insert_statement\" or \"execute_update_statement()\" or \"execute_delete_statement()\" instead.")
with sqlite3.connect(self.db_path) as con:
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute(sql)
return cur.fetchall()