def check_email(self, email):
db = SQLSoup(engine)
try:
result = db.accounts.filter(db.accounts.email == email).one()
return "found"
except:
return "not found"
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
self._editable = config.get("Editable", False)
usermap = config.get('Map', {})
self.map = yaml.load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
self.pool_size = int(config.get('poolSize') or 5)
self.pool_timeout = int(config.get('poolTimeout') or 10)
# create the connectstring like
params = {'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database}
self.connect_string = self._create_connect_string(params)
log.info("using the connect string {0!s}".format(self.connect_string))
try:
log.debug("using pool_size={0!s} and pool_timeout={1!s}".format(
self.pool_size, self.pool_timeout))
self.engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False,
pool_size=self.pool_size,
pool_timeout=self.pool_timeout)
except TypeError:
# The DB Engine/Poolclass might not support the pool_size.
log.debug("connecting without pool_size.")
self.engine = create_engine(self.connect_string,
encoding=self.encoding,
convert_unicode=False)
# create a configured "Session" class
Session = sessionmaker(bind=self.engine)
# create a Session
self.session = Session()
self.session._model_changes = {}
self.db = SQLSoup(self.engine)
self.TABLE = self.db.entity(self.table)
return self
def check_username(self, username):
db = SQLSoup(engine)
try:
result = db.accounts.filter(db.accounts.username == username).one()
return "found"
except:
return "not found"
def get_account(self, username):
db = SQLSoup(engine)
try:
result = db.accounts.filter(db.accounts.username == username).one()
return result
except:
return -1
def loadConfig(self, config):
"""
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
"""
self.server = config.get('Server', "")
self.driver = config.get('Driver', "")
self.database = config.get('Database', "")
self.resolverId = self.database
self.port = config.get('Port', "")
self.limit = config.get('Limit', 100)
self.user = config.get('User', "")
self.password = config.get('Password', "")
self.table = config.get('Table', "")
self._editable = config.get("Editable", False)
self.password_hash_type = config.get("Password_Hash_Type", "SSHA256")
usermap = config.get('Map', {})
self.map = yaml.safe_load(usermap)
self.reverse_map = dict([[v, k] for k, v in self.map.items()])
self.where = config.get('Where', "")
self.encoding = str(config.get('Encoding') or "latin1")
self.conParams = config.get('conParams', "")
self.pool_size = int(config.get('poolSize') or 5)
self.pool_timeout = int(config.get('poolTimeout') or 10)
# recycle SQL connections after 2 hours by default
# (necessary for MySQL servers, which terminate idle connections after some hours)
self.pool_recycle = int(config.get('poolRecycle') or 7200)
# create the connectstring like
params = {
'Port': self.port,
'Password': self.password,
'conParams': self.conParams,
'Driver': self.driver,
'User': self.user,
'Server': self.server,
'Database': self.database
}
self.connect_string = self._create_connect_string(params)
# get an engine from the engine registry, using self.getResolverId() as the key,
# which involves the connect string and the pool settings.
self.engine = get_engine(self.getResolverId(), self._create_engine)
# We use ``scoped_session`` to be sure that the SQLSoup object
# also uses ``self.session``.
Session = scoped_session(sessionmaker(bind=self.engine))
# Session should be closed on teardown
self.session = Session()
register_finalizer(self.session.close)
self.session._model_changes = {}
self.db = SQLSoup(self.engine, session=Session)
self.db.session._model_changes = {}
self.TABLE = self.db.entity(self.table)
return self
def __init__(self):
if not self.singleton:
self.__class__.singleton = True
else:
raise SystemError, 'SQLite3Vocab is singleton.'
path = os.path.dirname(os.path.realpath(__file__))
self.engine = SQLSoup('sqlite:////%s/sqlite3.db' % path)
self.comune_id = self.engine.comuni.codice_istat
self.provincia_id = self.engine.province.sigla
self.regione_id = self.engine.regioni.codice_istat
def get_table(self, tablename):
db = SQLSoup(
"mysql+pymysql://cic_admin:159357a@{}:{}/cic_finance".format(
'192.168.10.11', 3306))
table = db.entity(tablename)
records = table.all()
res = [{
key: val
for key, val in record.__dict__.items() if not key.startswith('_')
} for record in records]
return res
def main(config_path):
cfg = ConfigParser()
cfg.read(config_path)
# Start Twisted logging to console.
log.startLogging(stderr)
# Read database configuration options.
db_url = cfg.get('database', 'url')
# Read website configuration options.
http_debug = cfg.getboolean('http', 'debug', fallback=False)
http_host = cfg.get('http', 'host', fallback='localhost')
http_port = cfg.getint('http', 'port', fallback=5000)
http_pool = cfg.getint('http', 'pool_size', fallback=4)
# Default to much saner database query defaults and always
# commit and/or flush statements explicitly.
# factory = sessionmaker(autocommit=False, autoflush=False)
# Prepare database connection with table reflection.
engine = create_engine(db_url)
session = scoped_session(sessionmaker(autocommit=False, autoflush=False))
db = SQLSoup(engine, session=session)
# Extract manager options, sans the pool_size we handle here.
# pool_size = int(manager_opts.pop('pool_size', 2))
pool_size = 2
# Set the correct thread pool size for the manager.
reactor.suggestThreadPoolSize(pool_size)
# Prepare the website that will get exposed to the users.
site = make_site(db, debug=http_debug)
# Prepare WSGI site with a separate thread pool.
pool = ThreadPool(http_pool, http_pool, 'http')
site = Site(WSGIResource(reactor, pool, site))
pool.start()
# Bind the website to it's address.
reactor.listenTCP(http_port, site, interface=http_host)
# Run the Twisted reactor until the user terminates us.
reactor.run()
# Kill the HTTP ThreadPool.
pool.stop()
def testconnection(cls, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = cls._create_connect_string(param)
log.info(u"using the connect string {0!s}".format(
censor_connect_string(connect_string)))
engine = create_engine(connect_string)
# create a configured "Session" class
Session = scoped_session(sessionmaker(bind=engine))
session = Session()
db = SQLSoup(engine, session=Session)
try:
TABLE = db.entity(param.get("Table"))
conditions = cls._append_where_filter([], TABLE,
param.get("Where"))
filter_condition = and_(*conditions)
result = session.query(TABLE).filter(filter_condition).count()
num = result
desc = "Found {0:d} users.".format(num)
except Exception as exx:
desc = "failed to retrieve users: {0!s}".format(exx)
finally:
# We do not want any leftover DB connection, so we first need to close
# the session such that the DB connection gets returned to the pool (it
# is still open at that point!) and then dispose the engine such that the
# checked-in connection gets closed.
session.close()
engine.dispose()
return num, desc
def create_account(self, params):
db = SQLSoup(engine)
# Generate a random 32 character number and get it's md5 hash
random_num = randrange(100000000000000000000000000000000,
999999999999999999999999999999999, 2)
salt = hashlib.md5(str(random_num).encode())
# Concatenate the users password and salt, then hash it with sha256
concat = str(params['password']) + str(salt.hexdigest())
pass_hash = hashlib.sha256(concat.encode())
db.accounts.insert(project_name=params['project_name'],
email=params['email'],
username=params['username'],
password=pass_hash.hexdigest(),
salt=salt.hexdigest())
db.commit()
def connect_db(config):
"""
:param ConfigParser config
"""
factory = sessionmaker(autocommit=False, autoflush=False)
db_engine = config.get('database', 'engine')
username = config.get('database', 'username')
password = config.get('database', 'password')
host = config.get('database', 'hostname')
port = config.get('database', 'port')
db_name = config.get('database', 'db_name')
db_url = str(db_engine) + '://' + str(username) + ':' + str(
password) + '@' + str(host) + ':' + str(port) + '/' + str(db_name)
engine = create_engine(db_url)
session = scoped_session(factory)
db_meta = reflect(engine)
db = SQLSoup(db_meta, session=session)
return db
def testconnection(cls, param):
"""
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter
to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port,
Limit, Table, Map
Where, Encoding, conParams
"""
num = -1
desc = None
connect_string = cls._create_connect_string(param)
log.info("using the connect string {0!s}".format(connect_string))
engine = create_engine(connect_string)
# create a configured "Session" class
session = sessionmaker(bind=engine)()
db = SQLSoup(engine)
try:
TABLE = db.entity(param.get("Table"))
conditions = cls._append_where_filter([], TABLE,
param.get("Where"))
filter_condition = and_(*conditions)
result = session.query(TABLE).filter(filter_condition).count()
num = result
desc = "Found {0:d} users.".format(num)
except Exception as exx:
desc = "failed to retrieve users: {0!s}".format(exx)
return num, desc
def main(url, crawl_id):
G = nx.DiGraph()
soup = SQLSoup(url)
peers_with_ip = 0
reachable_peers = 0
peers = dict(
(p.id, p) for p in soup.peers.filter_by(crawl_id=crawl_id).all())
edges = soup.edges.filter_by(crawl_id=crawl_id).all()
for p in peers.values():
if p.reachable: reachable_peers += 1
if p.ip: peers_with_ip += 1
G.add_node(
p.id,
color=('blue' if p.ip else 'red' if not p.reachable else 'orange'))
for e in edges:
G.add_edge(getattr(e, 'from'),
e.to,
directed=e.directed,
color=('black' if e.directed else 'red'))
degrees = find_degrees(G)
export_to_dot(G, peers, degrees, 'crawl-%d.dot' % crawl_id)
UG = nx.Graph(G)
print('vertices with ip: ', peers_with_ip)
print('vertices with reachable ip: ', reachable_peers)
print('vertices: ', len(G))
print('edges: ', G.size())
print('average degree: ', degree_avg(G.degree()))
print('average in degree: ', degrees['in_degree_avg'])
print('average out degree: ', degrees['out_degree_avg'])
print('average unknown degree: ', degrees['unknown_degree_avg'])
print('graph edges (as undirected graph): ', UG.size())
print('graph diameter (as undirected graph): ', nx.diameter(UG))
@site.after_request
def insert_lang_cookie(response):
"""
Make user locale selection persistent using a cookie.
"""
if 'lang' in request.args:
response.set_cookie('lang', request.args['lang'])
return response
# Use SQLSoup for database access.
db = SQLSoup(site.config['SQLSOUP_DATABASE_URI'])
# Specify primary keys for SQLSoup to allow us to work with views.
map_view(db, 'my_subscriptions', ['id'])
map_view(db, 'my_channels', ['id'])
map_view(db, 'my_campaigns', ['id'])
map_view(db, 'recipients', ['user', 'channel'])
# Specify automatic row relations.
db.campaign.relate('Channel', db.channel)
db.my_campaigns.relate('Channel', db.channel,
primaryjoin=(db.channel.c.id == db.my_campaigns.c.channel),
foreign_keys=[db.my_campaigns.c.channel])
db.channel.relate('Template', db.template)
vars = _select.lower().split('select ')[1].split(',')
if len(vars) == 1:
_select = _select.replace('select', 'select shot,')
# default order is by shot
if _order is '':
if len(vars) is 1:
_order = 'order by shot'
else:
_order = 'order by ' + vars[0]
url_cpts = db_url.split('sqlite:///')
if not os.path.exists(url_cpts[-1]):
input('{fn} could not be found!!\n'.format(fn=url_cpts[-1]))
h1db = SQLSoup(db_url)
# Note - this info. is here now, but disappears if there is an error - save
tables = list(h1db._cache)
print(tables if len(tables) > 0 else ' possibly no tables in ' + db_url +
" although this doesn't mean anything sometimes")
if len(tables) == 0 and block == 1:
input('Continue?')
try:
h1db.SUMM = eval('h1db.' + table)
except Exception as reason:
print(reason.__repr__(), ' table names are ', tables)
raise
cols = h1db.SUMM.c.keys()
if plabel is not '':
def __init__(self, dsn):
self.soup = SQLSoup(dsn)
from fastapi import FastAPI, HTTPException
from sqlsoup import SQLSoup
from api_test import db, model
app = FastAPI()
engine = db.get_db_engine()
db = SQLSoup(engine)
@app.get("/")
def read_root():
return {"hello": "world"}
@app.get("/counts")
def read_count_ids(only_latest: bool = False):
""" Get all counts
Field `only_latest` is optional.
If True, will only return the lastest count per device_id"""
counts = db.counts.all()
if only_latest:
return_dict = {}
for count in counts:
if count.device_id not in return_dict or count.timestamp > return_dict[
count.device_id].timestamp:
return_dict[count.device_id] = count
return return_dict
def connect(self):
"""Connect to remote join backend (DB)"""
self.db = SQLSoup(self.connect_string)
from sqlsoup import SQLSoup
# server details
SERVER = os.environ.get("SERVER", "localhost")
USERNAME = os.environ.get("DB_USERNAME", "sa")
PASSWORD = os.environ.get("DB_PASSWORD", "reallyStrongPwd123")
DATABASE = os.environ.get("DB_NAME", "DW_AccessBI")
PORT = os.environ.get("DB_PORT", 1433)
# load data from SQL Server
# db_connection = pymssql.connect(
# server=SERVER, user=USERNAME, password=PASSWORD, database=DATABASE, port=PORT)
conn_str = "mssql+pymssql://{user}:{password}@{server}/{database}?charset=utf8".format(
user=USERNAME, password=PASSWORD, server=SERVER, database=DATABASE)
sqlsoup_obj = SQLSoup(conn_str)
engine = sqlsoup_obj.engine
def get_data_from_sqlserver(sql_string, connection_obj=engine):
df = pd.read_sql(sql_string, connection_obj)
return df
def get_categorical_values(column, table_name=None):
if table_name is None:
table_name = "D_" + column.upper()
table = sqlsoup_obj.entity(table_name)
rows = table.all()
values = set()
