Python SQLite — Drop Table
You can remove an entire table using the DROP TABLE statement. You just need to specify the name of the table you need to delete.
Syntax
Following is the syntax of the DROP TABLE statement in PostgreSQL −
Example
Assume we have created two tables with name CRICKETERS and EMPLOYEES using the following queries −
sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); sqlite>
Now if you verify the list of tables using the .tables command, you can see the above created tables in it ( list) as −
sqlite> .tables CRICKETERS EMPLOYEE sqlite>
Following statement deletes the table named Employee from the database −
sqlite> DROP table employee; sqlite>
Since you have deleted the Employee table, if you retrieve the list of tables again, you can observe only one table in it.
sqlite> .tables CRICKETERS sqlite>
If you try to delete the Employee table again, since you have already deleted it you will get an error saying “no such table” as shown below −
sqlite> DROP table employee; Error: no such table: employee sqlite>
To resolve this, you can use the IF EXISTS clause along with the DELTE statement. This removes the table if it exists else skips the DLETE operation.
sqlite> DROP table IF EXISTS employee; sqlite>
Dropping a table using Python
You can drop a table whenever you need to, using the DROP statement of MYSQL, but you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table.
Example
To drop a table from a SQLite3 database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it.
import sqlite3 #Connecting to sqlite conn = sqlite3.connect('example.db') #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists cursor.execute("DROP TABLE emp") print("Table dropped. ") #Commit your changes in the database conn.commit() #Closing the connection conn.close()
Python MySQL Drop Table
You can delete an existing table by using the «DROP TABLE» statement:
Example
Delete the table «customers»:
mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)
sql = «DROP TABLE customers»
Drop Only if Exist
If the table you want to delete is already deleted, or for any other reason does not exist, you can use the IF EXISTS keyword to avoid getting an error.
Example
Delete the table «customers» if it exists:
mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)
sql = «DROP TABLE IF EXISTS customers»
COLOR PICKER
Report Error
If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:
Thank You For Helping Us!
Your message has been sent to W3Schools.
Top Tutorials
Top References
Top Examples
Get Certified
W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookie and privacy policy.
How to delete a table in SQLAlchemy?
I want to delete a table using SQLAlchemy. Since I am testing over and over again, I want to delete the table my_users so that I can start from scratch every single time. So far I am using SQLAlchemy to execute raw SQL through the engine.execute() method:
sql = text('DROP TABLE IF EXISTS my_users;') result = engine.execute(sql)
However, I wonder if there is some standard way to do so. The only one I could find is drop_all() , but it deletes all the structure, not only one specific table:
Base.metadata.drop_all(engine) # all tables are deleted
For example, given this very basic example. It consists on a SQLite infrastructure with a single table my_users in which I add some content.
from sqlalchemy import create_engine, Column, Integer, String, text from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine('sqlite://', echo=False) Base = declarative_base() class User(Base): __tablename__ = "my_users" primary_key=True) name = Column(String) def __init__(self, name): self.name = name # Create all the tables in the database which are # defined by Base's subclasses such as User Base.metadata.create_all(engine) # Construct a sessionmaker factory object session = sessionmaker() # Bind the sessionmaker to engine session.configure(bind=engine) # Generate a session to work with s = session() # Add some content s.add(User('myname')) s.commit() # Fetch the data print(s.query(User).filter(User.name == 'myname').one().name)
For this specific case, drop_all() would work, but it won’t be convenient from the moment I start having more than one table and I want to keep the other ones.
how to drop all tables in sqlite3 using python?
i made a project which collects data from user and store it on different tables, the application has a delete function which the first option is to delete a specific table which is i already did and the second one is to delete all existing tables. How can i drop all tables inside my database? so this is my variables.
conn = sqlite3.connect('main.db') cursor = conn.execute("DROP TABLE") cursor.close()
Have you consider using SQLAlchemy to model your database? Doing this would give you easy use of your database, including a db.drop_all (and a create_all), but also easier querying.
3 Answers 3
How can i drop all tables inside my database?
SQLite allows you to drop only one table at a time. To remove multiple tables, you need to issue multiple DROP TABLE statements.
You can do it by querying all table names (https://www.sqlite.org/faq.html#q7)
Then you can use the result to delete the tables one by one
Here is the code, the function delete_all_tables does that
TABLE_PARAMETER = "" DROP_TABLE_SQL = f"DROP TABLE ;" GET_TABLES_SQL = "SELECT name FROM sqlite_schema WHERE type='table';" def delete_all_tables(con): tables = get_tables(con) delete_tables(con, tables) def get_tables(con): cur = con.cursor() cur.execute(GET_TABLES_SQL) tables = cur.fetchall() cur.close() return tables def delete_tables(con, tables): cur = con.cursor() for table, in tables: sql = DROP_TABLE_SQL.replace(TABLE_PARAMETER, table) cur.execute(sql) cur.close()