Documentation Index
Fetch the complete documentation index at: https://mintlify.com/Adarsh275/PetrolPump-Management-System/llms.txt
Use this file to discover all available pages before exploring further.
database.py is the single module that all UI components import for database interaction. It establishes the MySQL connection at module load time and exposes one function per CRUD operation per entity. Every other module in the project — create.py, read.py, update.py, delete.py, and app.py — imports from this file. This page documents every function signature, its SQL statement, and what it returns.
All SQL in
get_all_info_* and delete_data_* functions uses Python string formatting (format()) rather than parameterized queries — this means they are vulnerable to SQL injection if exposed to untrusted input. Only the add_* and edit_* functions use the safe %s placeholder syntax supported by mysql.connector.Connection Setup
The module connects to MySQL usingmysql.connector at import time and exposes the cursor c and connection mydb as module-level globals. Every function in database.py uses these globals directly.
database.py
create_table()
create_table() is an idempotent DDL helper that creates all six primary tables if they do not already exist. It is called once at application startup inside main() in app.py. It accepts no parameters and returns no value.
The six tables created are: Petrolpump, Owners, Employee, Customer, Invoice, and Tanker. Each CREATE TABLE statement uses the IF NOT EXISTS guard so re-running the app against an already-initialised database is safe.
INSERT Functions
Eachadd_* function inserts a single record into its corresponding table, calls mydb.commit(), and returns None. All six functions use parameterized %s placeholders — the values are passed as a tuple, never interpolated into the SQL string directly.
add_Petrolpump_data()
add_Petrolpump_data()
Inserts one row into the Returns:
Petrolpump table.database.py
Unique primary key for the pump (e.g.
"HPC805103"). Maximum 10 characters.Trading name of the petrol pump (e.g.
"Sumaraj Petroleum"). Maximum 50 characters.Parent oil company (e.g.
"Hindustan Petroleum Corporation"). Maximum 30 characters. Nullable.Four-digit year the pump opened (e.g.
2016). Nullable.Indian state where the pump is located (e.g.
"Bihar"). Maximum 30 characters. Nullable.City of operation (e.g.
"Hisua"). Maximum 40 characters.Noneadd_Owners_data()
add_Owners_data()
Inserts one row into the Returns:
Owners table.database.py
Full name of the owner; also the primary key (e.g.
"Pawan Kumar"). Maximum 20 characters.10-digit mobile number stored as a fixed-length
char(10) (e.g. "9431073500").Date of birth in
YYYY-MM-DD format (e.g. "1971-01-03"). Nullable.Single character —
"M" or "F". Nullable.Full postal address (e.g.
"Friends colony more, Patna, Bihar"). Maximum 255 characters. Nullable.Ownership stake as a whole-number percentage (e.g.
35).Noneadd_Employee_data()
add_Employee_data()
Inserts one row into the Returns:
Employee table.database.py
Unique primary key (e.g.
"MANG957"). Maximum 10 characters.Full name of the employee (e.g.
"Aman Kumar"). Maximum 30 characters.Single character —
"M" or "F". Nullable.Job role (e.g.
"MANAGER", "NOZZEL PERSON", "COOKING"). Maximum 10 characters. Nullable.Date of birth in
YYYY-MM-DD format. Nullable.Monthly salary in Indian Rupees (e.g.
65000). Nullable.Residential address. Maximum 255 characters.
Email address (e.g.
"aman@outlook.com"). Maximum 100 characters.Foreign key referencing
Petrolpump.Registration_No — the pump this employee works at. Nullable.Self-referencing foreign key pointing to the
Employee_ID of this employee’s manager. Nullable (senior managers point to themselves in sample data).Noneadd_Customer_data()
add_Customer_data()
Inserts one row into the Returns:
Customer table.database.py
Unique primary key (e.g.
"SFG252"). Maximum 10 characters.Customer’s full name (e.g.
"Akash"). Maximum 30 characters.10-digit contact number stored as
char(10). Nullable.Email address. Maximum 100 characters. Nullable.
Single character —
"M" or "F". Nullable.Customer’s city (e.g.
"Bihar"). Maximum 50 characters. Nullable.Customer’s age in years (e.g.
27). Maximum 3 digits. Nullable.Noneadd_Invoice_data()
add_Invoice_data()
Inserts one row into the Returns:
Invoice table.database.py
Unique primary key (e.g.
"XC34"). Maximum 10 characters.Transaction date in
YYYY-MM-DD format (e.g. "2022-11-20").Payment method (e.g.
"Cash", "UPI", "Credit Card", "Debit Card"). Maximum 20 characters.Volume of fuel dispensed in litres (e.g.
7.0). Nullable.Name of the fuel product (e.g.
"PetrolE10", "Diesel", "Gasoline91"). Maximum 15 characters. Nullable.Percentage discount applied (e.g.
10). Nullable.Final billed amount in Indian Rupees after discount (e.g.
640.83).Foreign key referencing
Customer.Customer_Code. Nullable.Noneadd_Tanker_data()
add_Tanker_data()
Inserts one row into the Returns:
Tanker table.database.py
Unique primary key (e.g.
"BR6872"). Maximum 10 characters.Total tank capacity in litres (e.g.
5000.0). Nullable.Storage pressure in PSI or relevant unit (e.g.
550.0). Nullable.Internal fuel product identifier (e.g.
"A1234"). Maximum 10 characters.Current volume of fuel held in the tanker (e.g.
513.5). Nullable.Human-readable fuel name (e.g.
"PetrolE10", "Diesel", "CNG"). Maximum 20 characters. Nullable.Price per litre in Indian Rupees (e.g.
101.72).Foreign key referencing
Petrolpump.Registration_No. Nullable.NoneSELECT (View All) Functions
Eachview_all_* function issues a SELECT * FROM <table> query and returns every row as a list of tuples. These functions take no parameters. The UI layer wraps the result in a Pandas DataFrame to render it in Streamlit.
| Function | SQL Executed | Returns |
|---|---|---|
view_all_Petrolpump_data() | SELECT * FROM Petrolpump | List of tuples |
view_all_Owners_data() | SELECT * FROM Owners | List of tuples |
view_all_Employee_data() | SELECT * FROM Employee | List of tuples |
view_all_Customer_data() | SELECT * FROM Customer | List of tuples |
view_all_Invoice_data() | SELECT * FROM Invoice | List of tuples |
view_all_Tanker_data() | SELECT * FROM Tanker | List of tuples |
SELECT (View IDs Only) Functions
These functions fetch only the primary key column from each table. They are used to populate selectbox dropdowns in the Update and Remove views, so the user can pick an existing record by its identifier before acting on it. Each function takes no parameters and returns a list of single-element tuples.| Function | SQL Executed | Returned Column |
|---|---|---|
view_only_Registration_No() | SELECT Registration_No FROM Petrolpump | Registration_No |
view_only_Owner_Name() | SELECT Owner_Name FROM Owners | Owner_Name |
view_only_Employee_ID() | SELECT Employee_ID FROM Employee | Employee_ID |
view_only_Customer_Code() | SELECT Customer_Code FROM Customer | Customer_Code |
view_only_Invoice_No() | SELECT Invoice_No FROM Invoice | Invoice_No |
view_only_Tanker_ID() | SELECT Tanker_ID FROM Tanker | Tanker_ID |
SELECT (Get Single Record) Functions
Eachget_all_info_* function looks up the full row for a specific primary key value. The PK is embedded into the SQL string using Python’s str.format() method — see the security note at the top of this page.
| Function | Parameter | SQL Executed |
|---|---|---|
get_all_info_Petrolpump(selected_Petrolpump) | Registration_No value | SELECT * FROM Petrolpump WHERE Registration_No="..." |
get_all_info_Owners(selected_Owners) | Owner_Name value | SELECT * FROM Owners WHERE Owner_Name="..." |
get_all_info_Employee(selected_Employee) | Employee_ID value | SELECT * FROM Employee WHERE Employee_ID="..." |
get_all_info_Customer(selected_Customer) | Customer_Code value | SELECT * FROM Customer WHERE Customer_Code="..." |
get_all_info_Invoice(selected_Invoice) | Invoice_No value | SELECT * FROM Invoice WHERE Invoice_No="..." |
get_all_info_Tanker(selected_Tanker) | Tanker_ID value | SELECT * FROM Tanker WHERE Tanker_ID="..." |
UPDATE Functions
Eachedit_* function issues a parameterized UPDATE statement using %s placeholders, commits the transaction, then immediately calls the corresponding view_all_* function and returns its result. This pattern gives the UI a fresh snapshot of the table after every write.
edit_Petrolpump_data()
edit_Petrolpump_data()
Updates all editable fields for a pump identified by Returns: Result of
Registration_No.database.py
New trading name.
New parent oil company name.
Updated opening year.
Updated state.
Updated city.
Primary key identifying the record to update.
view_all_Petrolpump_data() — full updated table as a list of tuples.edit_Owners_data()
edit_Owners_data()
Updates all editable fields for an owner identified by Returns: Result of
Owner_Name.database.py
Updated 10-digit contact number.
Updated date of birth (
YYYY-MM-DD).Updated gender (
"M" or "F").Updated postal address.
Updated ownership stake percentage.
Primary key identifying the owner to update.
view_all_Owners_data() — full updated table as a list of tuples.edit_Employee_data()
edit_Employee_data()
Updates all editable fields for an employee identified by Returns: Result of
Employee_ID.database.py
Updated full name.
Updated gender (
"M" or "F").Updated job designation.
Updated date of birth (
YYYY-MM-DD).Updated monthly salary in INR.
Updated residential address.
Updated email address.
Updated pump assignment (FK to
Petrolpump.Registration_No).Updated manager reference (FK to
Employee.Employee_ID).Primary key identifying the employee to update.
view_all_Employee_data() — full updated table as a list of tuples.edit_Customer_data()
edit_Customer_data()
Updates all editable fields for a customer identified by Returns: Result of
Customer_Code.database.py
Updated customer name.
Updated 10-digit phone number.
Updated email address.
Updated gender (
"M" or "F").Updated city.
Updated age in years.
Primary key identifying the customer to update.
view_all_Customer_data() — full updated table as a list of tuples.edit_Invoice_data()
edit_Invoice_data()
Updates all editable fields for an invoice identified by Returns: Result of
Invoice_No.database.py
Updated transaction date (
YYYY-MM-DD).Updated payment method.
Updated fuel volume in litres.
Updated fuel product name.
Updated discount percentage.
Updated final billed amount in INR.
Updated customer foreign key.
Primary key identifying the invoice to update.
view_all_Invoice_data() — full updated table as a list of tuples.edit_Tanker_data()
edit_Tanker_data()
Updates all editable fields for a tanker identified by Returns: Result of
Tanker_ID.database.py
Updated total tank capacity in litres.
Updated storage pressure.
Updated internal fuel product identifier.
Updated current fuel volume in litres.
Updated human-readable fuel name.
Updated price per litre in INR.
Updated pump assignment foreign key.
Primary key identifying the tanker to update.
view_all_Tanker_data() — full updated table as a list of tuples.DELETE Functions
Eachdelete_data_* function builds a DELETE FROM statement using Python’s str.format() to embed the primary key value, then calls mydb.commit(). Like the get_all_info_* functions, these use string interpolation rather than parameterized queries — see the security note at the top of this page.
| Function | Parameter | SQL Executed | Returns |
|---|---|---|---|
delete_data_Petrolpump(selected_Petrolpump) | Registration_No | DELETE FROM Petrolpump WHERE Registration_No="..." | None |
delete_data_Owners(selected_Owners) | Owner_Name | DELETE FROM Owners WHERE Owner_Name="..." | None |
delete_data_Employee(selected_Employee) | Employee_ID | DELETE FROM Employee WHERE Employee_ID="..." | None |
delete_data_Customer(selected_Customer) | Customer_Code | DELETE FROM Customer WHERE Customer_Code="..." | None |
delete_data_Invoice(selected_Invoice) | Invoice_No | DELETE FROM Invoice WHERE Invoice_No="..." | None |
delete_data_Tanker(selected_Tanker) | Tanker_ID | DELETE FROM Tanker WHERE Tanker_ID="..." | None |
Special Function: TOTAL_Amount()
TOTAL_Amount(tanker_id) is the Python wrapper that calls the MySQL stored function TOTAL_AMOUNT(TID). Because mysql.connector does not support calling stored functions with callproc(), the implementation uses two consecutive c.execute() calls: the first sets a session variable with the tanker ID, and the second calls the function through that variable.
database.py
The
Tanker_ID primary key of the tanker to calculate the total fuel value for (e.g. "BR6872"). The value is embedded into the SET statement via str.format().float value representing FUEL_PRICE × FUEL_AMOUNT for the given tanker. Under normal operation the list contains exactly one tuple: [(total_amount,)].
The MySQL stored function TOTAL_AMOUNT(TID) that this calls is defined as: