Apa itu python dan sql?

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

The

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module was written by Gerhard Häring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.

This document includes four main sections:

teaches how to use the

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module.

describes the classes and functions this module defines.
details how to handle specific tasks.
provides in-depth background on transaction control.

Tutorial

In this tutorial, you will create a database of Monty Python movies using basic

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 functionality. It assumes a fundamental understanding of database concepts, including cursors and transactions.

First, we need to create a new database and open a database connection to allow

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 to work with it. Call to to create a connection to the database

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

6 in the current working directory, implicitly creating it if it does not exist:

import sqlite3
con = sqlite3.connect("tutorial.db")

The returned object

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

8 represents the connection to the on-disk database.

In order to execute SQL statements and fetch results from SQL queries, we will need to use a database cursor. Call to create the :

cur = con.cursor()

Now that we’ve got a database connection and a cursor, we can create a database table

con.commit()

1 with columns for title, release year, and review score. For simplicity, we can just use column names in the table declaration – thanks to the flexible typing feature of SQLite, specifying the data types is optional. Execute the

con.commit()

2 statement by calling :

cur.execute("CREATE TABLE movie(title, year, score)")

We can verify that the new table has been created by querying the

con.commit()

4 table built-in to SQLite, which should now contain an entry for the

con.commit()

1 table definition (see The Schema Table for details). Execute that query by calling , assign the result to

con.commit()

7, and call to fetch the resulting row:

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

We can see that the table has been created, as the query returns a containing the table’s name. If we query

con.commit()

4 for a non-existent table

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

con.commit()

8 will return

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

Now, add two rows of data supplied as SQL literals by executing an

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

4 statement, once again by calling :

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

The

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

4 statement implicitly opens a transaction, which needs to be committed before changes are saved in the database (see for details). Call on the connection object to commit the transaction:

con.commit()

We can verify that the data was inserted correctly by executing a

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

8 query. Use the now-familiar to assign the result to

con.commit()

7, and call to return all resulting rows:

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

The result is a of two

con.commit()

9s, one per row, each containing that row’s

data = [
    ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
    ("Monty Python's The Meaning of Life", 1983, 7.5),
    ("Monty Python's Life of Brian", 1979, 8.0),
]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit()  # Remember to commit the transaction after executing INSERT.

4 value.

Now, insert three more rows by calling :

data = [
    ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
    ("Monty Python's The Meaning of Life", 1983, 7.5),
    ("Monty Python's Life of Brian", 1979, 8.0),
]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit()  # Remember to commit the transaction after executing INSERT.

Notice that

data = [
    ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
    ("Monty Python's The Meaning of Life", 1983, 7.5),
    ("Monty Python's Life of Brian", 1979, 8.0),
]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit()  # Remember to commit the transaction after executing INSERT.

6 placeholders are used to bind

data = [
    ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
    ("Monty Python's The Meaning of Life", 1983, 7.5),
    ("Monty Python's Life of Brian", 1979, 8.0),
]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit()  # Remember to commit the transaction after executing INSERT.

7 to the query. Always use placeholders instead of to bind Python values to SQL statements, to avoid SQL injection attacks (see for more details).

We can verify that the new rows were inserted by executing a

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

8 query, this time iterating over the results of the query:

