Pony-SQLite3_simplified

A simple and easy way to work with SQL without real knowledge of it, just basic understanding of Python and Pony-ORM.

This code is acctually a fast implementation I did for an SQL test that I had no time to study or enough knowledge about it.

Libraries used

pony
pandas
random
os
sys
sqlite3
datetime
faker

An already created SQL code to generate Database if necessary

squery = '''
CREATE TABLE Worker (
    WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    FIRST_NAME CHAR(25),
    LAST_NAME CHAR(25),
    SALARY INT(15),
    JOINING_DATE DATETIME,
    DEPARTMENT CHAR(25)
);

INSERT INTO Worker 
    (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
        (001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'),
        (002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'),
        (003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'),
        (004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'),
        (005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'),
        (006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'),
        (007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'),
        (008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');

CREATE TABLE Bonus (
    WORKER_REF_ID INT,
    BONUS_AMOUNT INT(10),
    BONUS_DATE DATETIME,
    FOREIGN KEY (WORKER_REF_ID)
        REFERENCES Worker(WORKER_ID)
        ON DELETE CASCADE
);

INSERT INTO Bonus 
    (WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES
        (001, 5000, '16-02-20'),
        (002, 3000, '16-06-11'),
        (003, 4000, '16-02-20'),
        (001, 4500, '16-02-20'),
        (002, 3500, '16-06-11');
CREATE TABLE Title (
    WORKER_REF_ID INT,
    WORKER_TITLE CHAR(25),
    AFFECTED_FROM DATETIME,
    FOREIGN KEY (WORKER_REF_ID)
        REFERENCES Worker(WORKER_ID)
        ON DELETE CASCADE
);

INSERT INTO Title 
    (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
 (001, 'Manager', '2016-02-20 00:00:00'),
 (002, 'Executive', '2016-06-11 00:00:00'),
 (008, 'Executive', '2016-06-11 00:00:00'),
 (005, 'Manager', '2016-06-11 00:00:00'),
 (004, 'Asst. Manager', '2016-06-11 00:00:00'),
 (007, 'Executive', '2016-06-11 00:00:00'),
 (006, 'Lead', '2016-06-11 00:00:00'),
 (003, 'Lead', '2016-06-11 00:00:00');
 '''

Main code

db_file = 'test.db'
conn, curs = create_connection(db_file, rewrite=True) #rewrite is used to recreate db every time the main code runs, without issues.
## curs.close()
## conn.close()

db = Database()
#...............................................................ENTITIES...................................................................
class Worker(db.Entity):
#     _table_ = "worker"
    WORKER_ID = PrimaryKey(int)
    FIRST_NAME = Optional(str)
    LAST_NAME = Optional(str)
    SALARY = Optional(int)
    JOINING_DATE = Optional(datetime.datetime)
    DEPARTMENT = Optional(str)
    BONUSES = Set('Bonus')
    TITLES = Set('Title')
    
class Bonus(db.Entity):    
    WORKER_REF_ID = PrimaryKey(Worker)
    BONUS_AMOUNT = Optional(int)
    BONUS_DATE = Optional(datetime.datetime)   
        
class Title(db.Entity): 
    WORKER_REF_ID = PrimaryKey(Worker)
    WORKER_TITLE = Optional(str)
    AFFECTED_FROM = Optional(datetime.datetime)
#__________________________________________________________________________________________________________________________________________

# If SQL_queries_exist=True the above SQL code will be used
gen_fake_dat = execute_commands(squery, SQL_queries_exist=True)#<-<--<---<----<-----<------<---------------------------------------------<<

db.bind(provider='sqlite', filename=f"{os.path.abspath(os.getcwd())}/{db_file}", create_db=True)
db.generate_mapping(create_tables=True, check_tables=gen_fake_dat)

#If there is not an SQL code for the insertion of values in the db, then with SQL_queries_exist=False a specified number of random values will be added
if gen_fake_dat:
    fake_fillin(db.entities, 5)# 5 random values in each.

Output for SQL_queries_exist=True sample

