Examples

Here are examples of how to use the QuickSqlite module.

Creating a database

Here’s how you can create a single simple database.

from quick_sqlite import QuickSqlite

# Create a new database
db = QuickSqlite('my_database.db', False)

You can also work with multiple databases.

from quick_sqlite import QuickSqlite

# Create a simple database
db = QuickSqlite('my_database.db', False)

key = b'3kVCk6cXQqLX3HTFMg4XUvHVt7sG__-C25p4uAZvrIE='
# Create an encrypted database
db2 = QuickSqlite('my_database2.db', True, key)

You can generate a fernet key using cryptography module.

>>> from cryptography.fernet import Fernet
>>> key = Fernet.generate_key()
b'3kVCk6cXQqLX3HTFMg4XUvHVt7sG__-C25p4uAZvrIE='

You can read more about Fernet here.

Creating a table

>>> from quick_sqlite import Column
>>> db.create_table('accounts', [Column('id', 'INT'), Column('name', 'VARCHAR(255)'), Column('email', 'VARCHAR(255)'), Column('password', 'VARCHAR(255)')])

Here table name is ‘accounts’ and columns are ‘id’, ‘name’, ‘email’ and ‘password’. The Column class is imported from quick_sqlite package and its first arguement is column name and the second arguement is column data type.

Renaming a table

>>> db.rename_table('accounts', 'users')
>>> db.rename_table('users', 'accounts')

First line will change name of the table ‘accounts’ to ‘users’. Second line will change it back to ‘accounts’

Checking for a table

>>> db.check_table_exists('accounts')
True

It returns True as we just recently created table accounts.

>>> db.check_table_exists('customers')
False

It returns False as there is no table customers in our database right now.

Deleting a table

>>> db.delete_table('accounts')

This will delete the table ‘accounts’ from our database.

Inserting data

data = {
'id': 1,
'name': 'John Doe',
'email': 'email@example.com',
'password': '123456789'
}
db.insert_data('accounts', data)

This will insert the data in the specified columns.

Here the data is provided as a dictionary where the key is column name and the value is column data.

Updating data

data = {
'name': 'Dan',
}
db.update_data('accounts', data, 'id', 1)

This will update the value of name column to Dan where id is 1.

Fetching a table

>>> table = db.fetch_table('accounts')
>>> print(table)
[(1, 'Dan', 'email@example.com', '123456789'), (2, 'Joe', 'email2@example.com', '930232213'), (3, 'Smith', 'email3@example.com', '232131231')]

This will return all the entries of a table in a list containing tuple of each row.

If you want specific number of rows then use row_limit arguement.

>>> limited_table = db.fetch_table('accounts', 2)
>>> print(limited_table)
[(1, 'Dan', 'email@example.com', '123456789'), (2, 'Joe', 'email2@example.com', '930232213')]

Adding a column

>>> from quick_sqlite import Column
>>> db.add_column('accounts', Column('address', 'VARCHAR(255)'))

This will create a new column ‘address’ with data type ‘VARCHAR’ in table ‘accounts’.

Renaming a column

>>> db.rename_column('accounts', 'address', 'home address')

This will rename column ‘address’ to ‘home address’ in table ‘accounts’.

Deleting a column

>>> db.delete_column('accounts', 'home address')

This will delete column ‘home address’ in table ‘accounts’.

Selecting column

>>> column = db.fetch_column('accounts', 'name')
>>> print(column)
[('Dan',), ('Joe',), ('Smith',)]

This will return all entries of a column of the table.

Checking for a column

>>> db,check_column_exists('accounts', 'name')
True
>>> db,check_column_exists('accounts', 'address')
False

This will check if a column exists in the table.

Selecting data

>>> data = db.fetch_data('accounts', 'password', 'name', 'Dan', '=')
>>> print(data)
('123456789',)

This will return password of field where the name is equal to Dan.

>>> data = db.fetch_data('accounts', 'password', 'id', '1', '>')
>>> print(data)
None

This will return password of field where the id is greater than 1

The supported consitions are: =, <, >, <=, >=, <>(not equal).

The statement will look like: {filter_column_name} {condition} {filter_column_data}

Deleting row

>>> db.delete_row('accounts', 'name', 'Dan')

This will delete the row where name is Dan.