SQLLEX Simple and Faster.

from sqllex import * from sqllex.types import SQLRequest db = SQLite3x( path='my_data.db', template={ "users": { "username": [TEXT, NOT_NULL], "age": INTEGER, } } ) db.insert('users', ['Sqllex', 33]) users = db.select('users', 'username', WHERE={'age': 33}) print(users) # ['Sqllex']

# Init-ing your databse db = SQLite3x(path='my_awesome_db.db')

# Creating Groups table db.create_table( 'groups', # here is name of table { # here is table structure 'id': [INTEGER, PRIMARY_KEY, UNIQUE], # group id 'name': [TEXT, NOT_NULL, DEFAULT, 'Unknown'] # group name } )

db.create_table( name='users', # here is name of table columns={ 'id': [INTEGER, PRIMARY_KEY, UNIQUE], # user id 'username': [TEXT, NOT_NULL, DEFAULT, 'Unknown'], # user name 'user_group': INTEGER, # the group user belongs to FOREIGN_KEY: { "user_group": ["groups", "id"] # link to table groups, column id } })

# Record some groups for the first db.insert('groups', id=1, name="Admin") # You can add data like this db.insert('groups', [2, "User"]) # Or like this db.insert('groups', 3, 'Guest') # Or like this

# Here we have a list of users in format: [id, name, group_id] users_list = [ [0, "User_0", 1], [1, "User_1", 2], [2, "User_2", 3], [3, "User_3", 1], [4, "User_4", 2], [5, "User_5", 3], [6, "User_6", 1], [7, "User_7", 2], [8, "User_8", 3], [9, "User_9", 1], ] # Insert it by one line db.insertmany('users', users_list)

# SELECT FROM (table) (what) users_in_db = db.select('users', 'username') print(users_in_db)

users_group_1 = db.select( 'users', 'username', WHERE={'user_group': 1} ) print(users_group_1)

db = SQLite3x('db-1') db.connect() db.create_table("numbers", {"value": [INTEGER]}, IF_NOT_EXIST=True) for i in range(1000): db.insert("numbers", i) print(db.select("numbers",ALL)) db.disconnect()

import pandas as pd # Load CSV data into Pandas DataFrame bank = pd.read_csv('Database/bank_data.csv') bank.head()

bank.columns

bank_db = SQLite3x( path='Database/Bank_data.db', template={ "recovery": { "id": [INTEGER, PRIMARY_KEY, UNIQUE], "expected_recovery_amount": INTEGER, "actual_recovery_amount": REAL, "recovery_strategy": TEXT, "age": INTEGER, "sex": [TEXT,NOT_NULL, 'Unknown'] } } )

bank_db.insertmany('recovery', bank.values)

bank_db.select('recovery', '*',LIMIT=10)

academic_db = SQLite3x('Database/scopus_data.sqlite/scopus_data.sqlite')

academic_db.pragma("database_list")

academic_db.execute("SELECT name FROM sqlite_master WHERE type='table';")

academic_db.table_info('AI_scopus')

academic_db.select("AI_scopus",["Title","Year"],LIMIT=5)

academic_db.update( 'AI_scopus', # table name ['Title', 'its hard'], ['Title', 'Soft-Margin Learning for Multiple Feature-Kernel Combinations with Domain Adaptation, for Recognition in Surveillance Face Datasets'] # where id == 1 )

academic_db.select("AI_scopus",["Title","Year"],LIMIT=5)

academic_db.delete('AI_scopus',{'Title': "its hard"})

academic_db.select("AI_scopus",["Title","Year"],LIMIT=5)

academic_db.drop('industry_data')

academic_db.execute("SELECT name FROM sqlite_master WHERE type='table';")

academic_db.select("university_data",ALL) ## ALL -> "*"

academic_db.table_info('university_data')

academic_db.connect() ## db.connect improve query time. request = SQLRequest( script=""" INSERT INTO university_data (name, publish_paper) VALUES (?, ?) """, values=(('IIT-Manipur', 20), ('JANT', 31)) ) ## ? ? is place holder for values that are added later. academic_db.executemany(request=request) ## executemany will help us to run ## multiple script at once as in our case we need ## to add multiple values. academic_db.disconnect()

academic_db.select("university_data",ALL)

university = academic_db.select("university_data",ALL) university_db = pd.DataFrame(university,columns=["University","Published_Numbers"]) university_db.head()