def load_person():
import pandas as pd
from config.dbconnection import mysql
df = pd.read_csv('data/Person.Person.csv',
delimiter=';',
keep_default_na=False)
cursor = mysql.cursor()
sql = '''INSERT INTO person (BusinessEntityID,
PersonType,
NameStyle,
Title,
FirstName,
MiddleName,
LastName,
Suffix,
EmailPromotion,
AdditionalContactInfo,
Demographics,
rowguid,
ModifiedDate)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
print('Starting Person')
for index, row in df.iterrows():
cursor.execute(sql, tuple(row))
print('For Done')
mysql.commit()
print('Person OK')
return None
def load_salesOrderDetail():
import pandas as pd
from config.dbconnection import mysql
df = pd.read_csv('data/Sales.SalesOrderDetail.csv',
delimiter=';',
keep_default_na=False)
cursor = mysql.cursor()
sql = '''INSERT INTO salesorderdetail (SalesOrderID,
SalesOrderDetailID,
CarrierTrackingNumber,
OrderQty,
ProductID,
SpecialOfferID,
UnitPrice,
UnitPriceDiscount,
LineTotal,
rowguid,
ModifiedDate
)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
for index, row in df.iterrows():
cursor.execute(sql, tuple(row))
mysql.commit()
print('SalesOrderDetail OK')
return None
def load_salesOrderHeader():
import pandas as pd
import numpy as np
from config.dbconnection import mysql
df = pd.read_csv('data/Sales.SalesOrderHeader.csv', delimiter = ';', keep_default_na = False)
df['CurrencyRateID'] = df['CurrencyRateID'].replace({'NULL': 0})
df['SalesPersonID'] = df['SalesPersonID'].replace({'NULL': 0})
df['CreditCardID'] = df['CreditCardID'].replace({'NULL': 0})
df = df.drop(['Comment'], axis = 1)
cursor = mysql.cursor()
sql = """
INSERT INTO salesorderheader
(SalesOrderID,
RevisionNumber,
OrderDate,
DueDate,
ShipDate,
Status,
OnlineOrderFlag,
SalesOrderNumber,
PurchaseOrderNumber,
AccountNumber,
CustomerID,
SalesPersonID,
TerritoryID,
BillToAddressID,
ShipToAddressID,
ShipMethodID,
CreditCardID,
CreditCardApprovalCode,
CurrencyRateID,
SubTotal,
TaxAmt,
Freight,
TotalDue,
rowguid,
ModifiedDate)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"""
for index, row in df.iterrows():
cursor.execute(sql, tuple(row))
mysql.commit()
print('SalesOrderHeader OK')
return None
def load_product():
import pandas as pd
from config.dbconnection import mysql
df = pd.read_csv('data/Production.Product.csv',
delimiter=';',
keep_default_na=False)
cursor = mysql.cursor()
sql = """
INSERT INTO product
(ProductID,
Name,
ProductNumber,
MakeFlag,
FinishedGoodsFlag,
Color,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
Size,
SizeUnitMeasureCode,
WeightUnitMeasureCode,
Weight,
DaysToManufacture,
ProductLine,
Class,
Style,
ProductSubcategoryID,
ProductModelID,
SellStartDate,
SellEndDate,
DiscontinuedDate,
rowguid,
ModifiedDate)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
"""
for index, row in df.iterrows():
cursor.execute(sql, tuple(row))
mysql.commit()
print('Product OK')
return None
def load_specialOfferProduct():
import pandas as pd
from config.dbconnection import mysql
df = pd.read_csv('data/Sales.SpecialOfferProduct.csv',
delimiter=';',
keep_default_na=False)
cursor = mysql.cursor()
sql = """
INSERT INTO specialofferproduct (SpecialOfferID, ProductID, rowguid, ModifiedDate) VALUES (%s, %s, %s, %s);
"""
for index, row in df.iterrows():
cursor.execute(sql, tuple(row))
mysql.commit()
print('SpecialOfferProduct OK')
return None
def load_customer():
import pandas as pd
from config.dbconnection import mysql
df = pd.read_csv('data/Sales.Customer.csv',
delimiter=';',
keep_default_na=False)
df['PersonID'] = df['PersonID'].replace({'NULL': 0})
df['StoreID'] = df['StoreID'].replace({'NULL': 0})
cursor = mysql.cursor()
sql = '''
INSERT INTO customer (CustomerID, PersonID, StoreID, TerritoryID, AccountNumber, rowguid, ModifiedDate)
VALUES (%s, %s, %s, %s, %s, %s, %s)
'''
for index, row in df.iterrows():
cursor.execute(sql, tuple(row))
mysql.commit()
print('Customer OK')
return None