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