def parse(self, response):
res = response.text
res = json.loads(res)
res = res["Time Series (5min)"]
Date = []
_open = []
high = []
low = []
close = []
for x, y in res.items():
Date.append(x)
_open.append(y['1. open'])
high.append(y['2. high'])
low.append(y['3. low'])
close.append(y['4. close'])
data = zip(Date, _open, high, low, close)
for item in data:
scraped_info = {
'Date': item[0],
'Open': item[1],
'High': item[2],
'Low': item[3],
'Close': item[4],
}
yield scraped_info
try:
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO Fin_AlphaVintage(Date,High, Open, Low,Close) VALUES (%s,%s,%s,%s,%s)"""
for d in range(0, len(res)):
record_to_insert = (Date[d], high[d], _open[d], low[d],
close[d])
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
if (connection):
print("Failed to insert record into test2 table", error)
finally:
#closing database connection
if (connection):
cursor.close()
print("PostgreSQL connection is closed")
def create_tables():
#connection = psycopg2.connect(user='******',password='******',host='localhost',port="5432",database='postman')
cursor = connection.cursor()
#db= '''CREATE database sunilkumar''';
#cursor.execute(db)
cursor.execute("DROP TABLE IF EXISTS Animal_cat_facts")
cursor.execute("DROP TABLE IF EXISTS Animal_cats")
cursor.execute("DROP TABLE IF EXISTS Animal_HTTPcat")
cursor.execute("DROP TABLE IF EXISTS Anime_Ghibli")
cursor.execute("DROP TABLE IF EXISTS Anime_Jikan")
cursor.execute("DROP TABLE IF EXISTS Art_Copperhewit")
cursor.execute("DROP TABLE IF EXISTS Art_Harvardmus")
cursor.execute("DROP TABLE IF EXISTS Envi_OpenAQ")
cursor.execute("DROP TABLE IF EXISTS Fin_AlphaVintage")
connection.commit()
table1 = '''CREATE TABLE Animal_cat_facts(id text,text text,type text,upvotes text)'''
cursor.execute(table1)
table2 = '''CREATE TABLE Animal_cats(id text,name text, origin text, affection_level text)'''
cursor.execute(table2)
table3 = '''CREATE TABLE Animal_HTTPcat(para text,Image text)'''
cursor.execute(table3)
table4 = '''CREATE TABLE Anime_Ghibli(id text,title text, director text, producer text)'''
cursor.execute(table4)
table5 = '''CREATE TABLE Anime_Jikan(Episode_id text,title text, Aired text, Recap text)'''
cursor.execute(table5)
table6 = '''CREATE TABLE Art_Copperhewit(id text,title text, url text, start_date text,end_date text)'''
cursor.execute(table6)
table7 = '''CREATE TABLE Art_Harvardmus(id text,objevtcount text, lastupdate text, name text)'''
cursor.execute(table7)
table8 = '''CREATE TABLE Envi_OpenAQ(country text,Name text, City text, Locations text,Count text)'''
cursor.execute(table8)
table9 = '''CREATE TABLE Fin_AlphaVintage(Date text,High text, Open text, Low text,Close text)'''
cursor.execute(table9)
print("All Tables created successfully!!!")
connection.commit()
def parse(self, response):
res = response.text
res = json.loads(res)
res = res["exhibitions"]
_id = []
title = []
url = []
start = []
end = []
for i in range(0, len(res)):
_id.append(res[i]['id'])
title.append(res[i]['title'])
url.append(res[i]['url'])
start.append(res[i]['date_start'])
end.append(res[i]['date_end'])
data = zip(_id, title, url, start, end)
for item in data:
scraped_info = {
'Id': item[0],
'title': item[1],
'url': item[2],
'start': item[3],
'end': item[4],
}
yield scraped_info
try:
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO Art_Copperhewit(id,title, url, start_date,end_date) VALUES (%s,%s,%s,%s,%s)"""
for d in range(0, len(res)):
record_to_insert = (_id[d], title[d], url[d], start[d], end[d])
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
if (connection):
print("Failed to insert record into test2 table", error)
finally:
#closing database connection
if (connection):
cursor.close()
print("PostgreSQL connection is closed")
def parse(self, response):
res = response.text
res = json.loads(res)
res = res["results"]
country = []
name = []
city = []
locations = []
count = []
for i in range(0, len(res)):
country.append(res[i]['country'])
name.append(res[i]['name'])
city.append(res[i]['city'])
locations.append(res[i]['locations'])
count.append(res[i]['count'])
data = zip(country, name, city, locations, count)
for item in data:
scraped_info = {
'country': item[0],
'Name': item[1],
'City': item[2],
'Locations': item[3],
'Count': item[4],
}
yield scraped_info
try:
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO Envi_OpenAQ(country,Name, City, Locations,Count) VALUES (%s,%s,%s,%s,%s)"""
for d in range(0, len(res)):
record_to_insert = (country[d], name[d], city[d], locations[d],
count[d])
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
if (connection):
print("Failed to insert record into test2 table", error)
finally:
#closing database connection
if (connection):
cursor.close()
print("PostgreSQL connection is closed")
def parse(self, response):
res=response.text
res=json.loads(res)
s=res["all"]
_id=[]
text=[]
_type=[]
upvotes=[]
for i in range(0,len(s)):
_id.append(s[i]['_id'])
text.append(s[i]['text'])
_type.append(s[i]['type'])
upvotes.append(s[i]['upvotes'])
data = zip(_id,text,_type,upvotes)
for item in data:
scraped_info = {
'id' : item[0],
'text' : item[1],
'type' : item[2],
'upvotes' : item[3],
}
yield scraped_info
try:
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO Animal_cat_facts(id,text, type, upvotes) VALUES (%s,%s,%s,%s)"""
for d in range(0,len(s)):
record_to_insert = (_id[d], text[d], _type[d],upvotes[d] )
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print (count, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error :
if(connection):
print("Failed to insert record into test2 table", error)
finally:
#closing database connection
if(connection):
cursor.close()
print("PostgreSQL connection is closed")
def parse(self, response):
eps = jikan.anime(457, extension='episodes')
eps = eps["episodes"]
title = []
ep_id = []
aired = []
recap = []
for i in range(0, len(eps)):
ep_id.append(eps[i]['episode_id'])
title.append(eps[i]['title'])
aired.append(eps[i]['aired'])
recap.append(eps[i]['recap'])
data = zip(ep_id, title, aired, recap)
for item in data:
scraped_info = {
'Ep_id': item[0],
'Title': item[1],
'Aired': item[2],
'Recap': item[3],
}
yield scraped_info
try:
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO Anime_Jikan(Episode_id,title, Aired, Recap) VALUES (%s,%s,%s,%s)"""
for d in range(0, len(res)):
record_to_insert = (ep_id[d], title[d], aired[d], recap[d])
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
if (connection):
print("Failed to insert record into test2 table", error)
finally:
#closing database connection
if (connection):
cursor.close()
print("PostgreSQL connection is closed")
def parse(self, response):
res = response.text
res = json.loads(res)
_id = []
name = []
origin = []
ur = []
for i in range(0, len(res)):
_id.append(res[i]["id"])
name.append(res[i]["name"])
origin.append(res[i]["origin"])
ur.append(res[i]["affection_level"])
data = zip(_id, name, origin, ur)
for item in data:
scraped_info = {
'id': item[0],
'name': item[1],
'origin': item[2],
'affection_level': item[3],
}
yield scraped_info
try:
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO Animal_cats(id,name, origin, affection_level) VALUES (%s,%s,%s,%s)"""
for d in range(0, len(res)):
record_to_insert = (_id[d], name[d], origin[d], ur[d])
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
if (connection):
print("Failed to insert record into test2 table", error)
finally:
#closing database connection
if (connection):
cursor.close()
print("PostgreSQL connection is closed")
def parse(self, response):
res = response.text
res = json.loads(res)
_id = []
title = []
director = []
producer = []
for i in range(0, len(res)):
_id.append(res[i]['id'])
title.append(res[i]['title'])
director.append(res[i]['director'])
producer.append(res[i]['producer'])
data = zip(_id, title, director, producer)
for item in data:
scraped_info = {
'Id': item[0],
'Title': item[1],
'Director': item[2],
'Producer': item[3],
}
yield scraped_info
try:
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO Anime_Ghibli(id,title, director, producer) VALUES (%s,%s,%s,%s)"""
for d in range(0, len(res)):
record_to_insert = (_id[d], title[d], director[d], producer[d])
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
if (connection):
print("Failed to insert record into test2 table", error)
finally:
#closing database connection
if (connection):
cursor.close()
print("PostgreSQL connection is closed")
def parse(self, response):
para = response.css("p::text").extract()
Img = response.css("a::attr(href)").extract()
para = para[2:59]
Img = Img[2:59]
data = zip(para, Img)
for item in data:
scraped_info = {
'Img': item[1],
'para': item[0],
}
yield scraped_info
try:
cursor = connection.cursor()
postgres_insert_query = """ INSERT INTO Animal_HTTPcat(Para, Image) VALUES (%s,%s)"""
for d in range(0, len(res)):
record_to_insert = (para[d], Img[d])
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
if (connection):
print("Failed to insert record into test2 table", error)
finally:
#closing database connection
if (connection):
cursor.close()
print("PostgreSQL connection is closed")