SQLite manipulation from
Python
Plan
1. Presentation
2. Use of the python SQLITE module
3. Connection to the SQLITE database
4. Create and delete tables
5. Insertion of data into a database
data
6. Retrieve data (SELECT) with SQLITE
7. Updating and deleting data
8. Correspondence between data types
SQLITE and Python.
1. Presentation
Python includes a module called 'sqlite3' to
allow the use of databases
sqlite.
2. Use of the python SQLITE module
To use the SQLite3 module, we need to
add an import in the python code of our
program:
import sqlite3
3. Connection to the database
SQLITE
We use the function [Link] to
connect to the database.
Note that if the database does not yet exist, a file
will be created in the program folder. And if that one-
If it already exists, it will be reused.
Creation or opening of the file called mydb by
SQLite3
db = [Link]('data/[Link]')
When we are done with a database
must close the connection as follows:
[Link]()
4. Create and delete tables
In order to manipulate the database data,
we must call on a cursor object.
We pass him SQL queries that he
will execute.
Finally, it is necessary to validate
the operation (commit) for the
modifications are to be made.
4. Create and delete tables
Example:
Having a cursor object
cursor = [Link]()
[Link]('''
CREATE TABLE users(id INTEGER PRIMARY KEY, name
TEXT
tel TEXT, email TEXT unique, passe TEXT); '''
[Link]()
to destroy the table:
Having a cursor object
cursor = [Link]()
[Link]('''DROP TABLE users;''')
[Link]()
5. Inserting data into a database
of data
To insert data, we will do it using
of a cursor object that will execute the
SQL insert request into.
To use the values of the variables of the
Python program, it is recommended to
reserve the value placeholders in the request by
the use of the symbol '?.'
5. Insertion of data into the database
data
Example:
In this example, we will add a user to the database, their
Information is in variables of the python program.
cursor = [Link]()
name1 = 'Andres'
phone1 = '3366858'
email1 = 'user@[Link]'
password1 = '12345'
Insertion of the first user
[Link]('''INSERT INTO users(name, phone, email, password)
VALUES(?,?,?,?);''', (name1,phone1, email1, password1)
[Link]()
5. Insertion of data into the database
data
The values of Python variables are passed in tuples. A
Another way to do it is to use a dictionary using the
reservation of place with ":keyname" :
[Link]('''INSERT INTO users(name, phone, email, password)
VALUES(:name,:phone, :email, :password)''', {'name':name1,
'phone':phone1, 'email':email1, 'password':password1})
If we want to insert multiple users we will use
executemany with a list of tuples:
users = [(name1,phone1, email1, password1), (name2,phone2, email2,
password2), (name3,phone3, email3, password3)]
[Link](''' INSERT INTO users(name, phone, email, password)
VALUES(?,?,?,?)''', users)
[Link]()
6. Retrieve data (SELECT)
with SQLITE
To retrieve data, execute the query with an object
cursor then use fetchone() to retrieve one
recording or fetchall() to retrieve the entire
recordings.
[Link]('''SELECT nom, email, tel FROM users;''')
user1 = [Link]() # retrieves the first record
print(user1[0]) # Displays the first retrieved column (name of
the user)
all_rows = [Link]() # all_rows est une liste.
for row in all_rows:
# row[0] returns the first column of the query (name),
row[1] returns the email.
print('{0} : {1}, {2}'.format(row[0], row[1], row[2]))
6. Retrieve data (SELECT)
with SQLITE
The cursor object works like an iterator, calling fetchall()
automatically
[Link]('''SELECT name, email, phone FROM users;''')
for row in cursor:
# row[0] returns the first column of the query (name), row[1]
return the email column.
print('{0} : {1}, {2}'.format(row[0], row[1], row[2]))
To use conditions in the SQL query, we still use
the reservation character "?":
user_id = 3
[Link]('''SELECT name, email, phone FROM users WHERE
id=?;''', (user_id,)
user = [Link]()
7. Update and deletion of
data
The procedure to update or delete
data is the same as that used for insertion:
Update the user whose id is 1
newphone = '3113093164'
userid = 1
[Link]('''UPDATE users SET tel = ? WHERE id = ? '''
(newphone, userid)
deletion of user with id 2
delete_userid = 2
[Link]('''DELETE FROM users WHERE id = ? ;''',
(delete_userid,))
[Link]()
8. Correspondence between types of
SQLITE data and Python.
The following correspondence shows the relationship
between the data types of SQLite and
those of Python:
The None type of Python is converted to NULL
the python typeint is converted to INTEGER
The float type of Python is converted to REAL.
the python typestr is converted to TEXT