PySQlite

import sqlite3

Create a database in RAM

db = sqlite3.connect(‘:memory:’)

Creates or opens a file called mydb with a SQLite3 DB

db = sqlite3.connect(‘data/mydb’)

db.close()

Get a cursor object

cursor = db.cursor() cursor.execute(‘’‘ CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT unique, password TEXT) ‘’‘) db.commit()

Get a cursor object

cursor = db.cursor() cursor.execute(‘’‘DROP TABLE users’‘’) db.commit()

cursor = db.cursor() name1 = ‘Andres’ phone1 = ‘3366858’ email1 = ‘user@example.com’

A very secure password

password1 = ‘12345’

name2 = ‘John’ phone2 = ‘5557241’ email2 = ‘johndoe@example.com’ password2 = ‘abcdef’

Insert user 1

cursor.execute(‘’‘INSERT INTO users(name, phone, email, password) VALUES(?,?,?,?)’‘’, (name1,phone1, email1, password1)) print(‘First user inserted’)

Insert user 2

cursor.execute(‘’‘INSERT INTO users(name, phone, email, password) VALUES(?,?,?,?)’‘’, (name2,phone2, email2, password2)) print(‘Second user inserted’)

db.commit()

cursor.execute(‘’‘INSERT INTO users(name, phone, email, password) VALUES(:name,:phone, :email, :password)’‘’, {‘name’:name1, ‘phone’:phone1, ‘email’:email1, ‘password’:password1})

users = [(name1,phone1, email1, password1), (name2,phone2, email2, password2), (name3,phone3, email3, password3)] cursor.executemany(‘’‘ INSERT INTO users(name, phone, email, password) VALUES(?,?,?,?)’‘’, users) db.commit()

id = cursor.lastrowid print(‘Last row id: %d’ % id)

cursor.execute(‘’‘SELECT name, email, phone FROM users’‘’) user1 = cursor.fetchone() #retrieve the first row print(user1[0]) #Print the first column retrieved(user’s name) all_rows = cursor.fetchall() for row in all_rows: # row[0] returns the first column in the query (name), row[1] returns email column. print(‘{0} : {1}, {2}’.format(row[0], row[1], row[2]))

cursor.execute(‘’‘SELECT name, email, phone FROM users’‘’) for row in cursor: # row[0] returns the first column in the query (name), row[1] returns email column. print(‘{0} : {1}, {2}’.format(row[0], row[1], row[2]))

user_id = 3 cursor.execute(‘’‘SELECT name, email, phone FROM users WHERE id=?’‘’, (user_id,)) user = cursor.fetchone()

Update user with id 1

newphone = ‘3113093164’ userid = 1 cursor.execute(‘’‘UPDATE users SET phone = ? WHERE id = ? ‘’‘, (newphone, userid))

Delete user with id 2

delete_userid = 2 cursor.execute(‘’‘DELETE FROM users WHERE id = ? ‘’‘, (delete_userid,))

db.commit()

cursor.execute(‘’‘UPDATE users SET phone = ? WHERE id = ? ‘’‘, (newphone, userid)) db.commit() #Commit the change

cursor.execute(‘’‘UPDATE users SET phone = ? WHERE id = ? ‘’‘, (newphone, userid))

The user’s phone is not updated

db.rollback()

import sqlite3 #Import the SQLite3 module try: # Creates or opens a file called mydb with a SQLite3 DB db = sqlite3.connect(‘data/mydb’) # Get a cursor object cursor = db.cursor() # Check if table users does not exist and create it cursor.execute(‘’‘CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT unique, password TEXT)’‘’) # Commit the change db.commit()

Catch the exception

except Exception as e: # Roll back any change if something goes wrong db.rollback() raise e finally: # Close the db connection db.close()

Catch the exception

except Exception as e: raise e

name1 = ‘Andres’ phone1 = ‘3366858’ email1 = ‘user@example.com’

A very secure password

password1 = ‘12345’

try: with db: db.execute(‘’‘INSERT INTO users(name, phone, email, password) VALUES(?,?,?,?)’‘’, (name1,phone1, email1, password1)) except sqlite3.IntegrityError: print(‘Record already exists’) finally: db.close() db = sqlite3.connect(‘data/mydb’) db.row_factory = sqlite3.Row cursor = db.cursor() cursor.execute(‘’‘SELECT name, email, phone FROM users’‘’) for row in cursor: # row[‘name’] returns the name column in the query, row[‘email’] returns email column. print(‘{0} : {1}, {2}’.format(row[‘name’], row[‘email’], row[‘phone’])) db.close()