>>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
...     print(row)
(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")

Each row is a two-item of

>>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
...     print(row)
(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")

0, matching the columns selected in the query.

Finally, verify that the database has been written to disk by calling to close the existing connection, opening a new one, creating a new cursor, then querying the database:

cur = con.cursor()

You’ve now created an SQLite database using the

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module, inserted data and retrieved values from it in multiple ways.

Reference

Module functions

sqlite3.connect(database, timeout=5.0, detect_types=0, isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False)

Open a connection to an SQLite database.

Parameters

database () – The path to the database file to be opened. Pass

>>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
...     print(row)
(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")

3 to open a connection to a database that is in RAM instead of on disk.

timeout () – How many seconds the connection should wait before raising an exception, if the database is locked by another connection. If another connection opens a transaction to modify the database, it will be locked until that transaction is committed. Default five seconds.

detect_types () – Control whether and how data types not are looked up to be converted to Python types, using the converters registered with . Set it to any combination (using

>>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
...     print(row)
(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")

5, bitwise or) of and to enable this. Column names takes precedence over declared types if both flags are set. Types cannot be detected for generated fields (for example

>>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
...     print(row)
(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")

8), even when the detect_types parameter is set; will be returned instead. By default (

cur = con.cursor()

00), type detection is disabled.

isolation_level ( | None) – The of the connection, controlling whether and how transactions are implicitly opened. Can be
```
cur = con.cursor()
```
02 (default),
```
cur = con.cursor()
```
03 or
```
cur = con.cursor()
```
04; or
```
>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]
```
3 to disable opening transactions implicitly. See for more.
check_same_thread () – If
```
cur = con.cursor()
```
06 (default), only the creating thread may use the connection. If
```
cur = con.cursor()
```
07, the connection may be shared across multiple threads; if so, write operations should be serialized by the user to avoid data corruption.
factory () – A custom subclass of to create the connection with, if not the default class.

cached_statements () – The number of statements that

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 should internally cache for this connection, to avoid parsing overhead. By default, 128 statements.

uri () – If set to
```
cur = con.cursor()
```
06, database is interpreted as a URI with a file path and an optional query string. The scheme part must be
```
cur = con.cursor()
```
12, and the path can be relative or absolute. The query string allows passing parameters to SQLite, enabling various .

Return type

Raises an

cur = con.cursor()

13 with argument

cur = con.cursor()

14.

Raises an

cur = con.cursor()

15 with argument

cur = con.cursor()

16.

New in version 3.4: The uri parameter.

Changed in version 3.7: database can now also be a , not only a string.

New in version 3.10: The

cur = con.cursor()

15 auditing event.

sqlite3.complete_statement(statement)

Return

cur = con.cursor()

06 if the string statement appears to contain one or more complete SQL statements. No syntactic verification or parsing of any kind is performed, other than checking that there are no unclosed string literals and the statement is terminated by a semicolon.

For example:

cur = con.cursor()

This function may be useful during command-line input to determine if the entered text seems to form a complete SQL statement, or if additional input is needed before calling .

sqlite3.enable_callback_tracebacks(flag, /)

Enable or disable callback tracebacks. By default you will not get any tracebacks in user-defined functions, aggregates, converters, authorizer callbacks etc. If you want to debug them, you can call this function with flag set to

cur = con.cursor()

06. Afterwards, you will get tracebacks from callbacks on . Use

cur = con.cursor()

07 to disable the feature again.

cur = con.cursor()

sqlite3.register_adapter(type, adapter, /)

Register an adapter callable to adapt the Python type type into an SQLite type. The adapter is called with a Python object of type type as its sole argument, and must return a value of a .

sqlite3.register_converter(typename, converter, /)

Register the converter callable to convert SQLite objects of type typename into a Python object of a specific type. The converter is invoked for all SQLite values of type typename; it is passed a object and should return an object of the desired Python type. Consult the parameter detect_types of for information regarding how type detection works.

Note: typename and the name of the type in your query are matched case-insensitively.

Module constants

sqlite3.PARSE_COLNAMES

Pass this flag value to the detect_types parameter of to look up a converter function by using the type name, parsed from the query column name, as the converter dictionary key. The type name must be wrapped in square brackets (

cur = con.cursor()

27).

cur = con.cursor()

This flag may be combined with using the

>>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
...     print(row)
(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")

5 (bitwise or) operator.

sqlite3.PARSE_DECLTYPES

Pass this flag value to the detect_types parameter of to look up a converter function using the declared types for each column. The types are declared when the database table is created.

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 will look up a converter function using the first word of the declared type as the converter dictionary key. For example:

cur = con.cursor()

This flag may be combined with using the

>>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
...     print(row)
(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")

5 (bitwise or) operator.

sqlite3.SQLITE_OKsqlite3.SQLITE_DENYsqlite3.SQLITE_IGNORE

Flags that should be returned by the authorizer_callback callable passed to , to indicate whether:

Access is allowed (
```
cur = con.cursor()
```
35),
The SQL statement should be aborted with an error (
```
cur = con.cursor()
```
36)
The column should be treated as a
```
cur = con.cursor()
```
37 value (
```
cur = con.cursor()
```
38)

sqlite3.apilevel

String constant stating the supported DB-API level. Required by the DB-API. Hard-coded to

cur = con.cursor()

39.

sqlite3.paramstyle

String constant stating the type of parameter marker formatting expected by the

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module. Required by the DB-API. Hard-coded to

cur = con.cursor()

41.

Note

The

cur = con.cursor()

42 DB-API parameter style is also supported.

sqlite3.sqlite_version

Version number of the runtime SQLite library as a .

sqlite3.sqlite_version_info

Version number of the runtime SQLite library as a of .

sqlite3.threadsafety

Integer constant required by the DB-API 2.0, stating the level of thread safety the

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module supports. This attribute is set based on the default threading mode the underlying SQLite library is compiled with. The SQLite threading modes are:

Single-thread: In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.
Multi-thread: In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.
Serialized: In serialized mode, SQLite can be safely used by multiple threads with no restriction.

The mappings from SQLite threading modes to DB-API 2.0 threadsafety levels are as follows:

SQLite threading mode

DB-API 2.0 meaning

single-thread

Threads may not share the module

multi-thread

Threads may share the module, but not connections

serialized

Threads may share the module, connections and cursors

Changed in version 3.11: Set threadsafety dynamically instead of hard-coding it to

cur = con.cursor()

47.

sqlite3.version

Version number of this module as a . This is not the version of the SQLite library.

sqlite3.version_info

Version number of this module as a of . This is not the version of the SQLite library.

Connection objects

class sqlite3.Connection

Each open SQLite database is represented by a

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

7 object, which is created using . Their main purpose is creating objects, and .

See also

An SQLite database connection has the following attributes and methods:

cursor(factory=Cursor)

Create and return a object. The cursor method accepts a single optional parameter factory. If supplied, this must be a callable returning an instance of or its subclasses.

blobopen(table, column, row, /, *, readonly=False, name='main')

Open a handle to an existing BLOB.

Parameters

table () – The name of the table where the blob is located.
column () – The name of the column where the blob is located.
row () – The name of the row where the blob is located.
readonly () – Set to
```
cur = con.cursor()
```
06 if the blob should be opened without write permissions. Defaults to
```
cur = con.cursor()
```
07.
name () – The name of the database where the blob is located. Defaults to
```
cur = con.cursor()
```
59.

Raises

– When trying to open a blob in a

cur = con.cursor()

60 table.

Return type

Note

The blob size cannot be changed using the class. Use the SQL function

cur = con.cursor()

62 to create a blob with a fixed size.

New in version 3.11.

commit()

Commit any pending transaction to the database. If there is no open transaction, this method is a no-op.

rollback()

Roll back to the start of any pending transaction. If there is no open transaction, this method is a no-op.

close()

Close the database connection. Any pending transaction is not committed implicitly; make sure to before closing to avoid losing pending changes.

execute(sql, parameters=(), /)

Create a new object and call on it with the given sql and parameters. Return the new cursor object.

executemany(sql, parameters, /)

Create a new object and call on it with the given sql and parameters. Return the new cursor object.

executescript(sql_script, /)

Create a new object and call on it with the given sql_script. Return the new cursor object.

create_function(name, narg, func, *, deterministic=False)

Create or remove a user-defined SQL function.

Parameters

name () – The name of the SQL function.
narg () – The number of arguments the SQL function can accept. If
```
cur = con.cursor()
```
70, it may take any number of arguments.
func ( | None) – A callable that is called when the SQL function is invoked. The callable must return . Set to
```
>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]
```
3 to remove an existing SQL function.
deterministic () – If
```
cur = con.cursor()
```
06, the created SQL function is marked as deterministic, which allows SQLite to perform additional optimizations.

Raises

– If deterministic is used with SQLite versions older than 3.8.3.

New in version 3.8: The deterministic parameter.

Example:

cur = con.cursor()

create_aggregate(name, /, n_arg, aggregate_class)

Create or remove a user-defined SQL aggregate function.

Parameters

name () – The name of the SQL aggregate function.
n_arg () – The number of arguments the SQL aggregate function can accept. If
```
cur = con.cursor()
```
70, it may take any number of arguments.
aggregate_class ( | None) –
A class must implement the following methods:
- ```
cur = con.cursor()
```
  74: Add a row to the aggregate.
- ```
cur = con.cursor()
```
  75: Return the final result of the aggregate as .
The number of arguments that the
```
cur = con.cursor()
```
74 method must accept is controlled by n_arg.
Set to
```
>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]
```
3 to remove an existing SQL aggregate function.

Example:

cur = con.cursor()

create_window_function(name, num_params, aggregate_class, /)

Create or remove a user-defined aggregate window function.

Parameters

name () – The name of the SQL aggregate window function to create or remove.
num_params () – The number of arguments the SQL aggregate window function can accept. If
```
cur = con.cursor()
```
70, it may take any number of arguments.
aggregate_class ( | None) –
A class that must implement the following methods:
- ```
cur = con.cursor()
```
  74: Add a row to the current window.
- ```
cur = con.cursor()
```
  80: Return the current value of the aggregate.
- ```
cur = con.cursor()
```
  81: Remove a row from the current window.
- ```
cur = con.cursor()
```
  75: Return the final result of the aggregate as .
The number of arguments that the
```
cur = con.cursor()
```
74 and
```
cur = con.cursor()
```
80 methods must accept is controlled by num_params.
Set to
```
>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]
```
3 to remove an existing SQL aggregate window function.

Raises

– If used with a version of SQLite older than 3.25.0, which does not support aggregate window functions.

New in version 3.11.

Example:

cur = con.cursor()

create_collation(name, callable)

Create a collation named name using the collating function callable. callable is passed two arguments, and it should return an :

```
cur = con.cursor()
```
47 if the first is ordered higher than the second
```
cur = con.cursor()
```
70 if the first is ordered lower than the second
```
cur = con.cursor()
```
00 if they are ordered equal

The following example shows a reverse sorting collation:

cur = con.cursor()

Remove a collation function by setting callable to

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

Changed in version 3.11: The collation name can contain any Unicode character. Earlier, only ASCII characters were allowed.

interrupt()

Call this method from a different thread to abort any queries that might be executing on the connection. Aborted queries will raise an exception.

set_authorizer(authorizer_callback)

Register callable authorizer_callback to be invoked for each attempt to access a column of a table in the database. The callback should return one of , , or to signal how access to the column should be handled by the underlying SQLite library.

The first argument to the callback signifies what kind of operation is to be authorized. The second and third argument will be arguments or

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3 depending on the first argument. The 4th argument is the name of the database (“main”, “temp”, etc.) if applicable. The 5th argument is the name of the inner-most trigger or view that is responsible for the access attempt or

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3 if this access attempt is directly from input SQL code.

Please consult the SQLite documentation about the possible values for the first argument and the meaning of the second and third argument depending on the first one. All necessary constants are available in the

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module.

Passing

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3 as authorizer_callback will disable the authorizer.

Changed in version 3.11: Added support for disabling the authorizer using

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

set_progress_handler(progress_handler, n)

Register callable progress_handler to be invoked for every n instructions of the SQLite virtual machine. This is useful if you want to get called from SQLite during long-running operations, for example to update a GUI.

If you want to clear any previously installed progress handler, call the method with

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3 for progress_handler.

Returning a non-zero value from the handler function will terminate the currently executing query and cause it to raise an exception.

set_trace_callback(trace_callback)

The only argument passed to the callback is the statement (as ) that is being executed. The return value of the callback is ignored. Note that the backend does not only run statements passed to the methods. Other sources include the of the

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module and the execution of triggers defined in the current database.

Passing

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3 as trace_callback will disable the trace callback.

Note

Exceptions raised in the trace callback are not propagated. As a development and debugging aid, use to enable printing tracebacks from exceptions raised in the trace callback.

New in version 3.3.

enable_load_extension(enabled, /)

Enable the SQLite engine to load SQLite extensions from shared libraries if enabled is

cur = con.cursor()

06; else, disallow loading SQLite extensions. SQLite extensions can define new functions, aggregates or whole new virtual table implementations. One well-known extension is the fulltext-search extension distributed with SQLite.

Note

The

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module is not built with loadable extension support by default, because some platforms (notably macOS) have SQLite libraries which are compiled without this feature. To get loadable extension support, you must pass the option to configure.

Raises an

cur.execute("CREATE TABLE movie(title, year, score)")

10 with arguments

cur.execute("CREATE TABLE movie(title, year, score)")

11,

cur.execute("CREATE TABLE movie(title, year, score)")

12.

New in version 3.2.

Changed in version 3.10: Added the

cur.execute("CREATE TABLE movie(title, year, score)")

10 auditing event.

cur = con.cursor()

load_extension(path, /)

Load an SQLite extension from a shared library located at path. Enable extension loading with before calling this method.

Raises an

cur.execute("CREATE TABLE movie(title, year, score)")

15 with arguments

cur.execute("CREATE TABLE movie(title, year, score)")

11,

cur.execute("CREATE TABLE movie(title, year, score)")

17.

New in version 3.2.

Changed in version 3.10: Added the

cur.execute("CREATE TABLE movie(title, year, score)")

15 auditing event.

iterdump()

Return an to dump the database as SQL source code. Useful when saving an in-memory database for later restoration. Similar to the

cur.execute("CREATE TABLE movie(title, year, score)")

19 command in the sqlite3 shell.

Example:

cur.execute("CREATE TABLE movie(title, year, score)")

backup(target, *, pages=- 1, progress=None, name='main', sleep=0.250)

Create a backup of an SQLite database.

Works even if the database is being accessed by other clients or concurrently by the same connection.

Parameters

target () – The database connection to save the backup to.
pages () – The number of pages to copy at a time. If equal to or less than
```
cur = con.cursor()
```
00, the entire database is copied in a single step. Defaults to
```
cur = con.cursor()
```
70.
progress ( | None) – If set to a callable, it is invoked with three integer arguments for every backup iteration: the status of the last iteration, the remaining number of pages still to be copied, and the total number of pages. Defaults to
```
>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]
```
3.
name () – The name of the database to back up. Either
```
cur = con.cursor()
```
59 (the default) for the main database,
```
cur.execute("CREATE TABLE movie(title, year, score)")
```
24 for the temporary database, or the name of a custom database as attached using the
```
cur.execute("CREATE TABLE movie(title, year, score)")
```
25 SQL statement.
sleep () – The number of seconds to sleep between successive attempts to back up remaining pages.

Example 1, copy an existing database into another:

cur.execute("CREATE TABLE movie(title, year, score)")

Example 2, copy an existing database into a transient copy:

cur.execute("CREATE TABLE movie(title, year, score)")

New in version 3.7.

getlimit(category, /)

Get a connection runtime limit.

Parameters

category () – The SQLite limit category to be queried.

Return type

Raises

– If category is not recognised by the underlying SQLite library.

Example, query the maximum length of an SQL statement for

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

8 (the default is 1000000000):

cur.execute("CREATE TABLE movie(title, year, score)")

New in version 3.11.

setlimit(category, limit, /)

Set a connection runtime limit. Attempts to increase a limit above its hard upper bound are silently truncated to the hard upper bound. Regardless of whether or not the limit was changed, the prior value of the limit is returned.

Parameters

category () – The SQLite limit category to be set.
limit () – The value of the new limit. If negative, the current limit is unchanged.

Return type

Raises

– If category is not recognised by the underlying SQLite library.

Example, limit the number of attached databases to 1 for

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

8 (the default limit is 10):

cur.execute("CREATE TABLE movie(title, year, score)")

New in version 3.11.

serialize(*, name='main')

Serialize a database into a object. For an ordinary on-disk database file, the serialization is just a copy of the disk file. For an in-memory database or a “temp” database, the serialization is the same sequence of bytes which would be written to disk if that database were backed up to disk.

Parameters

name () – The database name to be serialized. Defaults to

cur = con.cursor()

59.

Return type

Note

This method is only available if the underlying SQLite library has the serialize API.

New in version 3.11.

deserialize(data, /, *, name='main')

Deserialize a database into a . This method causes the database connection to disconnect from database name, and reopen name as an in-memory database based on the serialization contained in data.

Parameters

data () – A serialized database.
name () – The database name to deserialize into. Defaults to
```
cur = con.cursor()
```
59.

Raises

– If the database connection is currently involved in a read transaction or a backup operation.
– If data does not contain a valid SQLite database.

– If is larger than

cur.execute("CREATE TABLE movie(title, year, score)")

36.

Note

This method is only available if the underlying SQLite library has the deserialize API.

New in version 3.11.

in_transaction

This read-only attribute corresponds to the low-level SQLite .

cur = con.cursor()

06 if a transaction is active (there are uncommitted changes),

cur = con.cursor()

07 otherwise.

New in version 3.2.

isolation_level

This attribute controls the performed by

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0. If set to

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3, transactions are never implicitly opened. If set to one of

cur = con.cursor()

02,

cur = con.cursor()

04, or

cur = con.cursor()

03, corresponding to the underlying , implicit is performed.

If not overridden by the isolation_level parameter of , the default is

cur.execute("CREATE TABLE movie(title, year, score)")

45, which is an alias for

cur = con.cursor()

02.

row_factory

The initial for objects created from this connection. Assigning to this attribute does not affect the

cur.execute("CREATE TABLE movie(title, year, score)")

47 of existing cursors belonging to this connection, only new ones. Is

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3 by default, meaning each row is returned as a .

See for more details.

text_factory

A callable that accepts a parameter and returns a text representation of it. The callable is invoked for SQLite values with the

cur.execute("CREATE TABLE movie(title, year, score)")

53 data type. By default, this attribute is set to . If you want to return

cur = con.cursor()

24 instead, set text_factory to

cur = con.cursor()

24.

Example:

cur.execute("CREATE TABLE movie(title, year, score)")

total_changes

Return the total number of database rows that have been modified, inserted, or deleted since the database connection was opened.

Cursor objects

A
con.commit()
0 object represents a database cursor which is used to execute SQL statements, and manage the context of a fetch operation. Cursors are created using , or by using any of the .
Cursor objects are , meaning that if you a
>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]
8 query, you can simply iterate over the cursor to fetch the resulting rows:
cur.execute("CREATE TABLE movie(title, year, score)")
6

class sqlite3.Cursor

A instance has the following attributes and methods.

execute(sql, parameters=(), /)

Execute SQL statement sql. Bind values to the statement using that map to the or parameters.

will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a . Use if you want to execute multiple SQL statements with one call.

If is not

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3, sql is an

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

cur.execute("CREATE TABLE movie(title, year, score)")

69,

cur.execute("CREATE TABLE movie(title, year, score)")

70, or

cur.execute("CREATE TABLE movie(title, year, score)")

71 statement, and there is no open transaction, a transaction is implicitly opened before executing sql.

executemany(sql, parameters, /)

Execute SQL statement sql against all parameter sequences or mappings found in the sequence parameters. It is also possible to use an yielding parameters instead of a sequence. Uses the same implicit transaction handling as .

Example:

cur.execute("CREATE TABLE movie(title, year, score)")

executescript(sql_script, /)

Execute the SQL statements in sql_script. If there is a pending transaction, an implicit

cur.execute("CREATE TABLE movie(title, year, score)")

73 statement is executed first. No other implicit transaction control is performed; any transaction control must be added to sql_script.

sql_script must be a .

Example:

cur.execute("CREATE TABLE movie(title, year, score)")

fetchone()

If is

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3, return the next row query result set as a . Else, pass it to the row factory and return its result. Return

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3 if no more data is available.

fetchmany(size=cursor.arraysize)

Return the next set of rows of a query result as a . Return an empty list if no more rows are available.

The number of rows to fetch per call is specified by the size parameter. If size is not given, determines the number of rows to be fetched. If fewer than size rows are available, as many rows as are available are returned.

Note there are performance considerations involved with the size parameter. For optimal performance, it is usually best to use the arraysize attribute. If the size parameter is used, then it is best for it to retain the same value from one call to the next.

fetchall()

Return all (remaining) rows of a query result as a . Return an empty list if no rows are available. Note that the attribute can affect the performance of this operation.

close()

Close the cursor now (rather than whenever

cur.execute("CREATE TABLE movie(title, year, score)")

84 is called).

The cursor will be unusable from this point forward; a exception will be raised if any operation is attempted with the cursor.

setinputsizes(sizes, /)

Required by the DB-API. Does nothing in

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

setoutputsize(size, column=None, /)

Required by the DB-API. Does nothing in

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

arraysize

Read/write attribute that controls the number of rows returned by . The default value is 1 which means a single row would be fetched per call.

connection

Read-only attribute that provides the SQLite database belonging to the cursor. A object created by calling will have a attribute that refers to con:

cur.execute("CREATE TABLE movie(title, year, score)")

description

Read-only attribute that provides the column names of the last query. To remain compatible with the Python DB API, it returns a 7-tuple for each column where the last six items of each tuple are

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

It is set for

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

8 statements without any matching rows as well.

lastrowid

Read-only attribute that provides the row id of the last inserted row. It is only updated after successful

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

4 or

cur.execute("CREATE TABLE movie(title, year, score)")

71 statements using the method. For other statements, after or , or if the insertion failed, the value of

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

00 is left unchanged. The initial value of

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

00 is

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

Note

Inserts into

cur = con.cursor()

60 tables are not recorded.

Changed in version 3.6: Added support for the

cur.execute("CREATE TABLE movie(title, year, score)")

71 statement.

rowcount

Read-only attribute that provides the number of modified rows for

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

cur.execute("CREATE TABLE movie(title, year, score)")

69,

cur.execute("CREATE TABLE movie(title, year, score)")

70, and

cur.execute("CREATE TABLE movie(title, year, score)")

71 statements; is

cur = con.cursor()

70 for other statements, including CTE queries. It is only updated by the and methods.

row_factory

Control how a row fetched from this

con.commit()

0 is represented. If

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3, a row is represented as a . Can be set to the included ; or a that accepts two arguments, a object and the

con.commit()

9 of row values, and returns a custom object representing an SQLite row.

Defaults to what was set to when the

con.commit()

0 was created. Assigning to this attribute does not affect of the parent connection.

See for more details.

Row objects

class sqlite3.Row

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

21 instance serves as a highly optimized for objects. It supports iteration, equality testing, , and access by column name and index.

Two

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

21 objects compare equal if they have identical column names and values.

See for more details.

keys()

Return a of column names as . Immediately after a query, it is the first member of each tuple in .

Changed in version 3.5: Added support of slicing.

Blob objects

New in version 3.11.

class sqlite3.Blob

A instance is a that can read and write data in an SQLite BLOB. Call to get the size (number of bytes) of the blob. Use indices and for direct access to the blob data.

Use the as a to ensure that the blob handle is closed after use.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

close()

Close the blob.

The blob will be unusable from this point onward. An (or subclass) exception will be raised if any further operation is attempted with the blob.

read(length=- 1, /)

Read length bytes of data from the blob at the current offset position. If the end of the blob is reached, the data up to EOF will be returned. When length is not specified, or is negative, will read until the end of the blob.

write(data, /)

Write data to the blob at the current offset. This function cannot change the blob length. Writing beyond the end of the blob will raise .

tell()

Return the current access position of the blob.

seek(offset, origin=os.SEEK_SET, /)

Set the current access position of the blob to offset. The origin argument defaults to (absolute blob positioning). Other values for origin are (seek relative to the current position) and (seek relative to the blob’s end).

PrepareProtocol objects

class sqlite3.PrepareProtocol

The PrepareProtocol type’s single purpose is to act as a PEP 246 style adaption protocol for objects that can to .

Exceptions

The exception hierarchy is defined by the DB-API 2.0 (PEP 249).

exception sqlite3.Warning

This exception is not currently raised by the

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module, but may be raised by applications using

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0, for example if a user-defined function truncates data while inserting.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

40 is a subclass of .

exception sqlite3.Error

The base class of the other exceptions in this module. Use this to catch all errors with one single statement.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

32 is a subclass of .

If the exception originated from within the SQLite library, the following two attributes are added to the exception:

sqlite_errorcode

The numeric error code from the SQLite API

New in version 3.11.

sqlite_errorname

The symbolic name of the numeric error code from the SQLite API

New in version 3.11.

exception sqlite3.InterfaceError

Exception raised for misuse of the low-level SQLite C API. In other words, if this exception is raised, it probably indicates a bug in the

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

46 is a subclass of .

exception sqlite3.DatabaseError

Exception raised for errors that are related to the database. This serves as the base exception for several types of database errors. It is only raised implicitly through the specialised subclasses.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

48 is a subclass of .

exception sqlite3.DataError

Exception raised for errors caused by problems with the processed data, like numeric values out of range, and strings which are too long.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

50 is a subclass of .

exception sqlite3.OperationalError

Exception raised for errors that are related to the database’s operation, and not necessarily under the control of the programmer. For example, the database path is not found, or a transaction could not be processed.

cur.execute("CREATE TABLE movie(title, year, score)")

01 is a subclass of .

exception sqlite3.IntegrityError

Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails. It is a subclass of .

exception sqlite3.InternalError

Exception raised when SQLite encounters an internal error. If this is raised, it may indicate that there is a problem with the runtime SQLite library.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

55 is a subclass of .

exception sqlite3.ProgrammingError

Exception raised for

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 API programming errors, for example supplying the wrong number of bindings to a query, or trying to operate on a closed .

cur.execute("CREATE TABLE movie(title, year, score)")

64 is a subclass of .

exception sqlite3.NotSupportedError

Exception raised in case a method or database API is not supported by the underlying SQLite library. For example, setting deterministic to

cur = con.cursor()

06 in , if the underlying SQLite library does not support deterministic functions.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

63 is a subclass of .

SQLite and Python types

SQLite natively supports the following types:

cur = con.cursor()

37,

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

66,

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

67,

cur.execute("CREATE TABLE movie(title, year, score)")

53,

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

69.

The following Python types can thus be sent to SQLite without any problem:

Python type

SQLite type

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

cur = con.cursor()

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

cur.execute("CREATE TABLE movie(title, year, score)")

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

This is how SQLite types are converted to Python types by default:

SQLite type

Python type

cur = con.cursor()

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

cur.execute("CREATE TABLE movie(title, year, score)")

depends on , by default

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

The type system of the

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module is extensible in two ways: you can store additional Python types in an SQLite database via , and you can let the

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module convert SQLite types to Python types via .

Default adapters and converters

There are default adapters for the date and datetime types in the datetime module. They will be sent as ISO dates/ISO timestamps to SQLite.

The default converters are registered under the name “date” for and under the name “timestamp” for .

This way, you can use date/timestamps from Python without any additional fiddling in most cases. The format of the adapters is also compatible with the experimental SQLite date/time functions.

The following example demonstrates this.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

If a timestamp stored in SQLite has a fractional part longer than 6 numbers, its value will be truncated to microsecond precision by the timestamp converter.

Note

The default “timestamp” converter ignores UTC offsets in the database and always returns a naive object. To preserve UTC offsets in timestamps, either leave converters disabled, or register an offset-aware converter with .

How-to guides

How to use placeholders to bind values in SQL queries

SQL operations usually need to use values from Python variables. However, beware of using Python’s string operations to assemble queries, as they are vulnerable to SQL injection attacks. For example, an attacker can simply close the single quote and inject

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

97 to select all rows:

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

Instead, use the DB-API’s parameter substitution. To insert a variable into a query string, use a placeholder in the string, and substitute the actual values into the query by providing them as a of values to the second argument of the cursor’s method.

An SQL statement may use one of two kinds of placeholders: question marks (qmark style) or named placeholders (named style). For the qmark style, parameters must be a whose length must match the number of placeholders, or a is raised. For the named style, parameters should be an instance of a (or a subclass), which must contain keys for all named parameters; any extra items are ignored. Here’s an example of both styles:

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

Note

PEP 249 numeric placeholders are not supported. If used, they will be interpreted as named placeholders.

How to adapt custom Python types to SQLite values

SQLite supports only a limited set of data types natively. To store custom Python types in SQLite databases, adapt them to one of the .

There are two ways to adapt Python objects to SQLite types: letting your object adapt itself, or using an adapter callable. The latter will take precedence above the former. For a library that exports a custom type, it may make sense to enable that type to adapt itself. As an application developer, it may make more sense to take direct control by registering custom adapter functions.

How to write adaptable objects

Suppose we have a

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

02 class that represents a pair of coordinates,

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

03 and

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

04, in a Cartesian coordinate system. The coordinate pair will be stored as a text string in the database, using a semicolon to separate the coordinates. This can be implemented by adding a

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

05 method which returns the adapted value. The object passed to protocol will be of type .

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

How to register adapter callables

The other possibility is to create a function that converts the Python object to an SQLite-compatible type. This function can then be registered using .

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

How to convert SQLite values to custom Python types

Writing an adapter lets you convert from custom Python types to SQLite values. To be able to convert from SQLite values to custom Python types, we use converters.

Let’s go back to the

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

02 class. We stored the x and y coordinates separated via semicolons as strings in SQLite.

First, we’ll define a converter function that accepts the string as a parameter and constructs a

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

02 object from it.

Note

Converter functions are always passed a object, no matter the underlying SQLite data type.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

We now need to tell

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 when it should convert a given SQLite value. This is done when connecting to a database, using the detect_types parameter of . There are three options:

Implicit: set detect_types to
Explicit: set detect_types to

Both: set detect_types to

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

15. Column names take precedence over declared types.

The following example illustrates the implicit and explicit approaches:

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

Adapter and converter recipes

This section shows recipes for common adapters and converters.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

How to use connection shortcut methods

Using the , , and methods of the class, your code can be written more concisely because you don’t have to create the (often superfluous) objects explicitly. Instead, the objects are created implicitly and these shortcut methods return the cursor objects. This way, you can execute a

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

8 statement and iterate over it directly using only a single call on the object.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

How to use the connection context manager

A object can be used as a context manager that automatically commits or rolls back open transactions when leaving the body of the context manager. If the body of the statement finishes without exceptions, the transaction is committed. If this commit fails, or if the body of the

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

25 statement raises an uncaught exception, the transaction is rolled back.

If there is no open transaction upon leaving the body of the

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

25 statement, the context manager is a no-op.

Note

The context manager neither implicitly opens a new transaction nor closes the connection.

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

How to work with SQLite URIs

Some useful URI tricks include:

Open a database in read-only mode:

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

Do not implicitly create a new database file if it does not already exist; will raise if unable to create a new file:

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

Create a shared named in-memory database:

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

More information about this feature, including a list of parameters, can be found in the SQLite URI documentation.

How to create and use row factories

By default,

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 represents each row as a . If a

con.commit()

9 does not suit your needs, you can use the class or a custom .

While

cur.execute("CREATE TABLE movie(title, year, score)")

47 exists as an attribute both on the and the , it is recommended to set , so all cursors created from the connection will use the same row factory.

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

21 provides indexed and case-insensitive named access to columns, with minimal memory overhead and performance impact over a

con.commit()

9. To use

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

21 as a row factory, assign it to the

cur.execute("CREATE TABLE movie(title, year, score)")

47 attribute:

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

Queries now return

>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)

21 objects:

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

You can create a custom that returns each row as a , with column names mapped to values:

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

Using it, queries now return a

cur.execute("CREATE TABLE movie(title, year, score)")

62 instead of a

con.commit()

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

The following row factory returns a :

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

47 can be used as follows:

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

With some adjustments, the above recipe can be adapted to use a , or any other custom class, instead of a .

Explanation

Transaction control

The

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 module does not adhere to the transaction handling recommended by PEP 249.

If the connection attribute is not

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3, new transactions are implicitly opened before and executes

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

cur.execute("CREATE TABLE movie(title, year, score)")

69,

cur.execute("CREATE TABLE movie(title, year, score)")

70, or

cur.execute("CREATE TABLE movie(title, year, score)")

71 statements; for other statements, no implicit transaction handling is performed. Use the and methods to respectively commit and roll back pending transactions. You can choose the underlying — that is, whether and what type of

>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True

61 statements

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

0 implicitly executes – via the attribute.

If is set to

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

3, no transactions are implicitly opened at all. This leaves the underlying SQLite library in , but also allows the user to perform their own transaction handling using explicit SQL statements. The underlying SQLite library autocommit mode can be queried using the attribute.

The method implicitly commits any pending transaction before execution of the given SQL script, regardless of the value of .

Apa bedanya SQL dan Python?

Perbedaan SQL vs Python yang paling signifikan dalam SQL digunakan untuk mengakses dan mengekstrak data dari database, sedangkan Python digunakan untuk menganalisis dan memanipulasi data dengan menjalankan berbagai tes mulai dari tes deret waktu, tes regresi, dan bentuk penghitungan data lainnya.

Python gunanya apa?

Python adalah bahasa pemrograman yang banyak digunakan dalam aplikasi web, pengembangan perangkat lunak, ilmu data, dan machine learning (ML). Developer menggunakan Python karena efisien dan mudah dipelajari serta dapat dijalankan di berbagai platform.

Apa itu program SQL?

SQL Server merupakan software yang sering digunakan untuk melakukan manajemen data berbasis database relasional atau biasa disebut Relational Database Management System (RDBMS) yang dikembangkan oleh Microsoft.

Python menggunakan database apa?

Standar Interface Python untuk database adalah Python DB-API. Kebanyakan Interface database Python mematuhi standar ini.

Kenapa SQL mudah dipelajari?

Mudah Dipelajari Oleh Orang Awam Bahasa SQL termasuk bahasa yang mudah dipahami karena menggunakan penyederhanaan deklaratif, tidak seperti bahasa pemrograman lainnya yang membutuhkan pemahaman konseptual tingkat tinggi serta harus menghafal langkah-langkah untuk melakukan perintah.

Apa itu Python Basic?

Basic Python Programming and Development Trending Python adalah bahasa pemrograman yang terkenal karena dinamis dan mudah digunakan, terutama karena memungkinkan para programmer untuk menggunakan metode yang kaya akan gaya, daripada melakukan dengan cara tertentu.