GET NEW CONNECTION
RELEASE CONNECTION
GET CONNECTION FROM THE LOCAL POOL
PRAGMA foreign_keys = false
BEGIN IMMEDIATE TRANSACTION
CREATE INDEX "idx_bonus__worker_ref_id" ON "Bonus" ("WORKER_REF_ID")

CREATE INDEX "idx_title__worker_ref_id" ON "Title" ("WORKER_REF_ID")

COMMIT
PRAGMA foreign_keys = true
CLOSE CONNECTION
RELEASE CONNECTION

Database representation

ER_draw(db_file)
for ent in list(db.entities.keys()):
    sql_present(ent_name=str_to_class(ent).__name__, entity_present=True)

Worker	WORKER_ID	FIRST_NAME	LAST_NAME	SALARY	JOINING_DATE	DEPARTMENT
0	1	Monika	Arora	100000	14-02-20 09.00.00	HR
1	2	Niharika	Verma	80000	14-06-11 09.00.00	Admin
2	3	Vishal	Singhal	300000	14-02-20 09.00.00	HR
3	4	Amitabh	Singh	500000	14-02-20 09.00.00	Admin
4	5	Vivek	Bhati	500000	14-06-11 09.00.00	Admin
5	6	Vipul	Diwan	200000	14-06-11 09.00.00	Account
6	7	Satish	Kumar	75000	14-01-20 09.00.00	Account
7	8	Geetika	Chauhan	90000	14-04-11 09.00.00	Admin

Bonus	WORKER_REF_ID	BONUS_AMOUNT	BONUS_DATE
0	1	5000	16-02-20
1	2	3000	16-06-11
2	3	4000	16-02-20
3	1	4500	16-02-20
4	2	3500	16-06-11

Title	WORKER_REF_ID	WORKER_TITLE	AFFECTED_FROM
0	1	Manager	2016-02-20 00:00:00
1	2	Executive	2016-06-11 00:00:00
2	8	Executive	2016-06-11 00:00:00
3	5	Manager	2016-06-11 00:00:00
4	4	Asst. Manager	2016-06-11 00:00:00
5	7	Executive	2016-06-11 00:00:00
6	6	Lead	2016-06-11 00:00:00
7	3	Lead	2016-06-11 00:00:00

Examples
Every example uses a Pony and a raw SQLite implementation

select(raw_sql('substr(w.FIRST_NAME,1,3)') for w in Worker).without_distinct().show() #Pony
sql_present('Select substr(FIRST_NAME,1,3) from Worker;') # SQLite

SELECT substr(w.FIRST_NAME,1,3)
FROM "Worker" "w"

raw_sql('substr(w.FIRST_NAME,1,3)')
-----------------------------------
Mon                                
Nih                                
Vis                                
Ami                                
Viv                                
Vip                                
Sat                                
Gee

	substr(FIRST_NAME,1,3)
0	Mon
1	Nih
2	Vis
3	Ami
4	Viv
5	Vip
6	Sat
7	Gee

select((w.DEPARTMENT, sum(w.SALARY)) for w in Worker).show()
sql_present('SELECT DEPARTMENT, sum(Salary) from worker group by DEPARTMENT;')

SELECT "w"."DEPARTMENT", coalesce(SUM("w"."SALARY"), 0)
FROM "Worker" "w"
GROUP BY "w"."DEPARTMENT"

w.DEPARTMENT|sum(w.SALARY)
------------+-------------
Account     |275000       
Admin       |1170000      
HR          |400000

	DEPARTMENT	sum(Salary)
0	Account	275000
1	Admin	1170000
2	HR	400000

In this example we can also check out that using the outputted SQL code from the Pony execution inside sql_present yields the same result

select((w.FIRST_NAME, w.SALARY) for w in Worker if w.SALARY == max(w.SALARY for w in Worker)).without_distinct().show()

sql_present('''SELECT "w"."FIRST_NAME", "w"."SALARY"
FROM "Worker" "w"
WHERE "w"."SALARY" = (
    SELECT MAX("w-2"."SALARY")
    FROM "Worker" "w-2"
    )''')

