aiosqlitedict is a Python package that provides easy way to convert from json/dict to sqlite and vise. versa.
Project description
aiosqlitedict is a Python package that provides fast, flexible and expressive data structures designed to make working with "relational" or "labeled" data both easy and intuitive.
Main Features:
- Easy conversion between sqlite table and Python dictionary and vice-versa.
- Execute SQL queries.
- Get values of a certain column in a Python list.
- delete from your table.
- convert your json file into a sql database table.
- Order your list with parameters like
order_by
,limit
..etc - Choose any number of columns to your dict, which makes it faster for your dict to load instead of selecting all.
Installation
py -m pip install -U aiosqlitedict
Usage
Aiosqlite is used to import a SQLite3 table as a Python dictionary.
In this example we have a database file named ds_data.db
this database has a table named ds_salaries
Now to create an instance of this table in python we do the following
from aiosqlitedict.database import Connect
ds_salaries = Connect("ds_data.db", "ds_salaries", "id")
now we can get rows of this table, to get job_title
and salary
of user with id 0
async def some_func():
...
user_0 = await ds_salaries.to_dict(0, "job_title", "salary")
print(user_0)
asyncio.run(some_func())
OUTPUT:
{'job_title': 'Data Scientist', 'salary': 70000}
now lets do some operations on our data
user_0 = await ds_salaries.to_dict(0, "job_title", "salary")
user_0["salary"] += 676 # increase user 0's salary
print(user_0["salary"])
# getting top 5 rows by salaries
salaries = await ds_salaries.select("salary", limit=5, ascending=False)
print(salaries)
# to get "job_title" but order with salaries
best_jobs = await ds_salaries.select("job_title", order_by="salary", limit=5, ascending=False)
print(best_jobs)
# We can do the same task by executing a query
best_jobs_2 = await ds_salaries.execute("SELECT job_title FROM ds_salaries ORDER BY salary DESC LIMIT 5")
print(best_jobs_2)
# to get job_titles that includes the title "scientist" without duplicates
scientists = await ds_salaries.select("job_title", like="scientist", distinct=True)
print(scientists)
# to get all users' salary that have the title "ML Engineer" using a query
ML_Engineers = await ds_salaries.execute("SELECT salary FROM ds_salaries WHERE job_title = 'ML Engineer'")
print(ML_Engineers)
# to get the highest salaries
high_salaries = await ds_salaries.select("salary", between=(10000000, 40000000)) # between 30M and 40M salary
print(sorted(high_salaries, reverse=True))
# but what if we want to know their ids? here order_by is best used
high_salaries2 = await ds_salaries.select("salary", order_by="salary", limit=3, ascending=False) # id of richest to poorest
print(high_salaries2)
high_salaries3 = await ds_salaries.select("id", order_by="salary", limit=3, ascending=False) # id of richest to poorest
print(high_salaries3)
OUTPUT
70676
[70000, 260000, 85000, 20000, 150000]
['Data Scientist', 'Data Scientist', 'BI Data Analyst', 'ML Engineer', 'ML Engineer']
[('Data Scientist',), ('Data Scientist',), ('BI Data Analyst',), ('ML Engineer',), ('ML Engineer',)]
['Data Scientist', 'Machine Learning Scientist', 'Lead Data Scientist', 'Research Scientist', 'AI Scientist', 'Principal Data Scientist', 'Applied Data Scientist', 'Applied Machine Learning Scientist', 'Staff Data Scientist']
[(14000,), (270000,), (7000000,), (8500000,), (256000,), (20000,)]
[30400000, 11000000, 11000000]
[30400000, 11000000, 11000000]
[177, 7, 102]
Lets say you want to delete a certain user
await ds_salaries.delete(5) # removing user with id 5 from the table.
finally updating our SQLite table
await ds_salaries.to_sql(0, user_0) # Saving user 0's data to the table
Contributing
Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.
Please make sure to update tests as appropriate.
License
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for aiosqlitedict-0.0.23-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 7194e591265a4514b03a68869cb723d8cbd70b706604c03e6fea4bbce9e9aeed |
|
MD5 | 7032e782ac9cd4ea6a2d91e903c4e8a8 |
|
BLAKE2b-256 | 33dab8a8a8b723c24c82da15c726035e3770ff698e0b87e49cab44ac001f5c99 |