-
Notifications
You must be signed in to change notification settings - Fork 0
/
loadData.py
47 lines (37 loc) · 1.15 KB
/
loadData.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
# load data from excel files to sqlalchemy
import pandas as pd
from catTV import db
from catTV.models import Video, Category
videoFile = "./CatVideos.xlsx"
categoryFile = "./CatVideoCategories.xlsx"
df = pd.read_excel(videoFile, header = 0)
df.head()
df2 = pd.read_excel(categoryFile, header = 0)
df2.head()
# TODO: improve this so not recreating table everytime
db.drop_all()
db.create_all()
# Video.query.all() to check
for idx,row in df.iterrows():
v = Video()
v.id = int(df.loc[idx,"id"])
v.url = df.loc[idx,"url"]
v.title = df.loc[idx,"title"]
db.session.add(v)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
print(str(e))
# Category.query.all() to check
for idx,row in df2.iterrows():
c = Category()
c.id = int(df2.loc[idx,"id"])
c.category = df2.loc[idx,"category"]
db.session.add(c)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
print(str(e))
# df.to_sql('Video', con=connection, if_exists='append', index_label="id")