In [1]:
import mimesis
import pandas as pd
import random
from secrets import randbelow # requires python 3.6.3
from sqlalchemy import create_engine, select
from sqlalchemy import Column, Date, Integer, MetaData, Numeric, Table, Text, Time, String
from sqlalchemy_utils import UUIDType
import uuid
NUM_CUSTOMERS = 10
NUM_PURCHASES = 15 # Should be >= NUM_CUSTOMERS
NUM_BOOKS = 5
In [2]:
add = mimesis.Address('en')
bus = mimesis.Business('en')
cod = mimesis.Code('en')
cry = mimesis.Cryptographic('en')
dat = mimesis.Datetime('en')
num = mimesis.Numbers('en')
per = mimesis.Personal('en')
engine = create_engine("postgresql://clinton:reece@localhost:5432/transactions")
meta = MetaData()
In [3]:
customers_table = Table('customers',
meta,
Column('customer_id', UUIDType(binary=False), primary_key=True),
Column('name', Text, nullable=False),
Column('email', Text, nullable=False),
Column('phone', Text, nullable=True),
Column('street', Text, nullable=False),
Column('city', Text, nullable=False),
Column('state', String(length=2), nullable=False))
In [4]:
books_table = Table('books',
meta,
Column('isbn', Text, nullable=False, primary_key=True),
Column('price', Numeric, nullable=False))
In [5]:
purchases_table = Table('purchases',
meta,
Column('receipt_id', UUIDType(binary=False), index=True),
Column('customer_id', UUIDType(binary=False)),
Column('date', Date, nullable=True),
Column('time', Time, nullable=True),
Column('isbn', Text, nullable=False),
Column('quantity', Integer, nullable=False))
In [6]:
with engine.connect() as conn:
# create tables if they don't exist
meta.create_all(engine, checkfirst=True)
#generate customers table
for _ in range(0, NUM_CUSTOMERS):
insert_data = customers_table.insert() \
.values(customer_id = cry.uuid(),
name = per.full_name(),
email = per.email(),
phone = per.telephone(),
street = add.address(),
city = add.city(),
state = add.state(abbr=True))
conn.execute(insert_data)
# create dataframe
customers_df = pd.read_sql_table(table_name='customers', con=conn)
customers_df
Out[6]:
In [7]:
# select customer IDs
with engine.connect() as conn:
customers_select = conn.execute(select([customers_table]))
customers_list = []
for c in customers_select:
customers_list.append(c[0])
customers_list
Out[7]:
In [8]:
with engine.connect() as conn:
# create tables if they don't exist
meta.create_all(engine, checkfirst=True)
#generate customers table
for _ in range(0, NUM_BOOKS):
insert_data = books_table.insert() \
.values(isbn = cod.isbn(),
price = bus.price(minimum=5.00, maximum=100.00).rstrip(' $'))
conn.execute(insert_data)
# create dataframe
books_df = pd.read_sql_table(table_name='books', con=conn)
books_df
Out[8]:
In [9]:
# select ISBNs
with engine.connect() as conn:
books_select = conn.execute(select([books_table]))
books_list = []
for b in books_select:
books_list.append(b[0])
books_list
Out[9]:
In [10]:
# generate receipt IDs
receipt_id = []
for _ in range(0, NUM_PURCHASES):
receipt_id.append(cry.uuid())
receipt_id
Out[10]:
In [11]:
with engine.connect() as conn:
# create tables if they don't exist
meta.create_all(engine, checkfirst=True)
# generate purchases table
for _ in range(0, NUM_PURCHASES):
# select random IDs with replacement
insert_data = purchases_table.insert() \
.values(receipt_id = receipt_id[randbelow(NUM_PURCHASES)],
customer_id = customers_list[randbelow(NUM_CUSTOMERS)],
date = dat.date(start=2010, end=2017),
time = dat.time(),
isbn = list(random.sample(books_list, 1))[0],
quantity = num.between(1, 5))
conn.execute(insert_data)
# create dataframe
purchases_df = pd.read_sql_table(table_name='purchases', con=conn)
purchases_df
Out[11]: