sayuDB

Preface

What is sayuDB

sayuDB is an database management system based on python and JSON. Developed at Clee Ltd. This project actually for personal purpose only but for some reason I publish it. It supports a large part of the SQL standard feature

Tutorial

Table of contents

• Installation
• Create or remove user
• Grant privileges to user
• Create and drop database
• Import and export database
• Activate or deactivate server
• Using the sayuDB
• Create table
• Drop table
• Show table
• Insert
• Select
• Where
• Update
• Delete
• Alter table
• Clear table

---

Installation

> pip3 install sayuDB

and if you want to show help menu just

> python3 -m sayuDB --h

Create or remove user

To create a user you can command by

> python3 -m sayuDB create user <username> <password>

and if you want to remove user you can command by

> python3 -m sayuDB remove user <username>

Note that creating or removing user only can be user when you have root/local access. the user is use for remote database. if you want to user this database locally, you didn't need this.

And for show user, you can command by

> python3 -m sayuDB show users

Grant privileges to user

Ofcourse you need to grant user to some database first before use it remotely. Grant it by command

> python3 -m sayuDB grant user <username> <database_name>

Create and drop database

To create a database, just command by

> python3 create database <database_name>

And to drop/delete it, just command by

> python3 -m sayuDB drop database <database_name>

Anyway, you can also show databases by

> python3 -m sayuDB show databases

Import and export database

To import a database, just command by

> python3 -m sayuDB import database <path_to_ezdb_file>

And for export it, just command by

> python3 -m sayuDB export database <database_name> <output_path>

Activate or deactivate server

If you want the database remotely online. just activate server by

> python3 -m sayuDB activate server <port>

Note: I recomended use 8787 as port then acces it within your server IP. if you didn't see something, try make the server public by

> python3 -m sayuDB setup public server <server_name> <port>

The server_name is your server IP or a domain that already set A record to your server IP. And the port must be same when you activate it.

If you want to deactivate it, just command by

> python3 -m sayuDB deactivate server

---

Using the sayuDB

To user sayu db in python, just define it first by

import sayuDB # Importing the sayuDB
db = sayuDB.sayuDB("testdb") # call the class

The parameters are

parameter	status	description
database	required	the database name
username	optional	the username for you sayuDB account if you access it remotely
password	optional	ofc the password
host	optional	the host of your database hosted in
as_json	optional	this is the boolean if you want to return the database as json or just printed table.

Create table

To create a table use this following function

db.create_table("<table_name>", col=[
    ["number","int"],
    ["name","str"],
    ["age","int"],
    ["address","str"]
])

The parameters are

parameter	status	description
name	required	the name of table you want to create
col	required	a lists that contain list wich contain the column name and datatypes [ ["<column_name>","<datatypes>"] ]

Note: supported datatypes str,float,int,dict

Drop table

You can drop table by use this dollowing function

db.drop_table("<table_name>")

The parameters are

parameter	status	description
name	required	the name of the table you want to drop/delete

Show table

you can show the table by use this following funtion.

db.show_table("<table_name>")

it will returned

number    name    age    address
--------  ------  -----  ---------

Insert

To insert some row to the table, you can use this following function

db.insert_row(table="<table_name>", col="number,name,age,address", contents=[1,"Arsybai",23,"Solo, Indonesia"])

The parameters are

parameter	status	description
table	required	The name of table you want to insert a row
col	required	the column that you want to insert. separate by comma or you can use list
contents	required	the contents/value for each column

and now if you show the table it will look like this

  number  name       age  address
--------  -------  -----  ---------------
       1  Arsybai     23  Solo, Indonesia

and you can also insert it with some function. as example I want to insert moepoi as number 2. but I want the number automatically increased.

db.insert_row(table="<table_name>", col="number,name,age,address", contents=["increase()","Moepoi",20,"Jakarta, Indonesia"])

So, I use '`increase()' function for it. then the table will look like this

  number  name       age  address
--------  -------  -----  ------------------
       1  Arsybai     23  Solo, Indonesia
       2  Moepoi      20  Jakarta, Indonesia

Table of functions

name	description
today()	Generate today datetime (only work in str typedata)
index()	Indexing the number of row. it can be use as id too (only work in int typedata)
genID()	Generate random 5 digits ID
increase()	Increase from the last row value (Only work in int typedata)

Select

To select a table, use this following function

db.select_row(table="<table_name>", col="name,age")

parameter	status	description
table:str	required	the name of table that you want to select
col:str	required	the column name that you want to select. it separated by comma. or you can use col="*" if you want to select all comuns
where:str	optional	select the specific row.
as_json:bool	optional	if you already define it in the sayuDB class, you didn't need to do it again. the default is False

Where

Now, if you want to select specific row, you can use where parameter for it

Equal or not equal.

as example, I want to select Arsybai from table.

db.select_row(table="<table_name>", col="*", where="name=Arsybai")

Otherwise, if you want to select row that isn't Arsybai use where="name!=Arsybai" The return will look like this

  number  name       age  address
--------  -------  -----  ---------------
       1  Arsybai     23  Solo, Indonesia

1 Rows

contains

If you want to select row that contain someting. you can use contain. as example I want to select name that contain poi

db.select_row(table="<table_name>", col="*", where="name contain poi")

The return will look like this

  number  name      age  address
--------  ------  -----  ------------------
       2  Moepoi     20  Jakarta, Indonesia

1 Rows

Using multiple condition

as example I want to select Arsybai with age 20

db.select_row(table="<table_name>", col="*", where="name=Arsybai && age=20")

The return will look like this

number    name    age    address
--------  ------  -----  ---------

0 Rows

Note : You can use AND ( && ), OR ( || ) in multiple condition. also the space between && or || is required

Update

If you want to update a row, use this following function

db.update_row(table="<table_name>", set_="<column_name>=<value>", where="<column_name>=<value>")

Note that update row isn't support multiple condition.

Also you can update a row more efficienly with update_row_json

db.update_row_json(table="<table_name>", set_={"<col1_name>":"<value1>","<col2_name>":"<value2>"}, where="<col_name>=<value>")

Delete

If you want to delete some row, use this following function

#to delete specific data that equal with some value
db.delete_row(table="<table_name>", where="<col_name>=<value>")

#to delete specific data that contain some value
db.delete_row(table="<table_name>", where="<col_name> contain <value>")

#to delete specific data with row number
db.delete_row(table="<table_name>", where="row <number_of_row>")

#to delete specific data between row number
db.delete_row(table="<table_name>", where="row between <number_of_rowX>-<number_of_rowY>")

Alter table

To alter or updating some table, use this following function

# to add a column
db.alter_table_add_column(table="<table_name>", col_name="<col_name>", datatypes="<datatype>")

# to remove a column
db.alter_table_drop_column(table="<table_name>", col_name="<col_name>")

# to rename a column
db.alter_table_rename_column(table="<table_name>", col="<column_to_rename>", to_="<rename_to>")

Clear table

To clear table use this following function

db.clear_table(table="<table_name>")

This will erase all rows data in the table.