def __init__(self, use_test_db):
if use_test_db:
self.dbase = sqlsoup.SQLSoup("sqlite:///%s" % TEST_DB_PATH)
if not os.path.exists(TEST_DB_PATH):
self.create_test_db()
else:
real_db_path = REAL_DB_PATH % get_password()
self.dbase = sqlsoup.SQLSoup("mysql://%s" % real_db_path)
def test_first(self):
db = sqlsoup.SQLSoup(engine)
MappedUsers = db.users
user = db.users.filter(db.users.name == 'Bhargan Basepair').one()
eq_(
user,
MappedUsers(name='Bhargan Basepair',
email='*****@*****.**',
password='******',
classname=None,
admin=1))
db.rollback()
user = db.users.get('Bhargan Basepair')
eq_(
user,
MappedUsers(name='Bhargan Basepair',
email='*****@*****.**',
password='******',
classname=None,
admin=1))
db.rollback()
user = db.users.filter_by(name='Bhargan Basepair').one()
eq_(
user,
MappedUsers(name='Bhargan Basepair',
email='*****@*****.**',
password='******',
classname=None,
admin=1))
db.rollback()
def __init__(self, schema='products', usesqlsoup=True, use_sqlite=False):
try:
self.schema = schema or es_constants.es2globals['schema_products']
# logger.debug("Usesqlsoup is: %s " % usesqlsoup)
if usesqlsoup:
dburl = ConnectDB.get_db_url(use_sqlite)
self.db = sqlsoup.SQLSoup(dburl)
self.session = self.db.session
else:
self.db = self.get_db_engine()
Mysession = sessionmaker(bind=self.db, autoflush=True)
self.session = Mysession()
# logger.debug("is_testing is: %s " % self.is_testing())
if use_sqlite:
self.schema = None
else:
self.db.schema = self.schema
except:
exceptiontype, exceptionvalue, exceptiontraceback = sys.exc_info()
#print traceback.format_exc()
# Exit the script and print an error telling what happened.
logger.error(
"Database connection failed!\n -> {}".format(exceptionvalue))
def find_with_bb_and_keyword(imageURL, bb, keyword, number_of_results=10):
# pdb.set_trace()
db = sqlsoup.SQLSoup("mysql://*****:*****@localhost/trendi2")
table = db.unique_items_by_image_url
session = db.session
query = session.query(table)\
.filter(table.KEYWORDS.like('%'+keyword+'%'))\
.filter(table.IMAGEURL.isnot(None))\
.filter(table.fingerprint.isnot(None))
db_fingerprint_list = []
for row in query:
fp_dict = {}
fp_dict["id"] = row.iditems
fp_dict["clothingClass"] = row.class1
fp_dict["fingerPrintVector"] = json.loads(row.fingerprint)
fp_dict["imageURL"] = row.IMAGEURL
fp_dict["buyURL"] = row.BUYURL
db_fingerprint_list.append(fp_dict)
#Fingerprint the bounded area
fgpt = fp2.fp(imageURL, bb)
targetDict = {"clothingClass": keyword, "fingerPrintVector": fgpt}
# pdb.set_trace()
closest_matches = findNNs(targetDict, db_fingerprint_list,
number_of_results)
return fgpt.tolist(), closest_matches
def insert_gaz_data(db_uri, gaz_file):
db = sqlsoup.SQLSoup(db_uri)
db.state.delete()
db.county.delete()
with open(gaz_file, 'rb') as csvfile:
reader = UnicodeReader(csvfile, dialect='excel-tab', encoding='iso-8859-2')
headers = map(string.strip, reader.next())
states_seen = set()
for row in reader:
row = csv_row_to_dict(headers, row)
geoid = row['GEOID']
state_fips = int(geoid[0:2])
county_fips = int(geoid[2:5])
if state_fips not in states_seen:
db.state.insert(fips_code=state_fips, abbr=row['USPS'])
states_seen.add(state_fips)
db.county.insert(
state_fips_code=state_fips,
county_fips_code=county_fips,
name=row['NAME'],
ansi_code=row['ANSICODE'],
population=int(row['POP10']),
housing_units=int(row['HU10']),
land_area=int(row['ALAND']),
water_area=int(row['AWATER']),
latitude=row['INTPTLAT'],
longitude=row['INTPTLONG'])
db.commit()
def __init__(self):
# Instance of MySQL db cursor.
self.mysql_db = sqlsoup.SQLSoup(MYSQL_URL)
# Initializing of psql session.
self.session = get_db_session(api.settings)()
# Need for remembering the valid problems.
self.problems_id = []
def __init__(self, schema='products', usesqlsoup=True):
try:
self.schema = schema or es_constants.es2globals['schema_products']
if usesqlsoup:
dburl = self.get_db_url()
self.db = sqlsoup.SQLSoup(dburl)
# myengine = self.db.engine
self.session = sqlsoup.Session # self.db.session
else:
self.db = self.get_db_engine()
mysession = sessionmaker(bind=self.db, autoflush=False)
self.session = mysession()
self.db.schema = self.schema
import logging
self.db.engine.echo = False
self.db.engine.logger.setLevel(logging.NOTSET)
self.db.engine.logger.disabled = True
self.db.engine.logger.level = logging.NOTSET
logging.basicConfig(level=logging.NOTSET)
# sqllogger = logging.getLogger('sqlalchemy.engine')
logging.getLogger('sqlalchemy.engine').setLevel(
logging.NOTSET) # NOTSET
except:
exceptiontype, exceptionvalue, exceptiontraceback = sys.exc_info()
#print traceback.format_exc()
# Exit the script and print an error telling what happened.
logger.error(
"Database connection failed!\n -> {}".format(exceptionvalue))
def test_clear(self):
db = sqlsoup.SQLSoup(engine)
eq_(db.loans.count(), 1)
_ = db.loans.insert(book_id=1, user_name='Bhargan Basepair')
db.expunge_all()
db.flush()
eq_(db.loans.count(), 1)
def test_relations(self):
db = sqlsoup.SQLSoup(engine)
db.users.relate('loans', db.loans)
MappedLoans = db.loans
MappedUsers = db.users
eq_(
db.users.get('Joe Student').loans, [
MappedLoans(book_id=1,
user_name='Joe Student',
loan_date=datetime.datetime(2006, 7, 12, 0, 0))
])
db.rollback()
eq_(
db.users.filter(~db.users.loans.any()).all(), [
MappedUsers(name='Bhargan Basepair',
email='*****@*****.**',
password='******',
classname=None,
admin=1)
])
db.rollback()
del db._cache['users']
db.users.relate('loans',
db.loans,
order_by=db.loans.loan_date,
cascade='all, delete-orphan')
def get_db(self):
if not self.db:
self.db_engine = sqlalchemy.create_engine(self.connection,
**self.kwargs)
self.db = sqlsoup.SQLSoup(self.db_engine,
session=scoped_session(
sessionmaker(bind=self.db_engine)))
return self.db
def __init__(self, name, xml):
self.name = name
self.xml = xml
self.xml_object = objectify.fromstring(self.xml)
# self.variable_mappings
# self.dbs get dbs from xml or somewhere
self.db = sqlsoup.SQLSoup(
'mysql+pymysql://david:david@localhost:3306/sm_play')
def test_mapped_join(self):
db = sqlsoup.SQLSoup(engine)
join1 = MappedJoin = db.join(db.users, db.loans, isouter=True)
mj = join1.filter_by(name='Joe Student').all()
eq_(mj, [
MappedJoin(
name='Joe Student',
email='*****@*****.**',
password='******',
classname=None,
admin=0,
book_id=1,
user_name='Joe Student',
loan_date=datetime.datetime(2006, 7, 12, 0, 0),
)
])
db.rollback()
join2 = MappedJoin = db.join(join1, db.books)
mj = join2.all()
eq_(mj, [
MappedJoin(
name='Joe Student',
email='*****@*****.**',
password='******',
classname=None,
admin=0,
book_id=1,
user_name='Joe Student',
loan_date=datetime.datetime(2006, 7, 12, 0, 0),
id=1,
title='Mustards I Have Known',
published_year='1989',
authors='Jones',
)
])
eq_(
db.with_labels(join1).c.keys(), [
'users_name',
'users_email',
'users_password',
'users_classname',
'users_admin',
'loans_book_id',
'loans_user_name',
'loans_loan_date',
])
labeled_loans = db.with_labels(db.loans)
eq_(
db.join(db.users, labeled_loans, isouter=True).c.keys(), [
'name',
'email',
'password',
'classname',
'admin',
'loans_book_id',
'loans_user_name',
'loans_loan_date',
])
def get_dashboard_engine_connection(db_type,
db_user,
db_password,
db_host,
db_port,
db_name,
db_socket_file=None):
global dashboard_engine_connection
# Build the connection string according to the connection parameters:
# Start with the database type, username, and password.
# They will always be present:
connectString = "%s://%s:%s@" % (db_type, db_user, db_password)
# If there was no host specified, use "localhost":
if db_host == None:
connectString += "localhost"
else:
connectString += db_host
# If there was a port specified, use it:
if db_port != None:
connectString += ":" + str(db_port)
# Then add the database name. This will always be present:
connectString += "/" + db_name
# If there was a customized socket file specified, add it:
if db_socket_file != None:
connectString += '?unix_socket=%s' % (db_socket_file, )
logging.debug("connect String->%s< " % connectString)
try:
logging.debug("Connecting to the database.")
# Create an SQLAlchemy "Engine" object (representing a database
# connection). Disable connection pooling by selecting a "null pool".
# We need to disable connection pooling to ensure that when we
# disconnect from the database with the "dashboardDB.close()" method, our
# connection to the database is completely severed. With connection
# pooling enabled, when we attempt to close the connection, it will
# actually be held open in anticipation of it being used by another
# query.
databaseEngine = sqlalchemy.create_engine(connectString,
poolclass=NullPool)
# Instantiate an "SQLSoup" object, which gives us a database session to
# use:
dashboard_engine_connection = sqlsoup.SQLSoup(databaseEngine)
logging.debug("obtained engine connection")
except:
logging.error(
"can't connect to database server Exception type:%s Exception value:%s"
% (sys.exc_info()[0], sys.exc_info()[1]))
return dashboard_engine_connection
def test_map_to_no_pk_selectable(self):
db = sqlsoup.SQLSoup(engine)
table = Table('users', db._metadata, Column('id', Integer))
assert_raises_message(sqlsoup.SQLSoupError,
"table 'users' does not have a primary ",
db.map_to,
'users',
selectable=table)
def test_map_to_attr_present(self):
db = sqlsoup.SQLSoup(engine)
users = db.users
assert_raises_message(sqlsoup.SQLSoupError,
"Attribute 'users' is already mapped",
db.map_to,
'users',
tablename='users')
def test_map_to_nothing(self):
db = sqlsoup.SQLSoup(engine)
assert_raises_message(
sqlsoup.ArgumentError,
"'tablename' or 'selectable' argument is "
"required.",
db.map_to,
'users',
)
def test_map_to_table_not_string(self):
db = sqlsoup.SQLSoup(engine)
table = Table('users', db._metadata,
Column('id', Integer, primary_key=True))
assert_raises_message(sqlsoup.ArgumentError,
"'tablename' argument must be a string.",
db.map_to,
'users',
tablename=table)
def test_map_to_string_not_selectable(self):
db = sqlsoup.SQLSoup(engine)
assert_raises_message(sqlsoup.ArgumentError,
"'selectable' argument must be a "
"table, select, join, or other "
"selectable construct.",
db.map_to,
'users',
selectable='users')
def test_map_to_invalid_schema(self):
db = sqlsoup.SQLSoup(engine)
table = Table('users', db._metadata, Column('id', Integer))
assert_raises_message(sqlsoup.ArgumentError,
"'tablename' argument is required when "
"using 'schema'.",
db.map_to,
'users',
selectable=table,
schema='hoho')
def __init__(self, table_name, sentry=cli):
'''table_name(product 和 analysis)'''
# 表名称
self.table_name = table_name
self._sentry = sentry
# 连接对应的表
self.db = sqlsoup.SQLSoup(SURL, session=Session)
self.table = self.db.entity(table_name)
def get_connection(self,
config_stream,
config=None,
security_level=ConnectionLevel.READ_ONLY,
sql_echo=False):
'''Return the SQLSoup connection to the server/access level of your choice'''
self.database_echo = sql_echo
self.config_stream = config_stream
logger.debug('Using config stream: {0}'.format(self.config_stream))
an_engine = self.get_engine(config, security_level)
is_valid_connection = False
try:
db = sqlsoup.SQLSoup(an_engine)
is_valid_connection = self.validate_connection(db)
logger.debug("Validating connection..isvalid: {0}".format(
is_valid_connection))
except Exception as e:
logger.error(
"invalid connection, try reconnect of engine: {0}".format(e))
an_engine = self.get_engine(config,
security_level,
force_flag=True)
db = sqlsoup.SQLSoup(an_engine)
if is_valid_connection is False:
try:
self.validate_connection(db)
except Exception:
logger.fatal("invalid connection, 2nd try")
raise ManagerConnectionException(
'Bad server {0}; failed on reconnect'.format(config))
db.echo = True
logger.debug('Returning database instance: {0}'.format(db))
return db
def update_connections(db_user, db_password, database, manager, lunsArray, portalsArray):
db_string = 'postgresql+psycopg2://' + db_user + ':' + db_password + '@' + manager + '/' + database
db = sqlsoup.SQLSoup(db_string)
print("Current Conections:")
verify_iscsi_changes(db=db)
sleep(5)
change_iscsi(luns=lunsArray, portals=portalsArray, db=db)
sleep(5)
print("New Conections:")
verify_iscsi_changes(db=db)
db.commit()
return 1
def setUp(self):
""".create a simple test database and fill with some data """
self.db = sqlsoup.SQLSoup("sqlite:///%s" %
TEST_CONSTANTS['DatabasePath'])
try:
self.db.execute("DROP TABLE test")
except: #pylint: disable=bare-except
pass #Due to table not existing - this is fine
self.db.execute(
"CREATE TABLE test (id INTEGER PRIMARY KEY, text1 TEXT, num1 INTEGER)"
)
def test_explicit_session(self):
Session = scoped_session(sessionmaker())
db = sqlsoup.SQLSoup(engine, session=Session)
try:
MappedUsers = db.users
sess = Session()
assert db.users._query.session is db.users.session is sess
row = db.users.insert(name='new name', email='new email')
assert row in sess
finally:
sess.rollback()
sess.close()
def test_map_to_table_or_selectable(self):
db = sqlsoup.SQLSoup(engine)
table = Table('users', db._metadata,
Column('id', Integer, primary_key=True))
assert_raises_message(
sqlsoup.ArgumentError,
"'tablename' and 'selectable' arguments are mutually exclusive",
db.map_to,
'users',
tablename='users',
selectable=table)
def test_cls_crud(self):
db = sqlsoup.SQLSoup(engine)
MappedUsers = db.users
db.users.filter_by(name='Bhargan Basepair').update(
dict(name='Some New Name'))
u1 = db.users.filter_by(name='Some New Name').one()
eq_(
u1,
MappedUsers(name='Some New Name',
email='*****@*****.**',
password='******',
classname=None,
admin=1))
def __init__(self, uri):
self.db = sqlsoup.SQLSoup(uri)
self.movie = self.db.Movie
self.movies = self.db.Movies
self.actor = ""
self.budget = 0
self.country = ""
self.runtime = 0
self.rate = float(0)
self.year = 0
self.gross = 0
self.languate = ""
self.movie_name = ""
def test_selectable(self):
db = sqlsoup.SQLSoup(engine)
MappedBooks = db.books
b = db.books._table
s = select([b.c.published_year,
func.count('*').label('n')],
from_obj=[b],
group_by=[b.c.published_year])
s = s.alias('years_with_count')
years_with_count = db.map(s, primary_key=[s.c.published_year])
eq_(
years_with_count.filter_by(published_year='1989').all(),
[MappedBooks(published_year='1989', n=1)])
def test_dont_persist_alias(self):
db = sqlsoup.SQLSoup(engine)
MappedBooks = db.books
b = db.books._table
s = select([b.c.published_year,
func.count('*').label('n')],
from_obj=[b],
group_by=[b.c.published_year])
s = s.alias('years_with_count')
years_with_count = db.map(s, primary_key=[s.c.published_year])
assert_raises(sqlsoup.SQLSoupError,
years_with_count.insert,
published_year='2007',
n=1)
def test_order_by(self):
db = sqlsoup.SQLSoup(engine)
MappedUsers = db.users
users = db.users.order_by(db.users.name).all()
eq_(users, [
MappedUsers(name='Bhargan Basepair',
email='*****@*****.**',
password='******',
classname=None,
admin=1),
MappedUsers(name='Joe Student',
email='*****@*****.**',
password='******',
classname=None,
admin=0)
])
