### CREDITS ##########################################################################################
# Copyright (c) 2007 Tom De Smedt.
# See LICENSE.txt for details.
__author__ = "Tom De Smedt"
__version__ = "1.9.2"
__copyright__ = "Copyright (c) 2007 Tom De Smedt"
__license__ = "MIT"
import sys, os
sys.path.append(os.path.dirname(__file__))
from .pysqlite2 import dbapi2 as sqlite
### DATABASE #########################################################################################
class Database:
def __init__(self):
self._name = None
self._tables = [] # All table names.
self._indices = []
self._commit = 0
self._i = 0
def connect(self, name):
"""Generic database.
Opens the SQLite database with the given name.
The .db extension is automatically appended to the name.
For each table in the database an attribute is created,
and assigned a Table object.
You can do: database.table or database[table].
"""
self._name = name.rstrip(".db")
self._con = sqlite.connect(self._name + ".db")
self._cur = self._con.cursor()
self._tables = []
self._cur.execute("select name from sqlite_master where type='table'")
for r in self._cur: self._tables.append(r[0])
self._indices = []
self._cur.execute("select name from sqlite_master where type='index'")
for r in self._cur: self._indices.append(r[0])
for t in self._tables:
self._cur.execute("pragma table_info("+t+")")
fields = []
key = ""
for r in self._cur:
fields.append(r[1])
if r[2] == "integer": key = r[1]
setattr(self, t, Table(self, t, key, fields))
def create(self, name, overwrite=True):
"""Creates an SQLite database file.
Creates an SQLite database with the given name.
The .box file extension is added automatically.
Overwrites any existing database by default.
"""
self._name = name.rstrip(".db")
from os import unlink
if overwrite:
try: unlink(self._name + ".db")
except: pass
self._con = sqlite.connect(self._name + ".db")
self._cur = self._con.cursor()
def __len__(self):
return len(self._tables)
def __getitem__(self, name):
if isinstance(name, int): name = self._tables[name]
return getattr(self, name)
# Deprecated, use for-loop and Database[table_name] instead.
def tables(self): return self._tables
table = __getitem__
def create_table(self, name, fields=[], key="id"):
"""Creates a new table.
Creates a table with the given name,
containing the list of given fields.
Since SQLite uses manifest typing, no data type need be supplied.
The primary key is "id" by default,
an integer that can be set or otherwise autoincrements.
"""
for f in fields:
if f == key: fields.remove(key)
sql = "create table "+name+" "
sql += "("+key+" integer primary key"
for f in fields: sql += ", "+f+" varchar(255)"
sql += ")"
self._cur.execute(sql)
self._con.commit()
self.index(name, key, unique=True)
self.connect(self._name)
# Deprecated, use create_table() instead.
append = create_table
def create_index(self, table, field, unique=False, ascending=True):
"""Creates a table index.
Creates an index on the given table,
on the given field with unique values enforced or not,
in ascending or descending order.
"""
if unique: u = "unique "
else: u = ""
if ascending: a = "asc"
else: a = "desc"
sql = "create "+u+"index index_"+table+"_"+field+" "
sql += "on "+table+"("+field+" "+a+")"
self._cur.execute(sql)
self._con.commit()
# Deprecated, use create_index() instead.
index = create_index
def commit(self, each=0):
"""Sets the commit frequency.
Modifications to the database,
e.g. row insertions are commited in batch,
specified by the given number.
A number that is reasonably high allows for faster transactions.
Commits anything still pending.
"""
self._commit = each
self._con.commit()
def close(self):
"""Commits any pending transactions and closes the database.
"""
self._con.commit()
self._cur.close()
self._con.close()
def sql(self, sql):
""" Executes a raw SQL statement on the database.
"""
self._cur.execute(sql)
if sql.lower().find("select") >= 0:
matches = []
for r in self._cur: matches.append(r)
return matches
def dump(self, ext=".xml"):
""" Dumps the data in the tables into another format (like XML).
"""
self._con.commit()
if ext == ".xml":
return self._dump_xml()
def _dump_xml(self):
data = "\n"
data += "\n"
for name in self._indices:
s = name.split("_")
data += "\n"
for table in self:
data += "
\n"
for row in table.all():
data += "\t\n"
i = 0
for field in table._fields:
r = str(row[i])
if row[i] == None: r = ""
data += "\t\t"+r+"\n"
i += 1
data += "\t\n"
data += "
\n"
data += ""
f = open(self._name+".xml", "w")
f.write(data)
f.close()
return data
# Deprecated, use dump() instead.
xml = dump
### TABLE ############################################################################################
class Table:
def __init__(self, db, name, key, fields):
"""Generic table.
Constructs a table with the given name, primary key and columns.
Each of the column names becomes the name of a table method
that fetches rows from the table.
For example, a table with an id field has the following method:
table.id(query, operator="=")
"""
self._db = db
self._name = name
self._key = key
self._fields = fields
for f in self._fields:
import new
im = lambda t, q, operator="=", fields="*", _field=f: t.find(q, operator, fields, _field)
setattr(self, f, new.instancemethod(im, self, None))
def _get_name(self): return self._name
name = property(_get_name)
def __len__(self):
""" The row count of the table. This should be optimized.
"""
sql = "select "+self._key+" from "+self._name
self._db._cur.execute(sql)
i = 0
for r in self._db._cur: i += 1
return i
def find(self, q, operator="=", fields="*", key=None):
"""A simple SQL SELECT query.
Retrieves all rows from the table
where the given query value is found in the given column (primary key if None).
A different comparison operator (e.g. >,