SQLite3x | Awesome example #0
This is basic example, for whom never before used sqlite3x
First you need to install sqllex by pip
pip install sqllex
Create main.py file and
And type some code into it
from sqllex.classes import SQLite3x
from sqllex.constants.sqlite import *
db = SQLite3x()
After you run this code you'll see a database file in the same directory as your mail.py
file
You can open it (by sqlitebrowser for example) and make sure it works and it's empty
Let's add table into this database
Imagine you need save some data about users consist of id
and username
.
Let's ask db to "create table
named
'users'
with columns
:
'id'
(have to beinteger
)'username'
(have to betext
-like and can not be empty)". Now type it as code.
db.create_table(
name='users',
columns={
'id': INTEGER,
'username': [TEXT, NOT_NULL]
},
IF_NOT_EXIST=True
)
IF_NOT_EXIST=True
- highly recommend set this argument True
it'll avoid you an error (in the next runs) if table already exist.
Run it. Done, and results:
Awesome table created, it's time to insert some data into it
Take table called users
(as table_users) and insert
into this TABLE next data (record): user_id
in column id
and user_name
in column username
user_id = 1
user_name = 'Alex'
table_users = db['users']
table_users.insert(
id=user_id, username=user_name
)
Run it.
And yup, we're in! So now we'll take it back.
Select all records from table
So select
ALL (by default) form TABLE named 'users'
, save it into var users
and print it.
users = table_users.select_all()
print(users) # [(1, 'Alex')]
Run it. It returns:
[(1, 'Alex')]
Great! Now let's add more users.
Insert many data
It's kind of the same as just insert
one record, but only use insertmany
method if you want make it for lists of data. In this example we have list of 4 new users that we want insert into a database (id, username).
new_users = [
(2, 'User2'),
(3, 'User3'),
(4, 'User4'),
(5, 'User5'),
]
table_users.insertmany(new_users)
And select all data from table again:
users = table_users.select_all()
print(users)
Returns:
[(1, 'Alex'), (2, 'User2'), (3, 'User3'), (4, 'User4'), (5, 'User5')]
Perfect!
Little bit more about selects
You have to know that select
method can be more selective (:D). You don't have to select all records from table all the time, you can just add a selection condition like WHERE
Lets select all records from table 'users'
records satisfying the condition id == 2
:
user2 = table_users.select(
WHERE='id=2',
)
print(user2)
returns:
([2, 'User2'])
Well done. How about get records WHERE
id != 2
:
users_345 = table_users.select(
WHERE=(table_users['id'] != 2)
)
print(users_345)
We got:
[(3, 'User3'), (4, 'User4'), (5, 'User5')]
If you need get only usernames
of records satisfying the condition, set SELECT
value.
users_names = table_users.select(
SELECT='username',
WHERE=(table_users['id'] > 2)
)
print(users_names)
We got:
[('User3',), ('User4',), ('User5',)]
Good job!
Mark up one more table and insert data into it
Earlier we got many lists of records, one of this users_345
print(users_345)
[(3, 'User3'), (4, 'User4'), (5, 'User5')]
Now create one more table but by mark up
method. And insert users_345 into it
new_table_scheme = {
'some_users': {
'id': INTEGER,
'username': [TEXT, NOT_NULL]
}
}
db.markup(new_table)
new_table = db['new_table']
new_table.insertmany(users_345)
print(db.tables_names)
returns:
['users', 'some_users']
Delete (drop) table
Now lest remove this new table. For this use drop
method with name of table (some_users
)
new_table.drop()
Cool.
Update data in record
As you see in table users
first record looks not like an other. I guess we have to fix it. Just updater data of this one record.
table_users.update(
SET=['username', 'User1'],
WHERE=['id', 1]
)
Run it and we got: