def db_conn():
from sqlalchemy import create_engine, engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.exc import InterfaceError
config = {
"drivername": "mysql+mysqlconnector",
"username": "******",
"password": "******",
"host": "mysql" if script_runs_in_container() else "localhost",
"port": "3306",
"database": "database",
}
Base = automap_base()
try:
engine = create_engine(engine.url.URL(**config))
except InterfaceError:
print("Something went wrong while connecting to MySQL db")
Base.prepare(engine, reflect=True)
yield engine
print("Disposing engine")
engine.dispose()
def __init__(self, filename):
echo = False
if self.DebugLevel > 2:
echo = True
self.SessionInstance=None
if not filename:
return
self.FileList=[]
self.AttachedDatabases=[]
if self.UseAttach:
self.Engine=create_engine('sqlite://',echo=False)
self.AttachedDatabases.append('Diff')
self.Engine.execute("ATTACH DATABASE '%s' AS Diff;" % filename)
self.Session=sessionmaker()
self.Session.configure(bind=self.Engine)
self.SessionInstance = self.Session()
metadata=MetaData(self.Engine)
type_map={}
for file_list in self.SessionInstance.query(FileList).all():
if type_map.has_key(file_list.type):
continue
type_map[file_list.type]=1
self.FileList.append(file_list)
self.AttachedDatabases.append(file_list.type)
query="ATTACH DATABASE '%s' AS %s;" % (file_list.filename,file_list.type)
self.Engine.execute(query)
else:
self.Engine = create_engine('sqlite:///' + filename, echo = echo)
self.metadata=MetaData(self.Engine)
self.Session=sessionmaker()
self.Session.configure(bind=self.Engine)
self.SessionInstance = self.Session()
self.SessionInstancesMap={}
for file_list in self.SessionInstance.query(FileList).all():
filename=self.GetFilename(file_list.filename)
engine = create_engine('sqlite:///' + filename, echo = echo)
metadata=Base.metadata
metadata.create_all(engine)
self.Session=sessionmaker()
self.Session.configure(bind=engine)
self.SessionInstancesMap[file_list.type] = self.Session()
self.FileList.append(file_list)
engine.dispose()
if not self.SessionInstancesMap.has_key('Source'):
self.SessionInstancesMap['Source']=self.SessionInstance
if not self.SessionInstancesMap.has_key('Target'):
self.SessionInstancesMap['Target']=self.SessionInstance
def _ping_listener(engine, dbapi_conn, connection_rec, connection_proxy):
"""Ensures that MySQL and DB2 connections are alive.
Borrowed from:
http://groups.google.com/group/sqlalchemy/msg/a4ce563d802c929f
"""
cursor = dbapi_conn.cursor()
try:
ping_sql = 'select 1'
if engine.name == 'ibm_db_sa':
# DB2 requires a table expression
ping_sql = 'select 1 from (values (1)) AS t1'
cursor.execute(ping_sql)
except Exception as ex:
if engine.dialect.is_disconnect(ex, dbapi_conn, cursor):
msg = _('Database server has gone away: %s') % ex
LOG.warning(msg)
# if the database server has gone away, all connections in the pool
# have become invalid and we can safely close all of them here,
# rather than waste time on checking of every single connection
engine.dispose()
# this will be handled by SQLAlchemy and will force it to create
# a new connection and retry the original action
raise sqla_exc.DisconnectionError(msg)
else:
raise
def postgres_db(orm_metadata):
engine = create_engine(
config.get_postgres_uri(),
isolation_level="READ_COMMITTED",
)
wait_for_engine_to_come_up(engine)
orm_metadata.create_all(engine)
yield engine
engine.dispose()
def sendRating(rating, username):
global engine
dict = {
'rating': [rating],
'username': [username],
'date': [datetime.datetime.now()]
}
df = pd.DataFrame.from_dict(dict)
df.to_sql('ratings', con=engine, if_exists='append', index=False)
engine.dispose()
return {"status": "rating sent"}
def destroy_db_engine(db_engine=None):
"""Destroys the DB SQAlchemy engine.
:param db_engine: Engine to destroy
:return: Nothing
"""
global engine
if db_engine:
db_engine.dispose()
else:
engine.dispose()
def putDataForUserGermany():
global engine
api = configscript.setupTwitterAuth()
places = api.geo_search(query="Germany", granularity="country")
place_id = places[0].id
tweets = tw.Cursor(api.search,
q="place:%s" % place_id,
tweet_mode='extended',
lang='en').items()
for tweet in tweets:
username = tweet.user.screen_name
allTweets = tw.Cursor(api.user_timeline,
screen_name=username,
tweet_mode="extended",
exclude_replies=False,
include_rts=False,
lang='en').items(150)
listAllTweets = list(allTweets)
if (len(listAllTweets) < 1):
continue
tweetsDict = m.getTweetsDict(listAllTweets)
score = m.getOverallScore(tweetsDict)
if (score == -1):
continue
tweetsonlyscore = m.tweetsOnlyScore(tweetsDict)
scoremax = m.getHappiestTweet(tweetsonlyscore)
scoremin = m.getSaddestTweet(tweetsonlyscore)
dict = {
username: {
"score": score,
"min": scoremin["score"],
"max": scoremax["score"]
}
}
df = pd.DataFrame.from_dict(dict, orient='index')
df.index.name = 'username'
df.to_sql('germany_users', con=engine, if_exists='append')
engine.execute(
"DELETE FROM germany_users T1 USING germany_users T2 WHERE T1.ctid < T2.ctid AND T1.username = T2.username;"
)
engine.dispose()
def putDataDB():
global engine
api = configscript.setupTwitterAuth()
places = api.geo_search(query="Denmark", granularity="country")
place_id = places[0].id
tweets = tw.Cursor(api.search,
q="place:%s" % place_id,
tweet_mode='extended',
lang='en').items()
df = pd.DataFrame.from_dict(m.getTweetsDictRaw(tweets), orient='index')
df.set_index('id', inplace=True)
df.to_sql('tweets', con=engine, if_exists='append')
engine.execute(
"DELETE FROM tweets T1 USING tweets T2 WHERE T1.ctid < T2.ctid AND T1.id = T2.id AND T1.score = T2.score AND T1.created = T2.created;"
)
engine.dispose()
def celebrityScore(username):
print(username)
global engine
api = configscript.setupTwitterAuth()
allTweets = tw.Cursor(api.user_timeline,
screen_name=username,
tweet_mode="extended",
exclude_replies=False,
include_rts=False,
lang='en').items()
listAllTweets = list(allTweets)
if (len(listAllTweets) == 0):
return
tweetsDict = m.getTweetsDict(listAllTweets)
score = m.getOverallScore(tweetsDict)
if (score == -1):
return
user = api.get_user(username)
# Remove _normal from profile image URL
profile_image_url = user.profile_image_url_https
url = re.sub('_normal', '', profile_image_url)
dict = {username: {"score": score, "pic": url}}
df = pd.DataFrame.from_dict(dict, orient='index')
df.index.name = 'username'
df.to_sql('celebrity', con=engine, if_exists='append')
engine.execute(
"DELETE FROM celebrity T1 USING celebrity T2 WHERE T1.ctid < T2.ctid AND T1.username = T2.username;"
)
engine.dispose()
def __init__(self, filename):
echo = False
if self.DebugLevel > 2:
echo = True
self.SessionInstance = None
if not filename:
return
self.FileList = []
self.AttachedDatabases = []
if self.UseAttach:
self.Engine = create_engine('sqlite://', echo=False)
self.AttachedDatabases.append('Diff')
self.Engine.execute("ATTACH DATABASE '%s' AS Diff;" % filename)
self.Session = sessionmaker()
self.Session.configure(bind=self.Engine)
self.SessionInstance = self.Session()
metadata = MetaData(self.Engine)
type_map = {}
for file_list in self.SessionInstance.query(FileList).all():
if type_map.has_key(file_list.type):
continue
type_map[file_list.type] = 1
self.FileList.append(file_list)
dgf_filename = self.GetFilename(filename, file_list.filename)
try:
import sqlite3
conn = sqlite3.connect(dgf_filename)
c = conn.cursor()
c.execute(
"SELECT name FROM sqlite_master WHERE type='table' AND name='OneLocationInfo'"
)
data = c.fetchone()
if data != None:
c.execute(
'''ALTER TABLE OneLocationInfo RENAME TO BasicBlock;'''
)
conn.close()
except:
pass
self.AttachedDatabases.append(file_list.type)
query = "ATTACH DATABASE '%s' AS %s;" % (dgf_filename,
file_list.type)
self.Engine.execute(query)
else:
self.Engine = create_engine('sqlite:///' + filename, echo=echo)
self.metadata = MetaData(self.Engine)
self.Session = sessionmaker()
self.Session.configure(bind=self.Engine)
self.SessionInstance = self.Session()
self.SessionInstancesMap = {}
for file_list in self.SessionInstance.query(FileList).all():
filename = self.GetFilename(filename, file_list.filename)
engine = create_engine('sqlite:///' + filename, echo=echo)
metadata = Base.metadata
metadata.create_all(engine)
self.Session = sessionmaker()
self.Session.configure(bind=engine)
self.SessionInstancesMap[file_list.type] = self.Session()
self.FileList.append(file_list)
engine.dispose()
if not self.SessionInstancesMap.has_key('Source'):
self.SessionInstancesMap['Source'] = self.SessionInstance
if not self.SessionInstancesMap.has_key('Target'):
self.SessionInstancesMap['Target'] = self.SessionInstance
def __init__(self, filename):
echo = False
if self.DebugLevel > 2:
echo = True
self.SessionInstance=None
if not filename:
return
self.FileList=[]
self.AttachedDatabases=[]
if self.UseAttach:
self.Engine=create_engine('sqlite://',echo=False)
self.AttachedDatabases.append('Diff')
self.Engine.execute("ATTACH DATABASE '%s' AS Diff;" % filename)
self.Session=sessionmaker()
self.Session.configure(bind=self.Engine)
self.SessionInstance = self.Session()
metadata=MetaData(self.Engine)
type_map={}
for file_list in self.SessionInstance.query(FileList).all():
if type_map.has_key(file_list.type):
continue
type_map[file_list.type]=1
self.FileList.append(file_list)
dgf_filename=self.GetFilename(filename, file_list.filename)
try:
import sqlite3
conn = sqlite3.connect(dgf_filename)
c=conn.cursor()
c.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='OneLocationInfo'")
data=c.fetchone()
if data!=None:
c.execute('''ALTER TABLE OneLocationInfo RENAME TO BasicBlock;''')
conn.close()
except:
pass
self.AttachedDatabases.append(file_list.type)
query="ATTACH DATABASE '%s' AS %s;" % (dgf_filename,file_list.type)
self.Engine.execute(query)
else:
self.Engine = create_engine('sqlite:///' + filename, echo = echo)
self.metadata=MetaData(self.Engine)
self.Session=sessionmaker()
self.Session.configure(bind=self.Engine)
self.SessionInstance = self.Session()
self.SessionInstancesMap={}
for file_list in self.SessionInstance.query(FileList).all():
filename=self.GetFilename(filename, file_list.filename)
engine = create_engine('sqlite:///' + filename, echo = echo)
metadata=Base.metadata
metadata.create_all(engine)
self.Session=sessionmaker()
self.Session.configure(bind=engine)
self.SessionInstancesMap[file_list.type] = self.Session()
self.FileList.append(file_list)
engine.dispose()
if not self.SessionInstancesMap.has_key('Source'):
self.SessionInstancesMap['Source']=self.SessionInstance
if not self.SessionInstancesMap.has_key('Target'):
self.SessionInstancesMap['Target']=self.SessionInstance