sql_present('SELECT FIRST_NAME, SALARY from Worker WHERE SALARY=(SELECT max(SALARY) from Worker);')

SELECT "w"."FIRST_NAME", "w"."SALARY"
FROM "Worker" "w"
WHERE "w"."SALARY" = (
    SELECT MAX("w-2"."SALARY")
    FROM "Worker" "w-2"
    )

w.FIRST_NAME|w.SALARY
------------+--------
Amitabh     |500000  
Vivek       |500000

	FIRST_NAME	SALARY
0	Amitabh	500000
1	Vivek	500000

	FIRST_NAME	SALARY
0	Amitabh	500000
1	Vivek	500000

Output for SQL_queries_exist=False sample

GET NEW CONNECTION
RELEASE CONNECTION
GET CONNECTION FROM THE LOCAL POOL
PRAGMA foreign_keys = false
BEGIN IMMEDIATE TRANSACTION
CREATE TABLE "Worker" (
  "WORKER_ID" INTEGER NOT NULL PRIMARY KEY,
  "FIRST_NAME" TEXT NOT NULL,
  "LAST_NAME" TEXT NOT NULL,
  "SALARY" INTEGER,
  "JOINING_DATE" DATETIME,
  "DEPARTMENT" TEXT NOT NULL
)

CREATE TABLE "Bonus" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "WORKER_REF_ID" INTEGER NOT NULL REFERENCES "Worker" ("WORKER_ID") ON DELETE CASCADE,
  "BONUS_AMOUNT" INTEGER,
  "BONUS_DATE" DATETIME
)

CREATE INDEX "idx_bonus__worker_ref_id" ON "Bonus" ("WORKER_REF_ID")

CREATE TABLE "Title" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "WORKER_REF_ID" INTEGER NOT NULL REFERENCES "Worker" ("WORKER_ID") ON DELETE CASCADE,
  "WORKER_TITLE" TEXT NOT NULL,
  "AFFECTED_FROM" DATETIME
)

CREATE INDEX "idx_title__worker_ref_id" ON "Title" ("WORKER_REF_ID")

SELECT "Bonus"."id", "Bonus"."WORKER_REF_ID", "Bonus"."BONUS_AMOUNT", "Bonus"."BONUS_DATE"
FROM "Bonus" "Bonus"
WHERE 0 = 1

SELECT "Title"."id", "Title"."WORKER_REF_ID", "Title"."WORKER_TITLE", "Title"."AFFECTED_FROM"
FROM "Title" "Title"
WHERE 0 = 1

SELECT "Worker"."WORKER_ID", "Worker"."FIRST_NAME", "Worker"."LAST_NAME", "Worker"."SALARY", "Worker"."JOINING_DATE", "Worker"."DEPARTMENT"
FROM "Worker" "Worker"
WHERE 0 = 1

COMMIT
PRAGMA foreign_keys = true
CLOSE CONNECTION

Database representation

ER_draw(db_file)
for ent in list(db.entities.keys()):
    sql_present(ent_name=str_to_class(ent).__name__, entity_present=True)

Worker	WORKER_ID	FIRST_NAME	LAST_NAME	SALARY	JOINING_DATE	DEPARTMENT
0	1	FIRST_NAME_1	LAST_NAME_1	2	2020-07-15 02:15:23	DEPARTMENT_1
1	2	FIRST_NAME_2	LAST_NAME_2	2	2020-08-31 06:01:18	DEPARTMENT_2
2	3	FIRST_NAME_3	LAST_NAME_3	-4	2020-01-18 19:36:28	DEPARTMENT_3
3	4	FIRST_NAME_4	LAST_NAME_4	-4	2020-02-09 13:09:19	DEPARTMENT_4
4	5	FIRST_NAME_5	LAST_NAME_5	-5	2020-11-22 22:22:37	DEPARTMENT_5

Bonus	id	WORKER_REF_ID	BONUS_AMOUNT	BONUS_DATE
0	1	1	0	2020-10-26 01:04:42
1	2	2	1	2020-07-15 21:22:23
2	3	3	1	2020-05-23 20:32:32
3	4	2	-1	2020-01-04 01:25:17
4	5	4	-4	2020-11-15 08:05:55

