[
Deleting Data from Table¶
Let us understand how data is deleted using DELETE statement.
- Delete statement will have optional
WHEREclause where we specify the condition to filter for rows that need to be deleted. - As part of the
WHEREclause generally we will have indexed column for performance reasons.
One need to have decent database and SQL skills to be comfortable with all types of application development. Feel free to Master SQL using Postgresql as target database using this course or playlist.
In [1]:
%run 05_function_get_database_connection.ipynb
In [2]:
%load_ext sql
In [3]:
%env DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
In [4]:
%%sql
SELECT user_id,
user_password
FROM users
6 rows affected.
Out[4]:
| user_id | user_password |
|---|---|
| 2 | None |
| 3 | None |
| 4 | h9LAz7p7ub |
| 5 | oEofndp |
| 6 | ih7Y69u56 |
| 1 | None |
In [5]:
# DELETE FROM users WHERE user_password IS NULL OR user_id = 4
cursor = sms_connection.cursor()
query = ("""
DELETE FROM users
WHERE user_password IS NULL OR user_id = %s
""")
cursor.execute(query, (4,))
sms_connection.commit()
sms_connection.close()
In [6]:
%%sql
SELECT user_id, user_password FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 2 rows affected.
Out[6]:
| user_id | user_password |
|---|---|
| 5 | oEofndp |
| 6 | ih7Y69u56 |
]