# SQLite3 in Python

[SQLite](https://www.ionos.com/digitalguide/websites/web-development/sqlite/ "SQLite") is the world’s most-used database and allows you to create an SQL-based database which can be integrated into all sorts of different applications. Python also supports SQLite3 by way of a module.

## Using SQLite3 in Python

You can connect SQLite to Python by using a module. To make your work more efficient with the database system in Python, we recommend you have some background knowledge of both. This makes the setup and, later on, use of the database much easier.

Tip If you want to refresh your knowledge, we’d recommend taking a look at our [Python tutorial](https://www.ionos.com/digitalguide/websites/web-development/python-tutorial/ "Python tutorial").

## How to set up a database – step by step

You can set up your own SQLite3 database in Python in a couple of steps. Once you’ve finished setting it up, you will be able to use the database using **standard SQL commands**.

### Step 1: Import SQLite3 into Python

So that you have access to the functions that are specific to SQLite3 in Python you will need to import the corresponding module first:

```python
import sqlite3
```

### Step 2: Create the database

The next step is to create your own database by using the SQLite function “connect()”. It will give you a connection object, from which you can connect to your database. To open connect and create a connection to the database “test”, you will need the following:

```python
connection = sqlite3.connect("test.db")
```

The transfer parameter of the function is a database file. If you still haven’t created a database file called “test.db” this will be done automatically by using the connect function.

### Step 3: Check whether the database was successfully created

At this point you can check whether your SQLite3 database has been successfully created. To do so you can call up the “total\_changes” function on the connection object you just created. It will show you how many table rows in your relational database have been changed since you connected to the database.

```python
print(connection.total_changes)
```

In this case the value would be “0” since we’ve not yet used any SQL commands and, therefore, have an empty database. In case you’ve already got data in your database, the value you see may be different.

### Step 4: Create the basic framework of your database

To add data to your SQLite3 database in Python, you will now need to create a table to store your data, as is the norm with [relational databases](https://www.ionos.com/digitalguide/hosting/technical-matters/relational-databases/ "Relational Databases").

To do this you will first need to create a cursor object with the pre-defined “cursor” function using SQLite3 in Python. This will allow you to send SQL commands to your database. The code you need to do this looks as follows:

```python
cursor = connection.cursor()
```

You can then use the “execute” function from the SQLite3 module to send SQL commands to your database. The function takes your commands, which follow standard SQL syntax, as strings. So, if you want to create a database with the name “example” and the columns “ID”, “name”, and “age”, then the code you use in Python will look as follows:

```python
cursor.execute("CREATE TABLE IF NOT EXISTS example (id INTEGER, name TEXT, age INTEGER)")
```

### Step 5: Add data

You will need the same syntax as you used to create a table to add data to a table:

```python
cursor.execute("INSERT INTO example VALUES (1, alice, 20)")
cursor.execute("INSERT INTO example VALUES (2, 'bob', 30)")
cursor.execute("INSERT INTO example VALUES (3, 'eve', 40)")
```

Using the code above you will have added three entries to your database table “example”.

To save the changes to your database you need to use the “commit” function.

```python
connection.commit()
```

Tip Do you want to save your SQLite3 database on your own server? You can choose between different server types. For small applications and amounts of data you could use a [vServer](https://www.ionos.com/servers/vps "vServer from IONOS") . For enterprises, however, we’d recommend choosing a [dedicated server](https://www.ionos.com/servers/dedicated-servers "Dedicated server from IONOS").

## Read the data

Of course, you can also **read and output data from your databases** instead of just using SQLite3 in Python to create them. To do this, you need to first connect to the database. From there you can create a connection and cursor object as explained in the step-by-step guide above. Finally you can formulate your SQL request, send it to the database using the “execute” function and use the “fetch\_all” function to display the results:

```python
cursor.execute("SELECT * FROM example")
rows = cursor.fetchall()
for row in rows:
    print(row)
```

The “fetch\_all” function will give you a list of rows which correspond to your request. To output these rows on the console, you can use a [Python for loop](https://www.ionos.com/digitalguide/websites/web-development/python-for-loop/ "Python for loop") alongside a print statement.

## Modify existing data

Since SQLite3 supports SQL commands, you can also **modify or delete** data within your database. To do so you just need to use the corresponding SQL command in your database. To start, this step also requires you to create a connection to your database followed by a connection and cursor object.

### Delete data

To delete the row with the ID 1, you can use the following code:

```python
cursor.execute("DELETE FROM example WHERE id = 1")
```

### Change data

You can use the following command to change the age in the row with ID 2:

```python
cursor.execute("UPDATE example SET age = 31 WHERE id = 2")
```

## Use a place holder

If you want to use dynamic SQL requests in your Python program, you should never use [Python string](https://www.ionos.com/digitalguide/websites/web-development/python-string/ "Python string") operations. Hackers can use them to attack your program with [SQL injections](https://www.ionos.com/digitalguide/server/security/sql-injection-fundamentals-and-safeguards/ "SQL injection: Fundamentals and safeguards"). Instead you should use the **SQL place holder “?”** which you can use with the SQL request you sent using “execute” instead of a specific value. You can also enter a second parameter as a [Python tuple](https://www.ionos.com/digitalguide/websites/web-development/python-tuples/ "Python tuples") to the “execute” function, which will be replaced by the question mark.

The following requests are the same:

```python
# Direct SQL requests
cursor.execute("UPDATE example SET age = 31 WHERE id = 2")
# SQL requests with place holders
age_var = 31
id_var = 2
cursor.execute("UPDATE example SET age = ? WHERE id = ?", (age_var, id_var))
```

Be aware that the question marks in the SQL request will be replaced in the order in which you set the variables in the tuple.

## End the connection to your database

After you have carried out all your tasks in your database, you will now need to end your connection. You can again use the SQLite3 module in Python to do so by opening it directly in your connection object:

```python
connection.close()
```


This is a markdown version of: [https://www.ionos.com/digitalguide/websites/web-development/sqlite3-python/](https://www.ionos.com/digitalguide/websites/web-development/sqlite3-python/) for AI/LLM consumption.