Title	id	WORKER_REF_ID	WORKER_TITLE	AFFECTED_FROM
0	1	5	WORKER_TITLE_1	2020-04-21 02:22:52
1	2	2	WORKER_TITLE_2	2020-08-16 02:56:11
2	3	3	WORKER_TITLE_3	2020-07-24 06:42:35
3	4	5	WORKER_TITLE_4	2020-08-31 07:08:27
4	5	3	WORKER_TITLE_5	2020-02-08 20:10:48

Examples Every example uses a Pony and a raw SQLite implementation

select(raw_sql('substr(w.FIRST_NAME,1,3)') for w in Worker).without_distinct().show() #Pony
sql_present('Select substr(FIRST_NAME,1,3) from Worker;') # SQLite

GET NEW CONNECTION
SWITCH TO AUTOCOMMIT MODE
SELECT substr(w.FIRST_NAME,1,3)
FROM "Worker" "w"

raw_sql('substr(w.FIRST_NAME,1,3)')
-----------------------------------
FIR                                
FIR                                
FIR                                
FIR                                
FIR

	substr(FIRST_NAME,1,3)
0	FIR
1	FIR
2	FIR
3	FIR
4	FIR

select((w.DEPARTMENT, sum(w.SALARY)) for w in Worker).show()
sql_present('SELECT DEPARTMENT, sum(Salary) from worker group by DEPARTMENT;')

SELECT "w"."DEPARTMENT", coalesce(SUM("w"."SALARY"), 0)
FROM "Worker" "w"
GROUP BY "w"."DEPARTMENT"

w.DEPARTMENT|sum(w.SALARY)
------------+-------------
DEPARTMENT_1|2            
DEPARTMENT_2|2            
DEPARTMENT_3|-4           
DEPARTMENT_4|-4           
DEPARTMENT_5|-5

	DEPARTMENT	sum(Salary)
0	DEPARTMENT_1	2
1	DEPARTMENT_2	2
2	DEPARTMENT_3	-4
3	DEPARTMENT_4	-4
4	DEPARTMENT_5	-5

In this example we can also check out that using the outputted SQL code from the Pony execution inside sql_present yields the same result

select((w.FIRST_NAME, w.SALARY) for w in Worker if w.SALARY == max(w.SALARY for w in Worker)).without_distinct().show()

sql_present('''SELECT "w"."FIRST_NAME", "w"."SALARY"
FROM "Worker" "w"
WHERE "w"."SALARY" = (
    SELECT MAX("w-2"."SALARY")
    FROM "Worker" "w-2"
    )''')

sql_present('SELECT FIRST_NAME, SALARY from Worker WHERE SALARY=(SELECT max(SALARY) from Worker);')

SELECT "w"."FIRST_NAME", "w"."SALARY"
FROM "Worker" "w"
WHERE "w"."SALARY" = (
    SELECT MAX("w-2"."SALARY")
    FROM "Worker" "w-2"
    )

w.FIRST_NAME|w.SALARY
------------+--------
FIRST_NAME_1|2       
FIRST_NAME_2|2

	FIRST_NAME	SALARY
0	FIRST_NAME_1	2
1	FIRST_NAME_2	2

	FIRST_NAME	SALARY
0	FIRST_NAME_1	2
1	FIRST_NAME_2	2

Name		Name	Last commit message	Last commit date
Latest commit History 11 Commits
img		img
README.md		README.md
full_code.ipynb		full_code.ipynb

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

Pony-SQLite3_simplified

Output for SQL_queries_exist=True sample

Output for SQL_queries_exist=False sample

About

Releases

Packages

Languages

chris-bbrs/Pony-SQLite3_simplified

Folders and files

Latest commit

History

Repository files navigation

Pony-SQLite3_simplified

Output for SQL_queries_exist=True sample

Output for SQL_queries_exist=False sample

About

Topics

Resources

Stars

Watchers

Forks

Releases

Packages 0

Languages

Packages