create-dreamsales.ipynb

Kernel: Python 3 (Ubuntu Linux)

Создаём базу данных и три таблицы

import sqlite3
conn = sqlite3.connect('dreamsales.db')
c = conn.cursor()
sql = '''
CREATE TABLE Salespeople (
    SNo INTEGER PRIMARY KEY,
    SName TEXT,
    City TEXT, Comm REAL
    )'''
c.execute(sql)
sql = '''
CREATE TABLE Customers (
    CNo INTEGER PRIMARY KEY,
    CName TEXT,
    City TEXT,
    Rating INTEGER,
    SNo REFERENCES Salespeople(SNo)
    )'''
c.execute(sql)
sql = '''
CREATE TABLE Orders (
    ONo INTEGER PRIMARY KEY,
    Amt REAL,
    ODate TEXT,
    CNo REFERENCES Customers(CNo),
    SNo REFERENCES Salespeople(SNo)
    )'''
c.execute(sql)

<sqlite3.Cursor at 0x7f8763a0af80>

Опишем вспомогательные функции для создания строк в каждой таблице

def create_salesman(_sno, _sname, _city, _comm) :
    c.execute("INSERT INTO Salespeople VALUES ({0}, '{1}', '{2}', {3})".format(_sno, _sname, _city, _comm))
    conn.commit()
def create_customer(_cno, _cname, _city, _rating, _sno) :
    c.execute("INSERT INTO Customers VALUES ({0}, '{1}', '{2}', {3}, {4})".format(_cno, _cname, _city, _rating, _sno))
    conn.commit()
def create_order(_ono, _amt, _odate, _cno, _sno) :
    c.execute("INSERT INTO Orders VALUES ({0}, {1}, '{2}', {3}, {4})".format(_ono, _amt, _odate, _cno, _sno))
    conn.commit()

Вспомогательная функция для чтения из файла сразу в таблицу

def file2table(filename) :
    f = open(filename+'.csv', 'r')
    head = f.readline()
    for line in f :
        s = line[:-1].replace(',', '.').split(';')
        if filename == 'salespeople' :
            create_salesman(*s)
        elif filename == 'customers' :
            create_customer(*s)
        elif filename == 'orders' :
            create_order(*s)
    f.close()

Заполняем таблицы в базе данных

file2table('salespeople')
file2table('customers')
file2table('orders')

Проверяем, всё ли ОК

c.execute('SELECT * FROM Customers')
print(c.fetchall())
c.execute('SELECT * FROM Salespeople')
print(c.fetchall())
c.execute('SELECT * FROM Orders')
print(c.fetchall())

[(2001, 'Hoffmann', 'London', 100, 1001), (2002, 'Giovanni', 'Rome', 200, 1003), (2003, 'Liu', 'San Jose', 200, 1002), (2004, 'Grass', 'Berlin', 300, 1002), (2006, 'Clemens', 'London', 100, 1001), (2007, 'Pereira', 'Rome', 100, 1004), (2008, 'Cisneros', 'San Jose', 300, 1007), (2009, 'Nielsen', 'New York', 200, 1003)]
[(1001, 'Peel', 'London', 0.12), (1002, 'Serres', 'San Jose', 0.13), (1003, 'Axelrod', 'New York', 0.1), (1004, 'Motika', 'London', 0.11), (1005, 'Johnson', 'New York', 0.15), (1007, 'Rifkin', 'Barcelona', 0.15)]
[(3001, 18.69, '03.05.2015', 2008, 1007), (3002, 1900.1, '03.05.2015', 2007, 1004), (3003, 761.19, '03.05.2015', 2001, 1001), (3005, 5160.45, '03.05.2015', 2003, 1002), (3006, 1098.16, '03.05.2015', 2008, 1007), (3007, 75.75, '04.05.2015', 2004, 1002), (3008, 4723.0, '05.05.2015', 2006, 1001), (3009, 1713.23, '04.05.2015', 2002, 1003), (3010, 1309.95, '06.05.2015', 2004, 1002), (3011, 9891.88, '06.05.2015', 2006, 1001)]