Python and SQL for Manufacturing: Hands-On Training¶
Demo Notebook 1: Working with a Database in Python¶
Author: Prakash Ukhalkar
Role: Assistant Professor (MCA) | Researcher in Data Science and Machine Learning
Notebook Scope: Learn how to connect to a SQLite database, create tables, and perform INSERT, UPDATE, and DELETE operations using the classic EMP and DEPT demo tables.
We will use two of the most famous demo tables in SQL history: EMP and DEPT.
By the end of this notebook you will be able to:
- Connect to a SQLite database
- Create tables
- Insert rows
- Update rows
- Delete rows
Step 1 — Import sqlite3 and connect to a database¶
sqlite3 comes built into Python — nothing to install.
Calling sqlite3.connect('company.db') will create the file company.db in the current folder if it doesn't exist yet.
import sqlite3
conn = sqlite3.connect('company.db')
cursor = conn.cursor()
print('Connected to company.db')
Connected to company.db
Step 2 — Create the DEPT table¶
The DEPT table stores department information.
| Column | Meaning |
|---|---|
| deptno | Department number (primary key) |
| dname | Department name |
| loc | Location / city |
cursor.execute('''
CREATE TABLE IF NOT EXISTS dept (
deptno INTEGER PRIMARY KEY,
dname TEXT,
loc TEXT
)
''')
conn.commit()
print('DEPT table created')
Step 3 — Create the EMP table¶
The EMP table stores employee information.
| Column | Meaning |
|---|---|
| empno | Employee number (primary key) |
| ename | Employee name |
| job | Job title |
| mgr | Manager's employee number |
| hiredate | Date hired |
| sal | Salary |
| comm | Commission (salespeople only) |
| deptno | Which department they belong to |
cursor.execute('''
CREATE TABLE IF NOT EXISTS emp (
empno INTEGER PRIMARY KEY,
ename TEXT,
job TEXT,
mgr INTEGER,
hiredate TEXT,
sal REAL,
comm REAL,
deptno INTEGER,
FOREIGN KEY (deptno) REFERENCES dept(deptno)
)
''')
conn.commit()
print('EMP table created')
Step 4 — Insert rows into DEPT¶
We insert 4 departments using executemany, which lets us pass a list of rows all at once.
departments = [
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON'),
]
cursor.executemany('INSERT OR IGNORE INTO dept (deptno, dname, loc) VALUES (?, ?, ?)', departments)
conn.commit()
print(f'{cursor.rowcount} row(s) inserted into DEPT')
Step 5 — Insert rows into EMP¶
Now we add 14 classic employees.
employees = [
(7839, 'KING', 'PRESIDENT', None, '1981-11-17', 5000, None, 10),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, None, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, None, 10),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, None, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, None, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, None, 20),
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, None, 20),
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, None, 20),
(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, None, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, None, 10),
]
cursor.executemany(
'INSERT OR IGNORE INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
employees
)
conn.commit()
print(f'{cursor.rowcount} row(s) inserted into EMP')
Step 6 — View what we just inserted¶
Let's quickly read back both tables to confirm the data is there.
Option 1 — iterate (one row at a time)¶
for row in cursor.execute('SELECT * FROM dept'):
print(row)
Option 2 — fetchall() returns all rows as a list¶
rows = cursor.execute('SELECT * FROM dept').fetchall()
print(rows)
Option 3 — fetchone() returns just the next single row¶
row = cursor.execute('SELECT * FROM dept').fetchone()
print(row)
print('--- DEPT ---')
for row in cursor.execute('SELECT * FROM dept'):
print(row)
print('--- EMP ---')
for row in cursor.execute('SELECT empno, ename, job, sal, deptno FROM emp'):
print(row)
Step 7 — Update a row¶
SMITH currently earns 800. Let's give him a raise to 900.
The
?placeholder keeps user-supplied values separate from the SQL statement — this is the safe way to pass values.
cursor.execute(
'UPDATE emp SET sal = ? WHERE ename = ?', (900, 'SMITH')
)
conn.commit()
print(f'{cursor.rowcount} row(s) updated')
# Verify the change
row = cursor.execute('SELECT ename, sal FROM emp WHERE ename = ?', ('SMITH',)).fetchone()
print(f"{row[0]}'s new salary: {row[1]}")
Step 8 — Delete a row¶
Let's remove the OPERATIONS department (dept 40) since no employees are assigned there.
cursor.execute('DELETE FROM dept WHERE deptno = ?', (40,))
conn.commit()
print(f'{cursor.rowcount} row(s) deleted')
# Confirm dept 40 is gone
print('Remaining departments:')
for row in cursor.execute('SELECT * FROM dept'):
print(row)
Step 9 — Close the connection¶
Always close the connection when you're done. The data is already saved to company.db.
conn.close()
print('Connection closed. Data saved to company.db')
Summary¶
| What you did | SQL statement used |
|---|---|
| Create a table | CREATE TABLE IF NOT EXISTS ... |
| Add rows | INSERT OR IGNORE INTO ... VALUES (?, ...) |
| Change a value | UPDATE ... SET ... WHERE ... |
| Remove a row | DELETE FROM ... WHERE ... |
Next: Open day2_querying.ipynb to learn how to query and filter data.
About This Material¶
| Author | Prakash Ukhalkar |
| Role | Assistant Professor (MCA) · Researcher in Data Science and Machine Learning |
| GitHub | @prakash-ukhalkar |
| Repository | mfg-python-sql-training |
| License | MIT |
Provided for educational use in manufacturing analytics training programmes.
For questions, corrections, or contributions, open an issue or pull request on GitHub.