Python SQLite Installation Process

The sqlite3 module, designed by Gerhard Haring, can be used to combine SQLite3 with Python. It offers a SQL interface that complies with PEP 249's DB-API 2.0 definition. This module is included by default with Python versions 2.5.x and up, so you don't have to install it manually.

To utilize the sqlite3 module, you must first construct a connection object to represent the database. Next, you have the option to make a cursor object to aid in the execution of all SQL commands.
Video : https://youtu.be/Q8r6tnH74iI

The following are key sqlite3 module procedures that will allow you to work with SQLite databases from your Python program. If you want a more advanced application, check out the official documentation of the Python sqlite3 module.

1. connect sqlite3 (database [,timeout, other optional arguments])

This API establishes a connection to an SQLite database file. You can use ":memory:" to connect to a database that is stored in RAM rather than on a disc.

When many connections access a database and one of the processes alters it, the SQLite database is locked until the transaction is committed. The timeout argument determines how long the connection should wait for the lock to release before throwing an error. The timeout parameter's default value is 5.0. (five seconds).

This method will create a database if the supplied database name does not exist. If you wish to build a database somewhere other than the current directory, you may also supply a filename with the needed location.

2. connection.cursor([cursorClass])

This procedure produces a cursor that you'll use throughout your Python database programming. CursorClass is the only optional parameter for this function. This must be a custom cursor class extending sqlite3. Cursor, if one is available.

3. cursor.execute(sql [, optional parameters])

An SQL statement is executed by this method. It's possible to parameterize the SQL query (i. e. placeholders instead of SQL literals). There are two types of placeholders supported by the sqlite3 module: question marks and named placeholders (named style).

For example − cursor.execute("insert into people values (?, ?)", (who, age))

4. connection.execute(sql [, optional parameters])

This procedure is a shortcut for the cursor object's above execute function, and it constructs an interim cursor object by calling the cursor method, then calls the cursor's execute method with the provided parameters.

5. cursor.executemany(sql, seq_of_parameters)

This procedure is a shortcut for the cursor object's above execute function, and it generates an interim cursor object by calling the cursor method, then calls the cursor's execute method with the specified arguments.

6. connection.executemany(sql[, parameters])

This procedure is a shortcut that uses the cursor method to build an intermediate cursor object, then calls the cursor.executemany method with the arguments provided.

7. cursor.executescript(sql_script)

This function runs a script that contains numerous SQL statements at the same time. It initially produces a COMMIT statement before running the SQL script it receives as an input. A semicolon should be used to separate all SQL statements (;).

8. connection.executescript(sql_script)

This function is a shortcut that uses the cursor method to build an intermediate cursor object, the cursor's executescript function is then called with the arguments given.

9. connection.total_changes()

Since the database connection was started, this procedure reports the total number of database rows that have been changed, added, or removed.

10. connection.commit()

The current transaction is committed with this approach. If you don't use this function, whatever you've done after the last commit() call will be invisible to other database connections.

11. connection.rollback()

This function undoes any database modifications made since the last commit call ().

12. connection.close()

The database connection is closed with this approach. It's worth noting that this doesn't always result in a commit (). Your modifications will be lost if you close your database connection without first using commit()!

13. cursor.fetchone()

This function gets the next row of a query result set and returns a single sequence, or None in case if there is no more data available.

14. cursor.fetchmany([size = cursor.arraysize])

This procedure returns a list of the next set of rows from a query result. In case of availability of no rows, it will return an empty list. The method tries to get as many rows as the size parameter specifies.

15. cursor.fetchall()

This procedure returns a list of all (remaining) rows of a query result. In case of availability of no rows, it will return an empty list.