def test_rollback_migration_can_be_pretended(self):
resolver_mock = flexmock(DatabaseManager)
resolver_mock.should_receive('connection').and_return({})
resolver = flexmock(DatabaseManager({}))
connection = flexmock(Connection(None))
connection.should_receive('pretend').replace_with(
lambda callback: callback(None))
resolver.should_receive('connection').with_args(None).and_return(
connection)
migrator = flexmock(
Migrator(
flexmock(DatabaseMigrationRepository(resolver, 'migrations')),
resolver))
foo_migration = MigrationStub('foo')
bar_migration = MigrationStub('bar')
migrator.get_repository().should_receive('get_last').once().and_return(
[foo_migration, bar_migration])
bar_mock = flexmock(MigrationStub())
bar_mock.should_receive('down').once()
foo_mock = flexmock(MigrationStub())
foo_mock.should_receive('down').once()
migrator.should_receive('_resolve').with_args(
os.getcwd(), 'bar').once().and_return(bar_mock)
migrator.should_receive('_resolve').with_args(
os.getcwd(), 'foo').once().and_return(foo_mock)
migrator.rollback(os.getcwd(), True)
def execute(self, i, o):
"""
Executes the command.
:type i: cleo.inputs.input.Input
:type o: cleo.outputs.output.Output
"""
config = self._get_config(i)
self._resolver = DatabaseManager(config)
def test_connection_management(self):
resolver = flexmock(DatabaseManager)
resolver.should_receive('connection').once().with_args(
'foo').and_return('bar')
OrmModelStub.set_connection_resolver(DatabaseManager({}))
model = OrmModelStub()
model.set_connection('foo')
self.assertEqual('bar', model.get_connection())
def test_new_query_returns_eloquent_query_builder(self):
conn = flexmock(Connection)
grammar = flexmock(QueryGrammar)
processor = flexmock(QueryProcessor)
conn.should_receive('get_query_grammar').and_return(grammar)
conn.should_receive('get_post_processor').and_return(processor)
resolver = flexmock(DatabaseManager)
resolver.should_receive('connection').and_return(Connection(None))
OrmModelStub.set_connection_resolver(DatabaseManager({}))
model = OrmModelStub()
builder = model.new_query()
self.assertIsInstance(builder, Builder)
def test_nothing_is_done_when_no_migrations_outstanding(self):
resolver_mock = flexmock(DatabaseManager)
resolver_mock.should_receive('connection').and_return(None)
resolver = flexmock(DatabaseManager({}))
migrator = flexmock(
Migrator(
flexmock(DatabaseMigrationRepository(resolver, 'migrations')),
resolver))
g = flexmock(glob)
g.should_receive('glob').with_args(os.path.join(
os.getcwd(),
'*_*.py')).and_return([os.path.join(os.getcwd(), '1_foo.py')])
migrator.get_repository().should_receive('get_ran').once().and_return(
['1_foo'])
migrator.run(os.getcwd())
def test_up_migration_can_be_pretended(self):
resolver_mock = flexmock(DatabaseManager)
resolver_mock.should_receive('connection').and_return({})
resolver = flexmock(DatabaseManager({}))
connection = flexmock(Connection(None))
connection.should_receive('pretend').replace_with(
lambda callback: callback(None))
resolver.should_receive('connection').with_args(None).and_return(
connection)
migrator = flexmock(
Migrator(
flexmock(DatabaseMigrationRepository(resolver, 'migrations')),
resolver))
g = flexmock(glob)
g.should_receive('glob').with_args(os.path.join(
os.getcwd(), '*_*.py')).and_return([
os.path.join(os.getcwd(), '2_bar.py'),
os.path.join(os.getcwd(), '1_foo.py'),
os.path.join(os.getcwd(), '3_baz.py')
])
migrator.get_repository().should_receive('get_ran').once().and_return(
['1_foo'])
migrator.get_repository().should_receive(
'get_next_batch_number').once().and_return(1)
bar_mock = flexmock(MigrationStub())
bar_mock.should_receive('get_connection').once().and_return(None)
bar_mock.should_receive('up').once()
baz_mock = flexmock(MigrationStub())
baz_mock.should_receive('get_connection').once().and_return(None)
baz_mock.should_receive('up').once()
migrator.should_receive('_resolve').with_args(
os.getcwd(), '2_bar').once().and_return(bar_mock)
migrator.should_receive('_resolve').with_args(
os.getcwd(), '3_baz').once().and_return(baz_mock)
migrator.run(os.getcwd(), True)
# class Source(Model):
# pass
# source = Source.all()
from eloquent import DatabaseManager, Model
config = {
'default': 'mysql',
'mysql': {
'driver': 'mysql',
'host': 'localhost',
'port': 33060,
'database': 'aauuss',
'user': '******',
'password': '******',
'prefix': ''
}
}
db = DatabaseManager(config)
Model.set_connection_resolver(db)
class Source(Model):
pass
source = Source.all()
def createDatawarehouseClient(self):
monthList = {
'01': 'Jan',
'02': 'Feb',
'03': 'Mar',
'04': 'Apr',
'05': 'May',
'06': 'Jun',
'07': 'Jul',
'08': 'Aug',
'09': 'Sep',
'10': 'Oct',
'11': 'Nov',
'12': 'Dec'
}
mydb = mysql.connector.connect(host="localhost",
user="******",
passwd="",
database="interDB")
mycursor = mydb.cursor()
mycursor.execute(
"select country, countryCode, date, dateCode, sum(confirmed) as coronaConfirmed,"
" sum(deaths) as coronaDeaths, sum(recovered) as coronaRecovered from covidstats "
"group by country, countryCode, date, dateCode")
result = mycursor.fetchall()
preProcessing = TweetsPreProcessing()
configdb = {
'mysql': {
'driver': 'mysql',
'host': 'localhost',
'database': 'clientdb',
'user': '******',
'password': '',
'prefix': ''
}
}
db = DatabaseManager(configdb)
Model.set_connection_resolver(db)
cpt = 0
for row in result:
year = row[3][:4]
month = row[3][4:6]
day = row[3][6:8]
dayOfYear = date.fromisoformat(year + '-' + month + '-' +
day).timetuple().tm_yday
spring = range(80, 172)
summer = range(172, 264)
autumn = range(264, 355)
if dayOfYear in spring:
season = 'spring'
elif dayOfYear in summer:
season = 'summer'
elif dayOfYear in autumn:
season = 'autumn'
else:
season = 'winter'
timeAltID = row[3]
monthName = monthList[month]
rowLocation = preProcessing.getLocation(row[0])
rowTime = [
timeAltID, dayOfYear, day, month, monthName, year, season
]
time = DimTime()
location = DimLocation()
factCovCase = FactCovCase()
# fill the dimensions
timeID = time.insert(rowTime)
locationID = location.insert(rowLocation)
# fill the dimensions
# fill the fact table with foreign keys & mesures
nbrOfCases = row[4]
nbrOfDeath = row[5]
nbrOfRecovered = row[6]
row = [locationID, timeID, nbrOfCases, nbrOfDeath, nbrOfRecovered]
factCovCase.insert(row)
cpt += 1
print(cpt)
def createDatawareHouse(self, analysisID, numberOfTweets):
# get all the different concepts for this client
#allConcepts = dbIntermediary.table('alltweets').select(dbIntermediary.raw('distinct concept')).where('analysisID', '=', analysisID).get()
configdb = {
'mysql': {
'driver': 'mysql',
'host': 'localhost',
'database': 'datawarehouse',
'user': '******',
'password': '',
'prefix': ''
}
}
db = DatabaseManager(configdb)
Model.set_connection_resolver(db)
'''for temp in allConcepts:
concept = temp['concept']
print('concept is : ',concept)
print("hello")
#-----------------------------------------
# Fill the Fact Tweet Table
data = dbIntermediary.table('alltweets').select(dbIntermediary.raw(
'alltweets.languageName, alltweets.sourceName, alltweets.timeAltID, alltweets.sentimentLabel, alltweets.cityName, count(*) as numberOfTweets'))\
.where('alltweets.analysisID', '=', analysisID).where('alltweets.concept', '=', concept)\
.group_by('alltweets.languageName', 'alltweets.sourceName', 'alltweets.timeAltID', 'alltweets.sentimentLabel', 'alltweets.cityName')\
.get()
conceptTable = DimConcept()
rowConcept = [concept]
conceptID = conceptTable.insert(rowConcept)
cpt = 0
for tuple in data:
temp = dbIntermediary.table('alltweets').select(dbIntermediary.raw('*')).where(
'languageName', '=', tuple['languageName']).where(
'sourceName', '=', tuple['sourceName']).where(
'timeAltID', '=', tuple['timeAltID']).where(
'sentimentLabel', '=', tuple['sentimentLabel']).where(
'cityName', '=', tuple['cityName']).get()
row = temp[0]
rowSentiment = [row['sentimentLabel']]
rowSource = [row['sourceName']]
rowTime = [row['timeAltID'], row['dayOfWeek'], row['day'], row['month'], row['monthName'], row['year'],
row['season']]
rowLocation = [row['locationAltID'], row['cityName'], row['countryID'], row['countryName'],
row['continentID'], row['continentName']]
rowLanguage = [row['languageCode'], row['languageName']]
# instanciate the DB tables
time = DimTime()
sentiment = DimSentiment()
language = DimLanguage()
location = DimLocation()
source = DimSource()
factTweet = FactTweet()
# fill the dimensions
timeID = time.insert(rowTime)
sentimentID = sentiment.insert(rowSentiment)
languageID = language.insert(rowLanguage)
locationID = location.insert(rowLocation)
sourceID = source.insert(rowSource)
# fill the fact table with foreign keys & mesures
numberOfTweets = tuple['numberOfTweets']
row = [conceptID, locationID, sourceID, languageID, timeID, sentimentID, numberOfTweets]
factTweet.insert(row)
cpt += 1
print(cpt, "tuple inserted", sep=" ")
print("All tuples are inserted For the Fact Tweet for the concept :", concept)
# ----------------------------------------------------------------------------------------------------------------
# Fill the Fact Sentiment Table
data = dbIntermediary.table('alltweets').select(
dbIntermediary.raw('languageName, timeAltID, cityName, avg(sentimentValue) as averageSentiment'))\
.where('analysisID', '=', analysisID).where('concept', '=', concept).group_by(
'languageName', 'timeAltID', 'cityName').get()
cpt = 0
for tuple in data:
temp = dbIntermediary.table('alltweets').select(dbIntermediary.raw('*')).where(
'languageName', '=', tuple['languageName']).where(
'timeAltID', '=', tuple['timeAltID']).where(
'cityName', '=', tuple['cityName']).get()
row = temp[0]
rowTime = [row['timeAltID'], row['dayOfWeek'], row['day'], row['month'], row['monthName'], row['year'],
row['season']]
rowLocation = [row['locationAltID'], row['cityName'], row['countryID'], row['countryName'],
row['continentID'], row['continentName']]
rowLanguage = [row['languageCode'], row['languageName']]
# instanciate the DB tables
time = DimTime()
language = DimLanguage()
location = DimLocation()
factSentiment = FactSentiment()
# fill the dimensions
timeID = time.insert(rowTime)
languageID = language.insert(rowLanguage)
locationID = location.insert(rowLocation)
# fill the fact table with foreign keys & mesures
averageSentiment = tuple['averageSentiment']
row = [conceptID, locationID, languageID, timeID, averageSentiment]
factSentiment.insert(row)
cpt += 1
print(cpt, "tuple inserted", sep=" ")
print("All tuples are inserted For the Fact Sentiment for the concept :", concept)'''
listIDTweets = []
if numberOfTweets > 0:
listIDTweets = dbIntermediary.table('alltweets') \
.select('tweetID') \
.where('analysisID', '=', analysisID) \
.take(numberOfTweets)\
.get()
temp = []
for element in listIDTweets:
temp.append(element['tweetID'])
listIDTweets = temp
print(listIDTweets.__len__(), listIDTweets)
data = dbIntermediary.table('alltweets')\
.select(dbIntermediary.raw('languageCode , languageName, locationAltID, cityName, countryID, countryName, continentID, continentName, timeAltID ,dayOfWeek, day, month, monthName, year, season, sourceName, sentimentLabel, concept, count(*) as numberOfTweets'))\
.where('analysisID', '=', analysisID)\
.where_in('tweetID', listIDTweets)\
.group_by('languageCode', 'languageName', 'locationAltID', 'cityName', 'countryID', 'countryName', 'continentID', 'continentName', 'timeAltID' ,'dayOfWeek', 'day', 'month', 'monthName', 'year', 'season', 'sourceName', 'sentimentLabel', 'concept')\
.get()
else:
data = dbIntermediary.table('alltweets') \
.select(dbIntermediary.raw(
'languageCode , languageName, locationAltID, cityName, countryID, countryName, continentID, continentName, timeAltID ,dayOfWeek, day, month, monthName, year, season, sourceName, sentimentLabel, concept, count(*) as numberOfTweets')) \
.where('analysisID', '=', analysisID) \
.group_by('languageCode', 'languageName', 'locationAltID', 'cityName', 'countryID', 'countryName',
'continentID', 'continentName', 'timeAltID', 'dayOfWeek', 'day', 'month', 'monthName', 'year',
'season', 'sourceName', 'sentimentLabel', 'concept') \
.get()
cpt = 0
for row in data:
rowSentiment = [row['sentimentLabel']]
rowSource = [row['sourceName']]
rowTime = [
row['timeAltID'], row['dayOfWeek'], row['day'], row['month'],
row['monthName'], row['year'], row['season']
]
rowLocation = [
row['locationAltID'], row['cityName'], row['countryID'],
row['countryName'], row['continentID'], row['continentName']
]
rowLanguage = [row['languageCode'], row['languageName']]
rowConcept = [row['concept']]
# instanciate the DB tables
time = DimTime()
sentiment = DimSentiment()
language = DimLanguage()
location = DimLocation()
source = DimSource()
factTweet = FactTweet()
conceptTable = DimConcept()
# fill the dimensions
timeID = time.insert(rowTime)
sentimentID = sentiment.insert(rowSentiment)
languageID = language.insert(rowLanguage)
locationID = location.insert(rowLocation)
sourceID = source.insert(rowSource)
conceptID = conceptTable.insert(rowConcept)
# fill the fact table with foreign keys & mesures
numberOfTweet = row['numberOfTweets']
row = [
conceptID, locationID, sourceID, languageID, timeID,
sentimentID, numberOfTweet
]
factTweet.insert(row)
cpt += 1
print(cpt, "tuple inserted", sep=" ")
#print(listIDTweets)
# ----------------------------------------------------------------------------------------------------------------
# Fill the Fact Sentiment Table
if numberOfTweets > 0:
data = dbIntermediary.table('alltweets').select(
dbIntermediary.raw('languageCode , languageName, locationAltID, cityName, countryID, countryName, continentID, continentName, timeAltID ,dayOfWeek, day, month, monthName, year, season, concept, avg(sentimentValue) as averageSentiment'))\
.where('analysisID', '=', analysisID)\
.where_in('tweetID', listIDTweets)\
.group_by('languageCode', 'languageName', 'locationAltID', 'cityName', 'countryID', 'countryName',
'continentID', 'continentName', 'timeAltID', 'dayOfWeek', 'day', 'month', 'monthName', 'year',
'season', 'concept').get()
else:
data = dbIntermediary.table('alltweets').select(
dbIntermediary.raw(
'languageCode , languageName, locationAltID, cityName, countryID, countryName, continentID, continentName, timeAltID ,dayOfWeek, day, month, monthName, year, season, concept, avg(sentimentValue) as averageSentiment')) \
.where('analysisID', '=', analysisID) \
.group_by('languageCode', 'languageName', 'locationAltID', 'cityName', 'countryID', 'countryName',
'continentID', 'continentName', 'timeAltID', 'dayOfWeek', 'day', 'month', 'monthName', 'year',
'season', 'concept').get()
cpt = 0
for row in data:
print(row)
rowTime = [
row['timeAltID'], row['dayOfWeek'], row['day'], row['month'],
row['monthName'], row['year'], row['season']
]
rowLocation = [
row['locationAltID'], row['cityName'], row['countryID'],
row['countryName'], row['continentID'], row['continentName']
]
rowLanguage = [row['languageCode'], row['languageName']]
rowConcept = [row['concept']]
# instanciate the DB tables
time = DimTime()
language = DimLanguage()
location = DimLocation()
concept = DimConcept()
factSentiment = FactSentiment()
# fill the dimensions
timeID = time.insert(rowTime)
languageID = language.insert(rowLanguage)
locationID = location.insert(rowLocation)
conceptID = concept.insert(rowConcept)
# fill the fact table with foreign keys & mesures
averageSentiment = row['averageSentiment']
row = [conceptID, locationID, languageID, timeID, averageSentiment]
factSentiment.insert(row)
cpt += 1
print(cpt, "tuple inserted", sep=" ")
# ----------------------------------------------------------------------------------------------------------------
'''# Fill the Fact CovCase Table
from DWFiles.dimConcept import DimConcept
import mysql.connector
import random
config = {
'mysql': {
'driver': 'mysql',
'host': 'localhost',
'database': 'InterDB',
'user': '******',
'password': '',
'prefix': ''
}
}
dbIntermediary = DatabaseManager(config)
class DatawareHouseCreation:
def createDatawareHouse(self, analysisID, numberOfTweets):
# get all the different concepts for this client
#allConcepts = dbIntermediary.table('alltweets').select(dbIntermediary.raw('distinct concept')).where('analysisID', '=', analysisID).get()
configdb = {
'mysql': {
'driver': 'mysql',
'host': 'localhost',
'database': 'datawarehouse',
'user': '******',
'password': '',
'prefix': ''