def init_database(self):
"""Init the sqlite database for theme management
If it doesn't exist it will be created
"""
self.db_base_path = os.path.join(os.environ['HOME'],'.geoapt')
self.dbname = os.path.join(self.db_base_path,"geoapt.db")
#print QCoreApplication.translate("GeoApt", "Opening sqlite3 database %s\n") % self.dbname
if not os.path.exists(self.dbname):
# create the storage directory
if not os.path.exists(self.db_base_path):
os.mkdir(self.db_base_path)
self.db = sqlite3.connect(self.dbname)
# create the database schema
ThemeDatabase.create_schema(self.db)
QMessageBox.information(self, QCoreApplication.translate("GeoApt", "Theme Database"),QCoreApplication.translate("GeoApt", "A new theme database has been created.\nThis happens the first time you run the application or\nif your theme database has been moved or deleted.\n\nYour theme database can be found at:\n") + self.dbname)
else:
self.db = sqlite3.connect(self.dbname)
# Check to see if we have a qgis.db (needed for displaying data or the qgis libs assert and crash)
qgis_db_path = str(QgsApplication.qgisUserDbFilePath())
qgis_db_base_path = os.path.dirname(qgis_db_path)
if not os.path.exists(qgis_db_path):
# create an empty database
if not os.path.exists(qgis_db_base_path):
os.mkdir(qgis_db_base_path)
qgis_db = sqlite3.connect(qgis_db_path)
QMessageBox.information(self, QCoreApplication.translate("GeoApt", "Spatial Reference Database"),QCoreApplication.translate("GeoApt", "A new spatial reference (SRS) database has been created.\nThis happens the first time you run the application if you don't have QGIS installed.\n\nThe SRS database can be found at:\n") + qgis_db_path)
def save(self):
if self.id == -1:
self.db_path = sqlite3.connect("maillist.db")
self.cursor = self.db_path.cursor()
self.cursor.execute("""CREATE TABLE IF NOT EXISTS Subscribers
(id INTEGER PRIMARY KEY autoincrement,
name, email)""")
subscribers_query = ("""INSERT INTO Subscribers(name, email)
VALUES(?, ?)""")
subscribers_data = [self.__name, self.__email]
self.cursor.execute(subscribers_query, subscribers_data)
id_query = ("""SELECT id FROM Subscribers
WHERE name = ?""")
id_data = [self.__name]
id_in_db = self.cursor.execute(id_query, id_data)
for line in id_in_db:
self.id = line[0]
self.db_path.commit()
self.db_path.close()
elif self.id != -1:
self.db_path = sqlite3.connect("maillist.db")
self.cursor = self.db_path.cursor()
subscribers_data = [self.__name, self.__email, self.id]
subscribers_query = ("""UPDATE Subscribers
SET name = ?, email = ? WHERE id = ?""")
self.cursor.execute(subscribers_query, subscribers_data)
self.db_path.commit()
self.db_path.close()
def __init__(self, primary, secondary):
print 'Primary DB: %s' % primary
print 'Secondary DB: %s' % secondary
self.primary = sqlite3.connect(primary)
self.secondary = sqlite3.connect(secondary)
self.primary_cursor = self.primary.cursor()
self.secondary_cursor = self.secondary.cursor()
def iniciar (self) : # definicion del metodo
menu = '1 : añadirDVD - 2 : editarDVD - 3 : buscarDVD - 4 : listaDVDs - 5 : borrarDVD - S : salir \n' # asigna cadena menu
if os.path.exists ('./Escritorio/baseDatos.sql') : # condicion , si existe el fichero en la ruta indicada devuelve True
baseDATOS_SQL = sqlite3.connect ('./Escritorio/baseDatos.sql') # abre el fichero creado con el modulo sqlite3 , si no existe lo crea
else : # si no existe ; False
baseDATOS_SQL = sqlite3.connect ('./Escritorio/baseDatos.sql') # abre el fichero creado con el modulo sqlite3 , si no existe lo crea
cursor = baseDATOS_SQL.cursor () # crea el objeto que ejecuta las llamadas SQL
cursor.execute ('CREATE TABLE dvds (titulo TEXT UNIQUE NOT NULL,director TEXT NOT NULL,año INTEGER NOT NULL,duracion INTEGER NOT NULL)') # ejecuta las llamadas SQL en la base de datos del objeto -crea la tabla dvds con las columnas ; titulo , director , año ,duracion-
baseDATOS_SQL.commit () # guarda los cambios en el fichero SQL del objeto
while True : # bucle while continuo
print (menu) # presenta el menu
entradaTeclado = input ('entre opcion >> ') # espera entrada teclado
print ('\n') # salto de linea
if entradaTeclado == '1' : # condicion , si se cumple
self.añadirDVD (baseDATOS_SQL) # llama al metodo con el argumento indicado -objeto sqlite3-
elif entradaTeclado == '2' : # condicion , si se cumple
self.editarDVD (baseDATOS_SQL) # llama al metodo con el argumento indicado -objeto sqlite3-
elif entradaTeclado == '3' : # condicion , si se cumple
self.buscarDVD (baseDATOS_SQL) # llama al metodo con el argumento indicado -objeto sqlite3-
elif entradaTeclado == '4' : # condicion , si se cumple
self.listaDVDs (baseDATOS_SQL) # llama al metodo con el argumento indicado -objeto sqlite3-
elif entradaTeclado == '5' : # condicion , si se cumple
self.borrarDVD (baseDATOS_SQL) # llama al metodo con el argumento indicado -objeto sqlite3-
elif entradaTeclado == 'S' or entradaTeclado == 's' : # condicion , si se cumple
baseDATOS_SQL.close () # cierra el fichero de la base de datos
sys.exit (1) # sale del script
else : # ninguna de las opciones indicadas
continue # vuelve al inicio del bucle
def _getDb(self, channel):
filename = plugins.makeChannelFilename(self.filename, channel)
if filename in self.dbs:
return self.dbs[filename]
if os.path.exists(filename):
db = sqlite3.connect(filename)
db.text_factory = str
self.dbs[filename] = db
return db
db = sqlite3.connect(filename)
db.text_factory = str
self.dbs[filename] = db
cursor = db.cursor()
cursor.execute("""CREATE TABLE karma (
id INTEGER PRIMARY KEY,
name TEXT,
normalized TEXT UNIQUE ON CONFLICT IGNORE,
added INTEGER,
subtracted INTEGER
)""")
db.commit()
def p(s1, s2):
return int(ircutils.nickEqual(s1, s2))
db.create_function('nickeq', 2, p)
return db
def _bootstrap(self, index_file):
if not os.path.exists(index_file): # check whether the index exists
logging.info("No index was found at " + str(index_file) +
", so now creating the index.")
print("Please wait as the index is created, "
"this can take quite some time! – " + time.strftime('%X %x'))
db = sqlite3.connect(index_file)
cursor = db.cursor()
# limit memory usage to 64MB
cursor.execute("PRAGMA CACHE_SIZE = -65536")
# create a contentless virtual table using full-text search (FTS4)
# and the porter tokeniser
cursor.execute("CREATE VIRTUAL TABLE papers "
"USING fts4(content='', title, tokenize=porter);")
# get an iterator to access all the articles
articles = iter(self._zim_file)
for url, title, idx in articles: # retrieve articles one by one
cursor.execute(
"INSERT INTO papers(docid, title) VALUES (?, ?)",
(idx, title)) # and add them
# once all articles are added, commit the changes to the database
db.commit()
print("Index created, continuing – " + time.strftime('%X %x'))
db.close()
# return an open connection to the SQLite database
return sqlite3.connect(index_file)
def alias_register(alias_desired):
reg_string = "alias=" + alias_desired
if full_ledger == 1:
conn = sqlite3.connect('static/ledger.db')
else:
conn = sqlite3.connect('static/hyper.db')
conn.text_factory = str
c = conn.cursor()
m.execute("SELECT timestamp FROM transactions WHERE openfield = ?;", (reg_string,))
registered_pending = m.fetchone()
c.execute("SELECT timestamp FROM transactions WHERE openfield = ?;", (reg_string,))
registered_already = c.fetchone()
if registered_already == None and registered_pending == None:
alias_cb_var.set(0)
send_confirm("0", address, "1", reg_string)
else:
top9 = Toplevel()
top9.title("Name already registered")
registered_label = Label(top9, text="Name already registered")
registered_label.grid(row=0, column=0, sticky=N + W, padx=15, pady=(5, 0))
dismiss = Button(top9, text="Dismiss", command=top9.destroy)
dismiss.grid(row=3, column=0, sticky=W + E, padx=15, pady=(5, 5))
def qcsv(infilenames,file_db, keep_db, sql_cmd, sample_interval):
"""Query the listed CSV files, optionally writing the output to a sqlite file on disk."""
#
# Create a sqlite file, if specified
if file_db:
try:
os.unlink(file_db)
except:
pass
conn = sqlite3.connect(file_db)
else:
conn = sqlite3.connect(":memory:")
#
# Move data from input CSV files into sqlite
for csvfile in infilenames:
inhead, intail = os.path.split(csvfile)
tablename = os.path.splitext(intail)[0]
csv_to_sqldb(conn, csvfile, tablename)
#
# Execute the SQL
val=qsqldb( conn, sql_cmd, sample_interval)
#
# Clean up.
conn.close()
if file_db and not keep_db:
try:
os.unlink(file_db)
except:
pass
return val
def init_db(self):
db = os.path.join(self.local_path, "db")
if os.access(db, os.R_OK):
self.conn = sqlite3.connect(db)
self.c = self.conn.cursor()
self.c.execute(
"""
select root_dir, backup_dir, freq, start_time
from backup_unit"""
)
for row in self.c:
task = Task(row[0], row[1], row[2], row[3])
self.task_list.append([task] + list(row))
else:
self.conn = sqlite3.connect(db)
self.c = self.conn.cursor()
self.c.executescript(
"""
create table backup_unit(
root_dir varchar, backup_dir varchar, freq varchar, start_time varchar);
create table snapshot_unit(
root_dir varchar, snapshot varchar, cur_time varchar);
create table log(
user varchar, message varchar, time varchar);
create table dir_state(
root_dir varchar, cur_state varchar);
"""
)
def login(self,username=None,password=None):
ha=lambda u,p: hashlib.sha384(('U "%s" --> P "%s"'%(u,p)).encode()).hexdigest()
if 'username' in cherrypy.session:
raise cherrypy.HTTPRedirect('/')
if not username:
return lookup('login.html').render()
elif not password:
db=sqlite3.connect(const.DBFILE)
cur=db.cursor()
cur.execute('select exists(select * from users where username=?)',[username])
return 'login' if cur.fetchone()[0] else 'register'
else:
db=sqlite3.connect(const.DBFILE)
cur=db.cursor()
cur.execute('select password from users where username=?',[username])
result=cur.fetchone()
if result: #login
if ha(username,password)==result[0]:
cherrypy.session['username']=username
raise cherrypy.HTTPRedirect('/')
else:
return err('密码不正确')
else: #signup
cur.execute('insert into users (id,username,password,nick) values '\
'(null,?,?,?)',[username,ha(username,password),username])
db.commit()
cherrypy.session['username']=username
raise cherrypy.HTTPRedirect('/')
def patent_hunter(option=0, patinfo=""):
print 'option : ', option
print 'patent info : ', patinfo
assert option!=0 and patinfo!="" # option must not be zero
writer = csv.writer(open('./result.csv','w'))
psyinfo = '06304546'
# Citation sqlite3
if option == 'patnum' or option == '1':
opensql = sqlite3.connect("/home/minyoon/citation.sqlite3")
# Inventor sqlite3
elif option == 'inventor' or option == '2':
opensql = sqlite3.connect("/home/minyoon/full_disambiguation.sqlite3")
if opensql!=None:
cur = opensql.cursor()
if option == 'patent' or option == '1':
# list of patent that this patent is referencing
#cur.execute("SELECT Citation FROM citation WHERE Patent=? LIMIT 50", (patinfo,))
# list of patent that references this patent
cur.execute("SELECT Patent FROM citation WHERE Citation=?", (patinfo,))
elif option == 'inventor' or option == '2':
cur.execute("SELECT Patent FROM invpat WHERE Lastname = ? Limit 50", (patinfo,))
for entry in cur.fetchall():
print entry
return 1
def __init__(self, width=-1):
self.h=HistoryEngine()
self.tw=textwrap.TextWrapper()
self.tw.break_on_hyphens=False
self.width=width
if width!=-1: self.tw.width=width
self.prefix=self.guess_prefix()
self.db=os.path.join(self.prefix,'data.db')
self.cn=sqlite3.connect(self.db, check_same_thread = False)
self.c=self.cn.cursor()
self.langs=map(lambda a: a[0],self.c.execute(SQL_GET_LANGS).fetchall())
self.lang_boundary={}
for l in self.langs:
i=self.c.execute(SQL_GET_LANG_START, (l,)).fetchone()[0]
f=self.c.execute(SQL_GET_LANG_END, (l,)).fetchone()[0]
self.lang_boundary[l]=(i,f)
self.cn.row_factory=sqlite3.Row
self.c=self.cn.cursor()
self.fallback_lang='ar'
self.set_lang()
self.cities_db=os.path.join(self.prefix,'cities.db')
self.cities_cn=sqlite3.connect(self.cities_db)
self.cities_cn.row_factory=sqlite3.Row
self.cities_c=self.cities_cn.cursor()
r=self.cities_c.execute('select v from params where k=?', ('ver',)).fetchone()
print dict(r)
if r: self.cities_db_ver=r['v']
else: self.cities_db_ver='0'
def mainpage(imagename=None):
if request.method == 'GET':
if imagename:
imgname=(imagename,)
con = sqlite3.connect('Image.db')
cur = con.cursor()
cur.execute("SELECT * FROM Image WHERE imgname=?", imgname)
rows = cur.fetchall()
if rows:
for row in rows:
imgname = row[0]
imgdata = row[1]
return render_template('paint.html', saved=imgdata)
else:
resp = Response("""<html> <script> alert("Image not found");document.location.href="/" </script> </html>""")
return resp
else:
return render_template('paint.html')
if request.method == 'POST':
imgname=request.form['imagename']
imgdata=request.form['string']
data=(imgname, imgdata)
con = sqlite3.connect("Image.db")
cur=con.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS Image(imgname text, imgdata string)")
cur.execute("INSERT INTO Image VALUES(?, ?)", data)
con.commit()
con.close()
resp = Response("saved")
return resp
def __init__(self, db_file):
try:
import sqlite3
except ImportError:
raise ImportError, "You need sqlite3 to use this storage backend"
import os.path
if not os.path.exists(db_file):
self.conn = sqlite3.connect(db_file)
self.conn.text_factory = str
cursor = self.conn.cursor()
cursor.execute(
"""
CREATE TABLE occurrences(
class TEXT,
feature TEXT,
occurrences INTEGER,
PRIMARY KEY(feature, class)
)
"""
)
self.conn.commit()
cursor.close()
else:
self.conn = sqlite3.connect(db_file)
self.conn.text_factory = str
def initDB(): #データベースにファイルを作る if not os.path.exists(u"data.db"): con = sqlite3.connect(u"data.db") con.execute(u""" create table Picture( path_orig varchar(128), path_link varchar(128), path_thumb1 varchar(128), path_thumb2 varchar(128), flag integer, last_update real, tags varchar(128), dir varchar(128), name varchar(128), primary key (path_orig) ); """) con.execute(u""" create table Dir( path_orig varchar(128), flag integer, last_update varchar(128), date varchar(128), name varchar(128), fileNum integer, primary key (path_orig) ); """) else: con = sqlite3.connect(u"data.db") con.close()
def main(argv=sys.argv[1:]):
"""
This is the lantorrent daemon program. it mines the db for transfers
that it can group together and send. Only one should be running at
one time
"""
pylantorrent.log(logging.INFO, "enter %s" % (sys.argv[0]))
# use sqlaclh to make sure the db is there
x = LantorrentDB("sqlite:///%s" % pylantorrent.config.dbfile)
x.close()
con_str = pylantorrent.config.dbfile
#con = sqlite3.connect(con_str, isolation_level="EXCLUSIVE")
con = sqlite3.connect(con_str, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
done = False
while not done:
try:
rows = getrows(con)
if rows and len(rows) > 0:
do_it_live(con, rows)
else:
time.sleep(5)
except Exception, ex:
pylantorrent.log(logging.ERROR, "top level error %s" % (str(ex)), traceback)
con = sqlite3.connect(con_str, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
def makeDb(self, filename):
if os.path.exists(filename):
db = sqlite3.connect(filename)
if sys.version_info[0] < 3:
db.text_factory = str
return db
db = sqlite3.connect(filename)
if sys.version_info[0] < 3:
db.text_factory = str
cursor = db.cursor()
cursor.execute("""CREATE TABLE keys (
id INTEGER PRIMARY KEY,
key TEXT UNIQUE ON CONFLICT REPLACE
)""")
cursor.execute("""CREATE TABLE factoids (
id INTEGER PRIMARY KEY,
added_by TEXT,
added_at TIMESTAMP,
fact TEXT UNIQUE ON CONFLICT REPLACE,
locked BOOLEAN
)""")
cursor.execute("""CREATE TABLE relations (
id INTEGER PRIMARY KEY,
key_id INTEGER,
fact_id INTEGER,
usage_count INTEGER
)""")
db.commit()
return db
def _getDb(self, channel):
filename = plugins.makeChannelFilename(self.filename, channel)
def p(s1, s2):
# text_factory seems to only apply as an output adapter,
# so doesn't apply to created functions; so we use str()
return ircutils.nickEqual(str(s1), str(s2))
if filename in self.dbs:
return self.dbs[filename]
if os.path.exists(filename):
db = sqlite3.connect(filename)
db.text_factory = str
db.create_function('nickeq', 2, p)
self.dbs[filename] = db
return db
db = sqlite3.connect(filename)
db.text_factory = str
db.create_function('nickeq', 2, p)
self.dbs[filename] = db
cursor = db.cursor()
cursor.execute("""CREATE TABLE quotegrabs (
id INTEGER PRIMARY KEY,
nick BLOB,
hostmask TEXT,
added_by TEXT,
added_at TIMESTAMP,
quote TEXT
);""")
db.commit()
return db
def add_fic_links_to_linkdb(self):
dbpath = self.FilePath
select_fic_links = "SELECT FicID, FFNetID, Url, Updated, Published, Words, Chapters, 'www.fanfiction.net' AS Archive, '" + dbpath + "' AS Fandom_DB_Path FROM FanFic;"
fic_list = []
select_link_list_by_id = 'SELECT * FROM LinkList WHERE FFNetID = ? AND Fandom_DB_Path = ?'
select_fic = self._select_fic_by_ffnet_id
dbpath_tupal = (dbpath,)
insert_linklist = "INSERT INTO LinkList(FicID, FanFicArchiveId, Url, Updated, Published, Words, Chapters, Archive, Fandom_DB_Path) VALUES (?,?,?,?,?,?,?,?,?);"
con = sqlite3.connect(dbpath)
cur = con.cursor()
linkdb_path = self._FFnetArchiveLinkDB_Path
cur.execute(select_fic_links)
link_rows = cur.fetchall()
linkdb = sqlite3.connect(linkdb_path)
link_cur = linkdb.cursor()
icnt = 0
prrocessed = 0
print('total fics in db: ' + str(len(link_rows)))
link_cur.executemany(insert_linklist, link_rows)
linkdb.commit()
linkdb.close()
self.delete_dup_ficlinks()
return True
def _initialize_session_cache(self):
# Note -- fallback to returning None on any problems as this isn't
# critical. It just makes it so that we don't have to ask the user
# for their password over and over.
if not os.path.isdir(b_SESSION_DIR):
try:
os.makedirs(b_SESSION_DIR, mode=0o755)
except OSError as err:
log.warning('Unable to create {file}: {error}').format(
file=b_SESSION_DIR, error=err)
return None
if not os.path.exists(b_SESSION_FILE):
dbcon = sqlite3.connect(b_SESSION_FILE)
cursor = dbcon.cursor()
try:
cursor.execute('create table sessions (username text,'
' base_url text, session_id text,'
' primary key (username, base_url))')
except sqlite3.DatabaseError as err:
# Probably not a database
log.warning('Unable to initialize {file}: {error}'.format(
file=b_SESSION_FILE, error=err))
return None
dbcon.commit()
else:
try:
dbcon = sqlite3.connect(b_SESSION_FILE)
except sqlite3.OperationalError as err:
# Likely permission denied
log.warning('Unable to connect to {file}: {error}'.format(
file=b_SESSION_FILE, error=err))
return None
return dbcon
def run(warcs_dir, scratch_dir, db_dir, job):
conn = sqlite3.connect(os.path.join(db_dir, 'index.db'))
cursor = conn.cursor()
inserts = defaultdict(list)
for root, dirs, files in os.walk(warcs_dir):
for f in [f for f in files if f.endswith('.warc')]:
abs_path = os.path.join(root, f)
for headers, content, _ in WARC(abs_path):
older = _find_older(headers, cursor, job)
for pname, patcher in PATCHERS.iteritems():
for sname, strategy in STRATEGIES.iteritems():
n = '%s@%s' % (pname, sname)
nc = os.path.join(scratch_dir, n, 'no_compression')
rel_path = abs_path.replace(warcs_dir, '', 1)
rel_path = rel_path.lstrip('/')
p = os.path.join(nc, rel_path)
w = WARC(p)
if len(older) > 0:
d_headers, d_content = strategy(
cursor, headers, content,
older, pname, patcher)
w.add_record(d_headers, d_content)
inserts[n].append((
d_headers['WARC-Record-ID'], len(d_content)))
else:
w.add_record(headers, content)
conn.close()
for n, i in inserts.iteritems():
conn = sqlite3.connect(os.path.join(db_dir, '%s.db' % n))
cursor = conn.cursor()
cursor.executescript(SIZE_SCHEMA)
cursor.executemany(INSERT_RECORD_SIZE, i)
conn.commit()
conn.close()
return NAMES
def generate_sqlite(self):
print "Converting csv file to sqlite for train set:"
num_lines = sum(1 for line in open(self.folder+self.file_in))
columns = ['TRIP_ID', 'CALL_TYPE', 'ORIGIN_CALL', 'ORIGIN_STAND', 'TAXI_ID',
'TIMESTAMP', 'DAYTYPE', 'MISSING_DATA', 'POLYLINE']
con = sqlite3.connect(self.folder+self.file_out)
chunk_size = 5000
count = 1
for i in range(0, num_lines, chunk_size):
df = pd.read_csv(self.folder+self.file_in, header=None,
nrows=chunk_size, skiprows=i, low_memory=False)
df.columns = columns
sql.to_sql(df, name='train_data', con=con, index=False,
index_label='molecule_id', if_exists='append')
print "Batch No. {} completed".format(count)
count += 1
con.close()
# Delete the first row with duplicate column names
con = sqlite3.connect(self.folder+self.file_out)
c = con.cursor()
c.execute("DELETE FROM train_data WHERE TRIP_ID='TRIP_ID'")
con.commit()
con.close()
print "All completed!\n"
def test_new_geopackage(self):
assert os.path.exists(self.gpkg_file)
with sqlite3.connect(self.gpkg_file) as db:
cur = db.execute('''SELECT name FROM sqlite_master WHERE type='table' AND name=?''',
(self.table_name,))
content = cur.fetchone()
assert content[0] == self.table_name
with sqlite3.connect(self.gpkg_file) as db:
cur = db.execute('''SELECT table_name, data_type FROM gpkg_contents WHERE table_name = ?''',
(self.table_name,))
content = cur.fetchone()
assert content[0] == self.table_name
assert content[1] == 'tiles'
with sqlite3.connect(self.gpkg_file) as db:
cur = db.execute('''SELECT table_name FROM gpkg_tile_matrix WHERE table_name = ?''',
(self.table_name,))
content = cur.fetchall()
assert len(content) == 20
with sqlite3.connect(self.gpkg_file) as db:
cur = db.execute('''SELECT table_name FROM gpkg_tile_matrix_set WHERE table_name = ?''',
(self.table_name,))
content = cur.fetchone()
assert content[0] == self.table_name
def __init__(self,string):
if not ( os.path.isfile("{0}/{1}.db".format(self.LOCAL,string)) ):
self.conn = sqlite3.connect(string + ".db")
self.c = self.conn.cursor()
self.c.execute(''' CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT, name text, cpf text, data_nascimento date)''')
self.conn.commit()
self.dbname = string
elif os.path.isfile( "{0}/{1}.db".format(self.LOCAL,string) ):
self.conn = sqlite3.connect(string + ".db")
self.c = self.conn.cursor()
self.dbname = string
elif os.path.isfile( "{0}/appdb.db".format(self.LOCAL) ):
self.conn = sqlite3.connect("appdb.db")
self.c = self.conn.cursor()
self.dbname = "appdb"
else:
self.conn = sqlite3.connect("appdb.db")
self.c = self.conn.cursor()
self.c.execute(''' CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT, name text, cpf text, data_nascimento date)''')
conn.commit()
self.dbname = "appdb"
def open(self):
if os.path.exists(self.filename):
db = sqlite3.connect(self.filename, check_same_thread = False)
db.text_factory = str
self.db = db
return
db = sqlite3.connect(self.filename, check_same_thread = False)
db.text_factory = str
self.db = db
cursor = self.db.cursor()
cursor.execute("""CREATE TABLE users (
id INTEGER PRIMARY KEY,
total_rating INTEGER,
created_at INTEGER,
pos_rating_recv_count INTEGER,
neg_rating_recv_count INTEGER,
pos_rating_sent_count INTEGER,
neg_rating_sent_count INTEGER,
nick TEXT UNIQUE ON CONFLICT REPLACE,
host TEXT)
""")
cursor.execute("""CREATE TABLE ratings (
id INTEGER PRIMARY KEY,
rated_user_id INTEGER,
rater_user_id INTEGER,
created_at INTEGER,
rating INTEGER,
notes TEXT)
""")
self._commit()
return
def searching(self,entry,model):
lista_hist[:] = []
lista_histfull[:] = []
connb = sqlite3.connect(path + 'turbo.db')
a = connb.cursor()
a.execute("SELECT id,nome,conteudo,data,tipo,ip FROM history where conteudo like '%" + entry.get_text() + "%' order by id desc")
rows = a.fetchall()
for history in rows:
if history[4] == "S":
lista_hist.append([str(history[0]),history[1],cap(history[2],200),history[3],ico_send,history[5]])
else:
lista_hist.append([str(history[0]),history[1],cap(history[2],200),history[3],ico_receive,history[5]])
connc = sqlite3.connect(path + 'turbo.db')
c = connc.cursor()
c.execute("SELECT id,nome,conteudo,data,tipo,ip FROM history where conteudo like '%" + entry.get_text() + "%' order by id desc")
rowsc = c.fetchall()
for history in rowsc:
if history[4] == "S":
lista_histfull.append([str(history[0]),history[1],history[2].encode("utf-8"),history[3],ico_send,history[5]])
else:
lista_histfull.append([str(history[0]),history[1],history[2].encode("utf-8"),history[3],ico_receive,history[5]])
connb.close()
if len(model) != 0:
for i in range(len(model)):
iter = model.get_iter(0)
model.remove(iter)
for i in range(len(lista_hist)):
model.append(lista_hist[i])
def __init__(self, path):
conn = sqlite3.connect(path, check_same_thread=False)
if os.path.exists(path) and os.path.isfile(path):
self.conn = conn
else:
Log.error('Create sqlite3 file fail. use memory!')
self.conn = sqlite3.connect(':memory:')
def regendb2():
conn = sqlite3.connect('list2.db')
c = conn.cursor()
c.execute("select * from statics")
if os.path.exists('list3.db')==False:
conn2 = sqlite3.connect('list3.db')
c2 = conn2.cursor()
c2.execute("CREATE TABLE statics (id,name,sex,class,major,college,collegeid)")
else:
conn2 = sqlite3.connect('list3.db')
c2 = conn2.cursor()
for result in c.fetchall():
studentid=result[0]
studentname=result[1]
studentsex=result[2]
studentclass=result[3]
studentmajor=result[4]
studentcollege=result[5]
collegeid=result[6]
if studentsex=="男".decode("utf8"):
ensex=0
elif studentsex=="女".decode("utf8"):
ensex=1
c2.execute("insert into statics values ('"+studentid+"','"+studentname+"','"+str(ensex)+"','"+studentclass+"','"+studentmajor+"','"+studentcollege+"','"+str(collegeid)+"')")
conn2.commit()
c2.close()
conn2.close()
conn.commit()
c.close()
conn.close()
def add_cb(self, button,column,treesortable):
nome = self.nometxt.get_text()
connb = sqlite3.connect(path + 'turbo.db')
a = connb.cursor()
a.execute("SELECT nome FROM contacts WHERE upper(nome) = upper('" + nome + "')")
contactsName = a.fetchone()
connb.close()
if not contactsName:
if nome != "nome":
try:
IP = socket.gethostbyname(nome)
connc = sqlite3.connect(path + 'turbo.db')
c = connc.cursor()
c.execute("INSERT INTO contacts (nome,ip) VALUES (?,?)",(nome.upper(),IP))
connc.commit()
connc.close()
command = "mkdir " + path_attached + nome.upper()
os.system(command)
self.listmodel.append([nome.upper(),IP])
treesortable.set_sort_column_id(1, Gtk.SortType.ASCENDING)
except socket.gaierror, err:
msgerror = "I can't ping this host name!\\nPlease chech the host name and try again!";
command = "notify-send --hint=int:transient:1 \"TurboNote Gnome3\" \"" + (msgerror).decode('iso-8859-1').encode('utf8') + "\" -i " + path_icon + "turbo.png"
os.system(command)
else:
msgerror = "No data Found!\\nPlease i need contact name and ip adress for save contact!";
command = "notify-send --hint=int:transient:1 \"TurboNote Gnome3\" \"" + (msgerror).decode('iso-8859-1').encode('utf8') + "\" -i " + path_icon + "turbo.png"
os.system(command)
def connection(path): """Return the connection and the cursor for doing some stuff in the db path -String- absolute PATH of your db """ db = path #open 'database' return sqlite3.connect(db), sqlite3.connect(db).cursor() #connect to current database
from flask import Flask
from flask import render_template
from flask import request
from flask import redirect
from flask import url_for
from flask import flash
from flask import session
from utl import dbFunctions
import sqlite3
import os
DB_FILE="accounts.db"
db = sqlite3.connect(DB_FILE) #open if file exists, otherwise create
c = db.cursor()
#c = sqlite3.connect('accounts.db', check_same_thread=False).cursor()
c.execute('''CREATE TABLE IF NOT EXISTS USERNAMES(
[userID] INTEGER PRIMARY KEY, [username] text NOT NULL, [password] text);''')
c.execute('''CREATE TABLE IF NOT EXISTS STORIES
([storyID] INTEGER PRIMARY KEY, [title] text NOT NULL);''')
c.execute('''CREATE TABLE IF NOT EXISTS STORYEDITS
([storyID] INTEGER, [userID] INTEGER, [text] text NOT NULL);''')
app = Flask(__name__)
app.secret_key = os.urandom(32)
reason = ""
@app.route("/")
def root():
#######################################################
#CIS 41B Final Project
#File Name: builCarMpg.py
#Author: Tianqi Yang
#Time: 6/17/2019
#Description: fetch cars' mpg for different types car
#######################################################
import requests
from bs4 import BeautifulSoup
import sqlite3
import time
LINK = "https://www.cars.com/articles/best-and-worst-gas-mileage-2018-1420698621218/"
page = requests.get(LINK)
soup = BeautifulSoup(page.content, "lxml")
carsMpg = [ i.get_text() for elem in soup.find_all( 'tr', class_=['row1','row2']) for i in elem.find_all('td') ]
conn = sqlite3.connect('data.db')
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS CarMpg")
cur.execute('''CREATE TABLE CarMpg(
carName TEXT,
mpg INTEGER)''')
for k in range(1, len(carsMpg), 3):
cur.execute('''INSERT INTO CarMpg VALUES (?, ?)''', (carsMpg[k], int(carsMpg[k+1])))
conn.commit()
print('done')
import sqlite3
conn = sqlite3.connect('rpg_db.sqlite3')
curs = conn.cursor()
print('\nQ1: How many total Characters are there?')
query = "SELECT COUNT(*) FROM charactercreator_character;"
total_chars = curs.execute(query).fetchall()[0][0]
print(total_chars)
print('\nQ2: How many of each specific subclass?')
subclasses = ['mage', 'thief', 'cleric', 'fighter']
for subclass in subclasses:
query = "SELECT COUNT(character_ptr_id) FROM charactercreator_" + subclass + ";"
sub_count = curs.execute(query).fetchall()[0][0]
print(subclass + ': ' + str(sub_count))
print('\nQ3: How many total Items?')
query = "SELECT COUNT(item_id) FROM armory_item;"
total_items = curs.execute(query).fetchall()[0][0]
print(total_items)
print('\nQ4: How many of the Items are weapons? How many are not?')
query = "SELECT COUNT(*) FROM armory_weapon;"
num_weapons = curs.execute(query).fetchall()[0][0]
print('Number of items that are weapons: ' + str(num_weapons))
print('Number of items that are not weapons: ' + str(total_items - num_weapons))
print('\nQ5: How many Items does each character have? (Return first 20 rows)')
query = "SELECT character_id, COUNT(*) as num_items FROM charactercreator_character_inventory GROUP BY character_id LIMIT 20;"
first20_char_items = curs.execute(query).fetchall()
import sqlite3
# データベースに接続
conn = sqlite3.connect("ejdict.sqlite3")
c = conn.cursor()
# SQLでデータを取り出して表示
sql = 'SELECT * FROM sqlite_master'
rows = c.execute(sql)
for n in rows:
print(n[4])
url = 'https://slack.com/api/emoji.list'
# 認証用のパラメータ
param = {"token": enviroment.SLACK_LEGACY_TOKEN}
# おまじない
headers = {"Content-Type": "application/x-www-form-urlencoded"}
# 絵文字リストを取得
file = requests.get(url, params=param, headers=headers)
# 受け取ったjsonは実はstring型に変換されているのでjsonに変換
json_dict = json.loads(file.text)
# slackにつなぐ
slack = slackweb.Slack(url=enviroment.WEBHOOK_URL)
# データベースにつなぐ
dbname = "custom_emojis.db"
conn = sqlite3.connect(dbname)
c = conn.cursor()
# データベースを作ってなければ生成
c.execute('''CREATE TABLE IF NOT EXISTS custom_emojis (pos text)''')
for emoji_name in json_dict["emoji"]:
# すでにデータベースに入っているか検索
c.execute("SELECT * FROM custom_emojis WHERE pos=?", (emoji_name, ))
if c.fetchone() == None:
# 入っていない時は挿入
insert_sql = "INSERT INTO custom_emojis (pos) VALUES (?)"
insert_data = (emoji_name, )
c.execute(insert_sql, insert_data)
# 新しく入った絵文字をslackで通知
#create db tables and load csv files
import csv
import codecs
import sqlite3
import pprint
table_list = ['nodes', 'nodes_tags', 'ways', 'ways_tags', 'ways_nodes']
con = sqlite3.connect("safety_harbor.db")
cur = con.cursor()
#drop tables if they exists so we do not insert repeat data
for tablename in table_list:
stmt = "DROP TABLE IF EXISTS " + tablename
cur.execute(stmt)
con.commit()
# create nodes table
cur.execute("CREATE TABLE IF NOT EXISTS nodes (id, lat, lon, user, uid, version, changeset, timestamp);")
# load table
with codecs.open('nodes.csv', encoding='utf-8-sig') as fin:
dr = csv.DictReader(fin)
pprint.pprint(dr.fieldnames)
to_db = [(i['id'], i['lat'], i['lon'], i['user'], i['uid'], i['version'], i['changeset'], i['timestamp']) for i in dr]
cur.executemany("INSERT INTO nodes (id, lat, lon, user, uid, version, changeset, timestamp) \
VALUES (?, ?, ?, ?, ?, ?, ?, ?);", to_db)
# create nodes_tags table
import sqlite3 # nécessite
DB_LOCATION = './database.sqlite'
# CREATE_TABLE = """DROP TABLE IF EXISTS person ;
# CREATE TABLE person (id integer PRIMARY KEY AUTOINCREMENT, first_name varchar, last_name varchar);"""
CREATE_TABLE = """ CREATE TABLE IF NOT EXISTS person (id integer PRIMARY KEY AUTOINCREMENT, first_name varchar, last_name varchar);"""
INSERT = """INSERT INTO person (id , first_name , last_name )
values
(1, 'jean','dupont'),
(2, 'marie','lafraise')
;"""
SELECT = """SELECT * FROM person;"""
# ---------------------- exemple 1 d'appel bdd -------------------
# connexion bdd
connect = sqlite3.connect(DB_LOCATION)
try:
cursor = connect.cursor()
try:
cursor.execute(CREATE_TABLE)
cursor.execute(INSERT)
connect.commit()
except:
connect.rollback()
cursor.execute(SELECT)
connect.commit()
finally:
connect.close()
# ------------- équivalent avec ContextManager sur méthode ---------------
def __init__(self):
self.database_path = os.path.join(os.path.dirname('__file__'), 'spa_data.db')
self.connection = sqlite3.connect(self.database_path)
self.cursor = self.connection.cursor()
devs = os.listdir('devices')
""" testing
maxFields = -1
maxDevice = ''
for dev in devs:
if dev.endswith('.yml'):
f = deviceNumFields('devices/' + dev)
if maxFields < f:
maxFields = f
maxDevice = dev
print('max fields =' + str(maxFields))
print('device :' + maxDevice)
"""
db = sqlite3.connect('devices.sqlite')
cursor = db.cursor()
cursor.execute('drop table if exists devices;')
"""
TODO iterate all devices to find all the field names
"""
cursor.execute('''
create table devices(
architecture text,
battery_removable text,
battery_capacity text,
battery_tech text,
bt_spec text,
bt_profiles text,
cam0flash text,
import sqlite3
import csv
db = sqlite3.connect("database.db")
c = db.cursor()
# make tables
c.execute("DROP TABLE IF EXISTS students")
c.execute("DROP TABLE IF EXISTS courses")
c.execute(
"CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY KEY, name INTEGER, age STRING)"
)
c.execute(
"CREATE TABLE IF NOT EXISTS courses (id INTEGER PRIMARY KEY, code STRING, mark INTEGER, other_id INTEGER)"
)
with open("courses.csv") as file:
next(file)
reader = csv.reader(file)
for line in reader:
cmd = "INSERT INTO courses (code, mark, other_id) VALUES (\"" + line[
0] + "\"," + line[1] + "," + line[2] + ")"
c.execute(cmd)
with open("students.csv") as file:
next(file)
reader = csv.reader(file)
for line in reader:
cmd = "INSERT INTO students (name, age, id) VALUES (\"" + line[
0] + "\"," + line[1] + "," + line[2] + ")"
c.execute(cmd)
from pyicloud import PyiCloudService
from two_way_auth import get_gps_from_iphone
import sqlite3
from cal_rho import get_distance
from firebase import firebase
api = PyiCloudService('*****@*****.**', 'Bakabaka0208')
# 緯度経度をリストで保存
gps = get_gps_from_iphone(api)
conn = sqlite3.connect('gps_log.sqlite')
cur = conn.cursor()
path = './gps_log.sqlite3'
cur.execute('create table if not exists log(latitude INTEGER , longitude INTEGER )')
# cur.execute('delete from log')
cur.execute(f'INSERT INTO log VALUES (?, ?)', (gps[0], gps[1]))
cur.execute("select * from log")
cur.execute('select count(*) from log')
results = cur.fetchall()[0][0]
cur.execute('select * from log')
gps_list = [(float(i[0]), float(i[1])) for i in list(cur)]
import sqlite3
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute(
'create table user (id varchar(20) primary key, name varchar(20))')
print(cursor.rowcount)
cursor.close()
conn.commit()
conn.close()
from flask import Flask, render_template
import sqlite3, subprocess
con = sqlite3.connect('obdm_images.sqlite3', check_same_thread=False)
cur = con.cursor()
def create_table():
obdm_sql = """
CREATE TABLE obdm_img (
hyperlink text PRIMARY KEY,
name text)"""
cur.execute(obdm_sql)
#create SQL table if doesn't exist
cur.execute(
''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='obdm_img' '''
)
if cur.fetchone()[0] == 1:
print('Table exists.')
else:
create_table()
app = Flask(__name__)
@app.route('/')
def index():
cur.execute('SELECT * FROM obdm_img')
rows = cur.fetchall()
import sqlite3
conn =sqlite3.connect('login.db')
c=conn.cursor()
def read_db(logName,logPassword):
if logName=='' and logPassword=='':
print('plese enter the details')
else:
line=("SELECT * FROM logindb WHERE Name= " + "'" + logName + "' ")
c.execute(line)
data =c.fetchall()
for num in data:
if num[0]==logName :
if num[1]==logPassword:
print("Your login ")
else:
print("check your Password is worng")
if data == []:
print("check your Name worng")
print("No data enter on data base")
def check_password(self, id_, password):
with sqlite3.connect(self._db) as connection:
cursor = connection.cursor()
real_password = cursor.execute("""SELECT password FROM users
WHERE id=?""", (id_,)).fetchone()[0]
return password == real_password
def __init__(self):
self._conn = sqlite3.connect(':memory:')
with open('corona_radio/storage/initialization.sql', 'r') as fd:
self._conn.executescript(fd.read()).close()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os, sys
import sqlite3
query='algues vertes AND sangliers'
query='bipolaire'
query='algues vertes AND sangliers'
query='risk assessment'
#query='biofuel'
crawler=con=sqlite3.connect('output/'+query+'_crawl.db')
cur=con.execute("select urlid,domain from urlcorpus ")
res=cur.fetchall()
print res
pages = {}
for result in res:
#pages[result[0]]=result[1].replace(',','_')[10:70]
#pages[result[0]]='/'.join(result[1].replace('http://','').split('/')[:1]).replace("www.",'')
pages[result[0]]=result[1]
print pages
def unique(list):
list_clean = []
for item in list:
if not item in list_clean:
list_clean.append(item)
return list_clean
print (len(pages)), ' web pages '
print (len(unique(pages.values()))), ' unique sites '
cur=con.execute("select fromid,toid from link ")
import datetime, sqlite3, pytz
db = sqlite3.connect("accounts.sqlite", detect_types=sqlite3.PARSE_DECLTYPES)
db.execute(
"CREATE TABLE IF NOT EXISTS accounts (name TEXT PRIMARY KEY NOT NULL, balance INTEGER NOT NULL)"
)
db.execute(
"CREATE TABLE IF NOT EXISTS history (time TIMESTAMP NOT NULL, account TEXT NOT NULL,"
" amount INTEGER NOT NULL, PRIMARY KEY (time, account))")
db.execute(
"CREATE VIEW IF NOT EXISTS localhistory AS"
" SELECT strftime('%Y-%m-%d %H:%M:%f', history.time, 'localtime') AS localtime,"
" history.account, history.amount FROM history ORDER BY history.time")
class Account(object):
@staticmethod
def _current_time():
#return pytz.utc.localize(datetime.datetime.utcnow())
local_time = pytz.utc.localize(datetime.datetime.utcnow())
return local_time.astimezone()
def __init__(self, name: str, opening_balance: int = 10000):
cursor = db.execute(
"SELECT name, balance FROM accounts WHERE (name = ?)", (name, ))
row = cursor.fetchone()
if row:
self.name, self._bal = row
print("Retrieved Recorded for {}".format(self.name), end='')
else:
import tkinter
from tkinter import *
from tkinter import filedialog as fdialog
import shutil
from tkinter import messagebox
import sqlite3
import os
cnxn = sqlite3.connect(r"C:\Users\manda\AppData\Tkinter\student_db.db")
mycursor = cnxn.cursor()
class MainClass:
def __init__(self, master):
self.parent = master
self.gui()
def gui(self):
self.Source = StringVar()
self.Source1= StringVar()
self.Destination = "C:\\Users\\manda\\AppData\\Tkinter\\UploadFile" # only change this location to any folder on your desktop
self.y_index = 600
header_file_frame = LabelFrame(studfileroot, text="Welcome to the University of Bridgeport")\
.pack(fill="both", expand=False)
header_file_content = Label(header_file_frame, text="Course Files", font=('arial 40 bold'))\
.pack(fill="both", expand=False)
def delete_user(self, id_):
with sqlite3.connect(self._db) as connection:
cursor = connection.cursor()
cursor.execute("""DELETE FROM users WHERE id = ?""", (id_,))
connection.commit()
import sqlite3
import os
path = (os.getenv('APPDATA')
) + r"\discord\Local Storage\https_discordapp.com_0.localstorage"
data = sqlite3.connect(path)
selectdata = data.cursor()
read = selectdata.execute('SELECT value FROM ItemTable WHERE key="token"')
token = read.fetchone()[0].decode('utf-16le')
data.commit()
data.close()
print("Your Discord token: " + token + "\n\nPress Enter to exit.")
input()
def __init__(self, database):
self.conn = sqlite3.connect(database)
self.cur = self.conn.cursor()
self.cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text,"
"year integer, isbn integer)")
self.conn.commit()
import pickle
from pyvis.network import Network
import sqlite3
db = sqlite3.connect('users.db')
sql = db.cursor()
def visualization():
G = Network(height='1000px',
width='1000px',
# directed=True,
heading='Social graph of friends',
)
createNodesAndEdges(G)
G.show("G.html")
def createNodesAndEdges(G):
for value in sql.execute("SELECT * FROM users").fetchall():
size_ = 20
if value[4]!= '': size_ = len(pickle.loads(value[4])) + 20
if size_ > 100: size_ = 100
color_ = "blue"
if value[0] == 225273973:
color_ = "red"
G.add_node(value[0],
shape = "circularImage",
label = str(value[2]),
# Write your code here
import random
import sqlite3
# potential issue in the code: it does not take care of when randomly two card generations
# are the exact same. While the event is unlikely, it is still something that could happen
# todo: fix this issue in the next commit
conn = sqlite3.connect("card.s3db")
cur = conn.cursor()
cur.execute(
"create table if not exists card(id integer, number text, pin text, balance integer default 0)"
)
conn.commit()
def create_string(size, value):
str_val = str(value)
while len(str_val) != size:
str_val = "0" + str_val
return str_val
def generate_checksum(first_15_acc_number):
each_number = list(first_15_acc_number)
luhn_sum = 0
for i in range(len(each_number)):
num = int(each_number[i])
if (i + 1) % 2 == 1:
num = 2 * num
if num >= 10:
num = num - 9
import sqlite3
conn = sqlite3.connect('ema.db')
c = conn.cursor()
c.execute(
'''CREATE TABLE conversations (user1 text, user2 text, topic text, isEnded text, convo_id text)'''
)
conn.commit()
conn.close()
print 'Database Created'
import sqlite3
import os
conn = sqlite3.connect("Cosmos.db", isolation_level=None)
sql = """
CREATE TABLE Member(
id VARCHAR(4),
name VARCHAR(20),
age INTEGER,
email VARCHAR(128)
);
"""
conn.execute(sql)
sql = "INSERT INTO Member VALUES ('1018','Kenta',23,'*****@*****.**')"
conn.execute(sql)
sql = "INSERT INTO Member VALUES ('1027','Yamano',18,'*****@*****.**')"
conn.execute(sql)
sql = "INSERT INTO Member VALUES ('1135','Honda',28,'*****@*****.**')"
conn.execute(sql)
sql = "INSERT INTO Member VALUES ('1333','Tomita',32,'*****@*****.**')"
conn.execute(sql)
c = conn.cursor()
c.execute("SELECT * FROM Member")
for row in c:
print(row)
conn.close()
def dbcheck():
conn = sqlite3.connect(DB_FILE)
c = conn.cursor()
c.execute(
'CREATE TABLE IF NOT EXISTS artists (ArtistID TEXT UNIQUE, ArtistName TEXT, ArtistSortName TEXT, DateAdded TEXT, Status TEXT, IncludeExtras INTEGER, LatestAlbum TEXT, ReleaseDate TEXT, AlbumID TEXT, HaveTracks INTEGER, TotalTracks INTEGER)'
)
c.execute(
'CREATE TABLE IF NOT EXISTS albums (ArtistID TEXT, ArtistName TEXT, AlbumTitle TEXT, AlbumASIN TEXT, ReleaseDate TEXT, DateAdded TEXT, AlbumID TEXT UNIQUE, Status TEXT, Type TEXT)'
)
c.execute(
'CREATE TABLE IF NOT EXISTS tracks (ArtistID TEXT, ArtistName TEXT, AlbumTitle TEXT, AlbumASIN TEXT, AlbumID TEXT, TrackTitle TEXT, TrackDuration, TrackID TEXT, TrackNumber INTEGER, Location TEXT, BitRate INTEGER, CleanName TEXT)'
)
c.execute(
'CREATE TABLE IF NOT EXISTS snatched (AlbumID TEXT, Title TEXT, Size INTEGER, URL TEXT, DateAdded TEXT, Status TEXT, FolderName TEXT)'
)
c.execute(
'CREATE TABLE IF NOT EXISTS have (ArtistName TEXT, AlbumTitle TEXT, TrackNumber TEXT, TrackTitle TEXT, TrackLength TEXT, BitRate TEXT, Genre TEXT, Date TEXT, TrackID TEXT, Location TEXT, CleanName TEXT)'
)
c.execute(
'CREATE TABLE IF NOT EXISTS lastfmcloud (ArtistName TEXT, ArtistID TEXT, Count INTEGER)'
)
c.execute(
'CREATE TABLE IF NOT EXISTS descriptions (ReleaseGroupID TEXT, ReleaseID TEXT, Summary TEXT, Content TEXT)'
)
c.execute(
'CREATE TABLE IF NOT EXISTS releases (ReleaseID TEXT, ReleaseGroupID TEXT, UNIQUE(ReleaseID, ReleaseGroupID))'
)
c.execute(
'CREATE INDEX IF NOT EXISTS tracks_albumid ON tracks(AlbumID ASC)')
c.execute(
'CREATE INDEX IF NOT EXISTS album_artistid_reldate ON albums(ArtistID ASC, ReleaseDate DESC)'
)
try:
c.execute('SELECT IncludeExtras from artists')
except sqlite3.OperationalError:
c.execute(
'ALTER TABLE artists ADD COLUMN IncludeExtras INTEGER DEFAULT 0')
try:
c.execute('SELECT LatestAlbum from artists')
except sqlite3.OperationalError:
c.execute('ALTER TABLE artists ADD COLUMN LatestAlbum TEXT')
try:
c.execute('SELECT ReleaseDate from artists')
except sqlite3.OperationalError:
c.execute('ALTER TABLE artists ADD COLUMN ReleaseDate TEXT')
try:
c.execute('SELECT AlbumID from artists')
except sqlite3.OperationalError:
c.execute('ALTER TABLE artists ADD COLUMN AlbumID TEXT')
try:
c.execute('SELECT HaveTracks from artists')
except sqlite3.OperationalError:
c.execute(
'ALTER TABLE artists ADD COLUMN HaveTracks INTEGER DEFAULT 0')
try:
c.execute('SELECT TotalTracks from artists')
except sqlite3.OperationalError:
c.execute(
'ALTER TABLE artists ADD COLUMN TotalTracks INTEGER DEFAULT 0')
try:
c.execute('SELECT Type from albums')
except sqlite3.OperationalError:
c.execute('ALTER TABLE albums ADD COLUMN Type TEXT DEFAULT "Album"')
try:
c.execute('SELECT TrackNumber from tracks')
except sqlite3.OperationalError:
c.execute('ALTER TABLE tracks ADD COLUMN TrackNumber INTEGER')
try:
c.execute('SELECT FolderName from snatched')
except sqlite3.OperationalError:
c.execute('ALTER TABLE snatched ADD COLUMN FolderName TEXT')
try:
c.execute('SELECT Location from tracks')
except sqlite3.OperationalError:
c.execute('ALTER TABLE tracks ADD COLUMN Location TEXT')
try:
c.execute('SELECT Location from have')
except sqlite3.OperationalError:
c.execute('ALTER TABLE have ADD COLUMN Location TEXT')
try:
c.execute('SELECT BitRate from tracks')
except sqlite3.OperationalError:
c.execute('ALTER TABLE tracks ADD COLUMN BitRate INTEGER')
try:
c.execute('SELECT CleanName from tracks')
except sqlite3.OperationalError:
c.execute('ALTER TABLE tracks ADD COLUMN CleanName TEXT')
try:
c.execute('SELECT CleanName from have')
except sqlite3.OperationalError:
c.execute('ALTER TABLE have ADD COLUMN CleanName TEXT')
conn.commit()
c.close()
import sqlite3
#File to contain all calls to the database
db = sqlite3.connect('music-db')
cursor = db.cursor()
def startup():
db = sqlite3.connect('music-db')
cursor = db.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS songs(id INTEGER PRIMARY KEY, name TEXT)
''')
db.commit()
print('Connected to DB')
def insert(songname):
cursor.execute('''INSERT INTO songs(name) VALUES (?)''', (songname, ))
db.commit()
print('Inserted song: ' + songname)
def printall():
cursor.execute('''SELECT name FROM songs''')
all_rows = cursor.fetchall()
for row in all_rows:
print('{0} '.format(row[0]))
def get_connection():
"""Функция устанавливает соединение с базой данных"""
__connection = sqlite3.connect('database/education.db')
return __connection
def _create_conn(path: str) -> sqlite3.Connection:
c = sqlite3.connect(path)
c.row_factory = sqlite3.Row # allow dict-like access on rows with col name
return c