Python SQLite Tutorial
This is a SQLite programming tutorial. It covers using
To use this tutorial, you must have the Python language, the SQLite database, the pysqlite language binding, and the sqlite3 command-line tool installed on your system.
To work with SQLite, you can install either sqlite3 or the SQLite Browser GUI.
$ python
Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.21.0'
In the shell, we start the Python interactive interpreter. We can see the Python version. In our case, it’s Python 3.7.0. sqlite.version is the version of pysqlite (2.6.0), the Python language binding for the SQLite database. sqlite3.sqlite_version provides the version of the SQLite database library. In our case, it’s version 3.21.0.
SQLite
SQLite is an embedded relational database engine. The documentation describes it as a self-contained, serverless, zero-configuration, and transactional SQL database engine. Today, it’s in use worldwide, with hundreds of millions of copies, making it extremely popular. Several programming languages have built-in support for SQLite, including Python and PHP.
Creating a SQLite Database
Now, we’ll create a new database using the sqlite3 command-line tool.
$ sqlite3 ydb.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite>
We provide an argument to the sqlite3 tool; ydb.db is the database name. This is the file on our disk. If it exists, it is opened. If not, it is created.
sqlite> .tables
sqlite> .exit
$ ls
ydb.db
The .tables command provides a list of the tables in the ydb.db database. There are currently no tables. The .exit command terminates the interactive session of the sqlite3 command-line tool. The ls Unix command displays the contents of the current working directory. We can see the ydb.db file. All data will be stored in this single file.
Python SQLite Version Example
In this first code example, we will retrieve the version of the SQLite database.
version.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
import sys
con = None
try:
con = sqlite.connect('ydb.db')
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
data = cur.fetchone()[0]
print(f"SQLite version: {data}")
except sqlite.Error as e:
print(f"Error {e.args[0]}")
sys.exit(1)
finally:
if con:
con.close()
In the above Python script, we connect to the ydb.db database created earlier. We execute a SQL statement that returns the version of the SQLite database.
import sqlite3 as sqlite
We import sqlite3 and give it a name.
con = None
We initialize the con variable to None. If a connection to the database cannot be established (for example, if the disk is full), the connection variable will not be defined. This will result in an error in the finally clause.
con = sqlite.connect('ydb.db')
We connect to the ydb.db database. The connect() method returns a connection object.
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
From the connection, we get the cursor object. A cursor is used to iterate through the records in the result set. We call the cursor’s execute() method and execute the SQL statement.
data = cur.fetchone()[0]
We get the data. Since we are retrieving only one record, we call the fetchone() method.
print(f"SQLite version: {data}")
We print the retrieved data to the console.
except sqlite.Error as e:
print(f"Error {e.args[0]}")
sys.exit(1)
If an exception occurs, we print an error message and exit the script with error code 1.
finally:
if con:
con.close()
In the last step, we release resources.
In the second example, we again get the version of the SQLite database. This time we use the with keyword.
version2.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
con = sqlite.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
data = cur.fetchone()[0]
print(f"SQLite version: {data}")
This script returns the current version of the SQLite database. By using the with keyword, the code is more compact.
with con:
Using the with keyword, the Python interpreter automatically releases resources. It also provides error handling.
$ ./version.py
SQLite version: 3.21.0
Python SQLite execute
We create a cars table and insert a few rows into it. We use execute().
create_table.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
con = sqlite.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")
cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)")
cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)")
cur.execute("INSERT INTO cars VALUES(5,'Bentley',350000)")
cur.execute("INSERT INTO cars VALUES(6,'Citroen',21000)")
cur.execute("INSERT INTO cars VALUES(7,'Hummer',41400)")
cur.execute("INSERT INTO cars VALUES(8,'Volkswagen',21600)")
The above script creates a cars table and inserts 8 rows into it.
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
This SQL statement creates a new cars table. The table has three columns.
cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")
These two rows insert two cars into the table. Using the with keyword, the changes are automatically committed. Otherwise, we would have to commit them manually.
sqlite> .mode column
sqlite> .headers on
We use the sqlite3 tool to verify the written data. First, we change how the data is displayed in the console. We use column mode and turn on headers.
sqlite> select * from cars;
id name price
---------- ---------- ----------
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroën 21000
7 Hummer 41400
8 Volkswagen 21600
This is the data we wrote to the cars table.
Python SQLite executemany
We will create the same table, this time using the convenient executemany() method.
create_table2.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
cars = (
(1, 'Audi', 52642),
(2, 'Mercedes', 57127),
(3, 'Skoda', 9000),
(4, 'Volvo', 29000),
(5, 'Bentley', 350000),
(6, 'Hummer', 41400),
(7, 'Volkswagen', 21600)
)
con = sqlite.connect('ydb.db')
with con:
cur = con.cursor() cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
This program will drop the cars table (if it exists) and recreate it.
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
The first SQL statement drops the cars table if it exists. The second SQL statement creates the cars table.
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
We use the convenient executemany() method to insert 8 rows into the table. The first argument to this method is a parameterized SQL statement. The second argument is the data, in the form of a tuple of tuples.
Python SQLite executescript
We provide an alternative method for creating the cars table using executescript(). We manually commit the changes and provide our own error handling.
create_table3.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
importsys
con=None
try:
con = sqlite.connect('ydb.db')
cur = con.cursor()
cur.executescript("""
DROP TABLE IF EXISTS cars;
CREATE TABLE cars(id INT, name TEXT, price INT);
INSERT INTO cars VALUES(1,'Audi',52642);
INSERT INTO cars VALUES(2,'Mercedes',57127);
INSERT INTO cars VALUES(3,'Skoda',9000);
INSERT INTO cars VALUES(4,'Volvo',29000);
INSERT INTO cars VALUES(5,'Bentley',350000);
INSERT INTO cars VALUES(6,'Citroen',21000);
INSERT INTO cars VALUES(7,'Hummer',41400);
INSERT INTO cars VALUES(8,'Volkswagen',21600);
""")
con.commit()
except sqlite.Error as e:
if con:
con.rollback()
print(f"Error {e.args[0]}")
sys.exit(1)
finally:
if con:
con.close()
In the above script, we use the executescript() method to (re)create the cars table.
cur.executescript("""
DROP TABLE IF EXISTS cars;
CREATE TABLE cars(id INT, name TEXT, price INT);
INSERT INTO cars VALUES(1,'Audi',52642);
INSERT INTO cars VALUES(2,'Mercedes',57127);
...
The
executescript() method allows us to execute the entire SQL code in one step.
con.commit()
If the with keyword is not present, the commit() method must be used to commit the changes.
except sqlite.Error as e:
if con:
con.rollback()
print(f"Error {e.args[0]}")
sys.exit(1)
When an error occurs, the changes are rolled back and an error message is displayed on the terminal.
Python SQLite lastrowid
Sometimes, we need to determine the ID of the last inserted row. In Python SQLite, we use the lastrowid attribute of the cursor object.
lastrowid.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
con = sqlite.connect(':memory:')
with con:
cur = con.cursor()
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT);")
cur.execute("INSERT INTO friends(name) VALUES ('Tom');")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca');")
cur.execute("INSERT INTO friends(name) VALUES ('Jim');")
cur.execute("INSERT INTO friends(name) VALUES ('Robert');")
last_row_id = cur.lastrowid
print(f"The last Id of the inserted row is {last_row_id}")
We create a friends table in memory. The ID is automatically incremented.
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT);")
In SQLite, INTEGER PRIMARY KEY columns automatically increment. There is also an AUTOINCREMENT keyword. When used with INTEGER PRIMARY KEY AUTOINCREMENT, a slightly different ID creation algorithm is used. </p>
<pre><code class="language-python">cur.execute("INSERT INTO friends(name) VALUES ('Tom');")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca');")
cur.execute("INSERT INTO friends(name) VALUES ('Jim');")
cur.execute("INSERT INTO friends(name) VALUES ('Robert');")
When using auto-increment, we must explicitly state the column name; the auto-increment column name is omitted. These four statements insert four rows into the friends table.
last_row_id = cur.lastrowid
Use lastrowid to obtain the last inserted row ID.
$ ./lastrowid.py
The last ID of the inserted row is 4
Python SQLite: Retrieving Data Using fetchall
fetchall() retrieves all (or all remaining) rows of a query result set and returns a list of tuples.
fetch_all.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
con = sqlite.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(f"{row[0]} {row[1]} {row[2]}")
In this example, we retrieve all the data from the cars table.
cur.execute("SELECT * FROM cars")
This SQL statement selects all the data from the cars table.
rows = cur.fetchall()
fetchall() method gets all the records. It returns a result set. Technically, it is a tuple of tuples. Each inner tuple represents a row in the table.
for row in rows:
print(f"{row[0]} {row[1]} {row[2]}")
We print the data to the console row by row.
$ ./fetch_all.py
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
Python SQLite fetchone
fetchone() Returns the next row of a query result set, a single tuple, or None if no more data is available.
fetch_one.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
con = sqlite.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("SELECT * FROM cars")
while True:
row = cur.fetchone()
if row == None:
break
print(f"{row[0]} {row[1]} {row[2]}")
In this script, we connect to the database and read the rows of the cars table one by one.
while True:
We access the data from the while loop. The loop terminates when we read the last row.
row = cur.fetchone()
if row == None:
break
fetchone() method returns the next row of the table. If there is no data left, it returns None. In this case, we break out of the loop.
print(f"{row[0]} {row[1]} {row[2]}")
The data is returned as a tuple. Here, we select records from the tuple. The first is the ID, the second is the car name, and the third is the car’s price.
$ ./fetch_one.py
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
Python SQLite Dictionary Cursor
Default cursors return data as tuples of tuples. When using a dictionary cursor, the data is sent as a Python dictionary. This allows us to reference the data by column name.
dictionary_cursor.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
con = sqlite.connect('ydb.db')
with con:
con.row_factory = sqlite.Row
cur = con.cursor()
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(f"{row['id']} {row['name']} {row['price']}")
In this example, we use a dictionary cursor to print the contents of the cars table.
con.row_factory = sqlite.Row
We select a dictionary cursor. Now, we can access records by column name.
for row in rows:
print(f"{row['id']} {row['name']} {row['price']}")
Accessing data by column name.
Python SQLite Parameterized Statements
Now, we’ll focus on parameterized queries. When using parameterized queries, we use placeholders instead of writing values directly into the statement. Parameterized queries improve security and performance.
Python The sqlite3 module supports two types of placeholders: question marks and named placeholders.
Parameterized Statements with Question Marks
In the first example, we use the question mark syntax.
parameterized_query.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
uId = 1
uPrice = 62300
con = sqlite.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("UPDATE cars SET price=? WHERE id=?", (uPrice, uId))
print(f"Number of rows updated: {cur.rowcount}")
We updated the price of a car. In this code example, we use a question mark placeholder.
cur.execute("UPDATE cars SET price=? WHERE id=?", (uPrice, uId))
The question mark ? is a placeholder for a value. The value will be added to the placeholder.
print(f"Number of rows updated: {cur.rowcount}")
rowcount property returns the number of rows updated. In our case, one row is updated.
Parameterized Statements with Named Placeholders
The second example uses parameterized statements with named placeholders.
named_placeholders.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
uId = 4
con = sqlite.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("SELECT name, price FROM cars WHERE Id=:Id", {"Id": uId})
row = cur.fetchone()
print(f"{row[0]}, {row[1]}")
We use named placeholders to select the name and price of the car.
cur.execute("SELECT name, price FROM cars WHERE Id=:Id", {"Id": uId})
Named placeholders begin with a colon.
Python SQLite Insert Image
In this section, we insert an image into a SQLite database. Note that some people are against putting images in a database. Here, we only show how to do it. We do not discuss the technical issues of whether to save the image in the database.
sqlite> CREATE TABLE images(id INTEGER PRIMARY KEY, data BLOB);
For this example, we create a new table called Images. For images, we use the BLOB data type, which stands for Binary Large Object.
insert_image.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
import sys
def readImage():
fin = None
try:
fin = open("sid.jpg", "rb")
img = fin.read()
return img
except IOError as e:
print(e)
sys.exit(1)
finally:
if fin:
fin.close()
con = None
try:
con = sqlite.connect('ydb.db')
cur = con.cursor()
data = readImage()
binary = sqlite.Binary(data)
cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) )
con.commit()
except sqlite.Error as e:
if con:
con.rollback()
print(e)
sys.exit(1)
finally:
if con:
con.close()
In this script, we read an image from the current working directory and write it to the images table in the SQLite ydb.db database.
try:
fin = open("sid.png", "rb")
img = fin.read()
return img
We read binary data from the file system. We have a JPG image named sid.png.
binary = sqlite.Binary(data)
Encode the data using a SQLite Binary object.
cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) )
This SQL statement inserts an image into the database.
Reading Images from Python SQLite
In this section, we perform the reverse operation: we read an image from a database table.
read_image.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
import sys
def writeImage(data):
fout = None
try:
fout = open('sid2.png','wb')
fout.write(data)
except IOError as e:
print(e)
sys.exit(1)
finally:
if fout:
fout.close()
con = None
try:
con = sqlite.connect('ydb.db')
cur = con.cursor()
cur.execute("SELECT data FROM images LIMIT 1")
data = cur.fetchone()[0]
writeImage(data)
except sqlite.Error as e:
print(e)
sys.exit(1)
finally:
if con:
con.close()
We read the image data from the Images table and write it to another file woman2.jpg.
try:
fout = open('sid2.png','wb')
fout.write(data)
We open a binary file in write mode. The data from the database is written to the file.
cur.execute("SELECT data FROM images LIMIT 1")
data = cur.fetchone()[0]
These two lines select and fetch data from the images table. We fetch the binary data from the first row.
Python SQLite Metadata
Metadata is information about the data in the database. Metadata in SQLite contains information about the tables and columns in which we store data. The number of rows affected by an SQL statement is metadata. The number of rows and columns returned in the result set also belongs to metadata.
Metadata in SQLite can be obtained using the PRAGMA command. SQLite objects may have attributes, which are metadata. Finally, we can also query SQLite by The system sqlite_master table is used to obtain specific metadata.
column_names.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
con = sqlite.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA table_info(cars)')
data = cur.fetchall()
for d in data:
print(f"{d[0]} {d[1]} {d[2]}")
In this example, we issue the PRAGMA table_info(tableName) command to obtain some metadata information about the cars table.
cur.execute('PRAGMA table_info(cars)')
PRAGMA table_info(tableName) returns a row for each column in the cars table. The columns in the result set include the column number, column name, data type, whether the column can be NULL, and the default value of the column.
for d in data:
print(f"{d[0]} {d[1]} {d[2]}")
Based on the information provided, we print the column number, column name, and column data type.
$ ./column_names.py
0 id INT
1 name TEXT
2 price INT
In the following example, we print all rows and their column names in the cars table.
column_names2.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
con = sqlite.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute('SELECT * FROM cars')
col_names = [cn[0] for cn in cur.description]
rows = cur.fetchall()
print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}")
for row in rows:
print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}")
We print the contents of the cars table to the console. Now, we also include the names of the columns. The records are aligned with the column names.
col_names = [cn[0] for cn in cur.description]
We get the column names from the description attribute of the cursor object.
print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}")
This line prints the names of the three columns of the cars table.
for row in rows:
print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}")
We use a for loop to print the rows. The data is aligned with the column names.
$ ./column_names2.py
id name price
1 Audi 62300
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Hummer 41400
7 Volkswagen 21600
In this final example related to metadata, we will list all the tables in the ydb.db database.
list_tables.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
con = sqlite.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
rows = cur.fetchall()
for row in rows:
print(row[0])
This code example prints all available tables in the current database to the terminal.
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
The table names are stored in the system sqlite_master table.
$ ./list_tables.py
cars
images
These are the tables on our system.
Python SQLite Data Export
We can dump the data in SQL format to create a simple backup of our database tables.
export_table.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
cars = (
(1, 'Audi', 52643),
(2, 'Mercedes', 57642),
(3, 'Skoda', 9000),
(4, 'Volvo', 29000),
(5, 'Bentley', 350000),
(6, 'Hummer', 41400),
(7, 'Volkswagen', 21600)
)
def writeData(data):
f = open('cars.sql', 'w')
with f:
f.write(data)
con = sqlite.connect(':memory:')
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
cur.execute("DELETE FROM cars WHERE price < 30000")
data = 'n'.join(con.iterdump()) writeData(data)
In the example above, we recreate the cars table in memory. We delete some rows from the table and dump the current state of the table into the cars.sql file. This file can be used as a current backup of the table.
def writeData(data):
f = open('cars.sql', 'w')
with f:
f.write(data)
The data from the table is being written to the file.
con = sqlite.connect(':memory:')
We create a temporary table in memory.
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
cur.execute("DELETE FROM cars WHERE price < 30000")
These rows create the cars table, insert values, and delete rows where price is less than 30000 units.
data = 'n'.join(con.iterdump())
con.iterdump() returns an iterator that dumps the database in SQL text format. The built-in join() function takes an iterator and joins all the strings in the iterator together, separated by newlines. This data is written to the cars.sql file in the writeData() function.
$ cat cars.sql
BEGIN TRANSACTION;
CREATE TABLE cars(id INT, name TEXT, price INT);
INSERT INTO "cars" VALUES(1,'Audi',52643);
INSERT INTO "cars" VALUES(2,'Mercedes',57642);
INSERT INTO "cars" VALUES(5,'Bentley',350000);
INSERT INTO "cars" VALUES(6,'Hummer',41400);
COMMIT;
Discard the contents of the in-memory cars table.
Python SQLite Import Data
Now, we’ll perform the reverse operation. We’ll import the dumped table back into memory.
import_table.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
def readData():
f = open('cars.sql', 'r')
with f:
data = f.read()
return data
con = sqlite.connect(':memory:')
with con:
cur = con.cursor()
sql = readData()
cur.executescript(sql)
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(row)
In this script, we read the contents of the cars.sql file and execute it. This will recreate the dumped table.
def readData():
f = open('cars.sql', 'r')
with f:
data = f.read()
return data
Inside the readData() method, we read the contents of the cars.sql table.
cur.executescript(sql)
We call the executescript() method to start the SQL script.
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(row)
We verify the data.
$ ./import_table.py
(1, u'Audi', 52643)
(2, u'Mercedes', 57642)
(5, u'Bentley', 350000)
(6, u'Hummer', 41400)
The output shows that we have successfully recreated the saved car table.
Python SQLite transaction
Transactions are the basic unit of database operations for data in one or more databases. The impact of all SQL statements in a transaction can be submitted to the database or rolled back.
By default, the Python sqlite3 module implicitly issues the BEGIN statement before the data modification language (DML) statement (i.e. INSERT / UPDATE /DELETE / REPLACE).
sqlite3 is used to implicitly submit an open transaction before a DDL statement. This is no longer the case.
Manual transactions start with the BEGIN TRANSACTION statement and end with the COMMIT or ROLLBACK statement.
SQLite supports three non-standard transaction levels: DEFERRED, IMMEDIATE and EXCLUSIVE. The Python SQLite module also supports automatic submission mode in which all changes to the table take effect immediately.
no_commit.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlite3 as sqlite
import sys
con = None
try:
con = sqlite.connect('ydb.db')
cur = con.cursor()
cur.execute("DROP TABLE