![]() If you try to execute the “INSERT INTO” statement above you get the following error. We use “.header ON” to also display the column names as part of the SELECT SQL query. Use sqlite3 to confirm we can see the record in the table. ![]() cursor.execute("INSERT INTO flights (flight_number, origin, destination) VALUES (?, ?, ?)", ("AZ12345", "Rome", "Paris")) You can insert a single record or multiple records into a table.īelow you can see how to insert a single record into the table we have created. To insert data into an SQLite table you use cursor.execute() and pass to it an “INSERT INTO” SQL statement. Now that we have a table in the database, the next step is to insert data into the table. ![]() How to Insert A Single Record Into an SQLite Database With Python cursor.execute('''Īfter adding “IF NOT EXISTS” to the “CREATE TABLE” SQL statement, execute the Python code again and confirm that the error we have seen before doesn’t occur again. To avoid this error you can update the “CREATE TABLE” SQL statement as shown below. The error “table already exists” is caused by the fact that we are trying to recreate a table that is already present in the database. Sqlite3.OperationalError: table flights already exists When you execute the code you will see the following error: Traceback (most recent call last): Let’s try to execute again the Python code we have written so far. To see the tables in the database use the command “.tables”. The command to access the database is “sqlite3 ”. To explore the structure of an SQLite database you can use the sqlite3 client. mit()Īfter executing the CREATE TABLE statement and the commit, we want to verify that the table exists in the database. The sqlite Python module provides the function commit() that is needed to commit pending transactions to the database. cursor.execute('''Īs you can see we are setting the flight_number as the primary key of the table. Assume we are creating a Python application to book flights and we need a table called flights. Pass a “CREATE TABLE” SQL statement to cursor.execute(). Let’s run the first SQL command against our SQLite database. You can see that the cursor object provides the execute() method which is the one we will use to execute an SQL statement to create the first table in our database. Using Python’s dir() function let’s see what are the methods of the cursor object. You can create the cursor once you have a connection object using the cursor() factory. To execute SQL statements with SQLite and retrieve the results of the statements using Python you have to create a cursor object. The database we created is empty so we will create a table in that database. What’s next? How to Create a Table in SQLite Using Python rw-r-r- 1 codefather codefather 0 Mar 18 09:52 flight_booking.db When we execute the code to connect to the SQLite database, Python automatically creates the database file in the local directory if it doesn’t already exist.įrom the shell below you can see that an empty SQLite database file called flight_booking.db has been created. So far we haven’t created a database file, so what happens if we execute the following Python statement? conn = nnect('flight_booking.db') The filename of the database we will use in this example is flight_booking.db. You have to pass the filename of the SQLite database to the connect() function. To read data from an SQLite database you have to connect to it using the connect() function of the sqlite3 module. This module provides an interface for interacting with SQLite databases that is compliant with the Database API Specification 2.0. The first step to connect to an SQLite database in Python is to import the module sqlite3 which is part of Python since version 2.5 so you do not need to install it if you are using Python 3 (and you should). Let’s learn how to use SQLite with Python! How to Connect to an SQLite3 Database Using Python We will go through each one of the steps mentioned above so you can use this knowledge in your Python applications. Remember to commit() your changes and to close() your database connection once you are done with it. This allows you to execute all the CRUD operations you need. To interact with an SQLite database in Python you have to connect to the database, create a cursor and use the execute() function on the cursor. How can you use a database in Python? And specifically an SQLite database? In this Python tutorial, we will learn how to connect to an SQLite database and how to perform CRUD (Create Read Update Delete) operations using Python.
0 Comments
Leave a Reply. |