Rohit Chouhan

What is PySQL Framework?

PySQL is database framework for Python (v3.x) Language, Which is based on Python module mysql.connector, this module can help you to make your code more short and more easier. Before using this framework you must have knowledge about list, tuple, set, dictionary because all codes are designed using it. It’s totally free and open source.

Tutorial Video in English (Watch Now)

IMAGE ALT TEXT HERE

Installation

Before we said that this framework is based on mysql.connector so you have to install mysql.connector first on your system. Then you can import pysql and enjoy coding!

python -m pip install mysql-connector-python

After Install mysql.connector successfully create Python file download/install pysql on the same dir where you want to create program. You can clone is using git or npm command, and you can also downlaod manually from repository site.

PyPi Command

Go to https://pypi.org/project/pysql-framework/ or use command

pip install pysql-framework

Git Command

git clone https://github.com/rohit-chouhan/pysql

Npm Command

Go to https://www.npmjs.com/package/pysql or use command

$ npm i pysql

Snippet Extention for VS Code

Install From Here https://marketplace.visualstudio.com/items?itemName=rohit-chouhan.pysql

IMAGE ALT TEXT HERE

Connecting a Server


To connect a database with localhost server or phpmyadmin, use connect method to establish your python with database server.

import pysql

db = pysql.connect(
	"host",
	"username",
	"password"
 )

Create a Database in Server


Creating database in server, to use this method

import pysql

db = pysql.connect(
	"host",
	"username",
	"password"
 )
 pysql.createDb(db,"demo")
 #execute: CREATE DATABASE demo

Drop Database


To drop database use this method .

Syntex Code –
pysql.dropDb([connect_obj,"table_name"])
Example Code –
pysql.dropDb([db,"demo"])
#execute:DROP DATABASE demo

Connecting a Database


To connect a database with localhost server or phpmyadmin, use connect method to establish your python with database server.

import pysql

db = pysql.connect(
	"host",
	"username",
	"password",
	"database"
 )

Creating Table in Database


To create table in database use this method to pass column name as key and data type as value.

Syntex Code –
pysql.createTable([db,"table_name_to_create"],{
    "column_name":"data_type", 
    "column_name":"data_type"
})
Example Code –
pysql.createTable([db,"details"],{
    "id":"int(11) primary", 
     "name":"text", 
    "email":"varchar(50)",
    "address":"varchar(500)"
})
2nd Example Code –

Use can use any Constraint with Data Value

pysql.createTable([db,"details"],{
    "id":"int NOT NULL PRIMARY KEY", 
     "name":"varchar(20) NOT NULL", 
    "email":"varchar(50)",
    "address":"varchar(500)"
})

Drop Table in Database


To drop table in database use this method .

Syntex Code –
pysql.dropTable([connect_obj,"table_name"])
Example Code –
pysql.dropTable([db,"users"])
#execute:DROP TABLE users

Selecting data from Table


For Select data from table, you have to mention the connector object with table name. pass column names in set.

Syntex For All Data (*)
records = pysql.selectAll([db,"table_name"])
for x in records:
  print(x)
Example – –
records = pysql.selectAll([db,"details"])
for x in records:
  print(x)
#execute: SELECT * FROM details
Syntex For Specific Column
records = pysql.select([db,"table_name"],{"column","column"})
for x in records:
  print(x)
Example – –
records = pysql.select([db,"details"],{"name","email"})
for x in records:
  print(x)
#execute: SELECT name, email FROM details
Syntex Where and Where Not
#For Where Column=Data
records = pysql.selectWhere([db,"table_name"],{"column","column"},("column","data"))

#For Where Not Column=Data (use ! with column)
records = pysql.selectWhere([db,"table_name"],{"column","column"},("column!","data"))
for x in records:
  print(x)
Example – –
records = pysql.selectWhere([db,"details"],{"name","email"},("county","india"))
for x in records:
  print(x)
#execute: SELECT name, email FROM details WHERE country='india'

Add New Column to Table


To add column in table, use this method to pass column name as key and data type as value. Note: you can only add one column only one call

Syntex Code –
pysql.addColumn([db,"table_name"],{
    "column_name":"data_type"
})
Example Code –
pysql.addColumn([db,"details"],{
    "email":"varchar(50)"
})
#execute: ALTER TABLE details ADD email varchar(50);

Modify Column to Table


To modify data type of column table, use this method to pass column name as key and data type as value.

Syntex Code –
pysql.modifyColumn([db,"table_name"],{
    "column_name":"new_data_type"
})
Example Code –
pysql.modifyColumn([db,"details"],{
    "email":"text"
})
#execute: ALTER TABLE details MODIFY COLUMN email text;

Drop Column from Table


Note: you can only add one column only one call

Syntex Code –
pysql.dropColumn([db,"table_name"],"column_name")
Example Code –
pysql.dropColumn([db,"details"],"name")
#execute: ALTER TABLE details DROP COLUMN name

Manual Execute Query


To execute manual SQL Query to use this method.

Syntex Code –
pysql.query(connector_object,your_query)
Example Code –
pysql.query(db,"INSERT INTO users (name) VALUES ('Rohit')")

Inserting data


For Inserting data in database, you have to mention the connector object with table name, and data as sets.

Syntex –
data = 	{
	"db_column":"Data for Insert",
	"db_column":"Data for Insert"
}
pysql.insert([db,"table_name"],data)
Example Code –
data = 	{
	"name":"Komal Sharma",
	"contry":"India"
}
pysql.insert([db,"users"],data)

Updating data


For Update data in database, you have to mention the connector object with table name, and data as tuple.

Syntex For Updating All Data
data = ("column","data to update")
pysql.updateAll([db,"users"],data)
Example – –
data = ("name","Rohit")
pysql.updateAll([db,"users"],data)
#execute: UPDATE users SET name='Rohit'
Syntex For Updating Data (Where and Where Not)
data = ("column","data to update")
#For Where Column=Data
where = ("column","data")

#For Where Not Column=Data (use ! with column)
where = ("column!","data")
pysql.update([db,"users"],data,where)
Example –
data = ("name","Rohit")
where = ("id",1)
pysql.update([db,"users"],data,where)
#execute: UPDATE users SET name='Rohit' WHERE id=1

Deleting data


For Delete data in database, you have to mention the connector object with table name.

Syntex For Delete All Data
pysql.deleteAll([db,"table_name"])
Example – –
pysql.deleteAll([db,"users"])
#execute: DELETE FROM users
Syntex For Deleting Data (Where and Where Not)
where = ("column","data")

pysql.delete([db,"table_name"],where)
Example –
#For Where Column=Data
where = ("id",1)

#For Where Not Column=Data (use ! with column)
where = ("id!",1)
pysql.delete([db,"users"],where)
#execute: DELETE FROM users WHERE id=1

How to convert your MySQL Database into CRUD REST API

What is CRUD

CRUD is an acronym that stands for CREATE, READ, UPDATE, and DELETE. These four database commands are the foundation of CRUD. This acronym is well-known among programmers, but many software developers view it as more of guidance since CRUD was not made as a modern way to create API

Why Crudigniter

There is single API for everything to perform CRUD, use can easily create record on database with POST, to read with GET, to delete for DELETE and for update PUT. You do this from multiple platform and application because we know this is power of API.

Installation and Use

Just copy the whole program its developed in Codeigniter 4, and add created database and implication from App/Config/Database.php or root/env, make sure you renamed env to .env. There are some classes in App/Controller don’t delete it.

Follow Complete Documentation Here

GitHub Repository: rohit-chouhan/crudigniter

 

SAP-Analytics-Cloud

Set filter value to data action in Sap Analytics Cloud

What is SAC

SAP Analytics Cloud (or SAP Cloud for Analytics) is a software as a service (SaaS) business intelligence (BI) platform designed by SAP. Analytics Cloud is made specifically with the intent of providing all analytics capabilities to all users in one product.

The SAC story allows input control values to be passed to Data Action Parameters, but this is impossible with the Analytical Application. But there is a perfect way to accomplish this.

There is no function available to pass Input Control or FilterLine values, but there is one perfectly good reason to do this.

  1. Create Data Actions from sidebar (not from insert tab) on Analytical Application, Add your data action name.
  2. Create new Button, and add this mention code below in onClick()
  3. Replace your Dimension and Data Action Parameter Id, use this code.
Application.showBusyIndicator("Preparing for execution..."); 
var filter = Table_1.getDataSource().getDimensionFilters("your_table_dimension");
for (var i = 0; i < filter.length; i++) {
	if (filter[i].type === FilterValueType.Single) {
		var singleFilterValueCC = cast(Type.SingleFilterValue, filter[i]);
		DataAction_1.setParameterValue("data_action_parameter_id", [singleFilterValueCC.value]);
	} else if (filter[i].type === FilterValueType.Multiple) {
		var multiFilterValueCC = cast(Type.MultipleFilterValue, filter[i]);
		DataAction_1.setParameterValue("data_action_parameter_id", multiFilterValueCC.values);
	}
}

DataAction_1.execute();
Application.refreshData();
Application.hideBusyIndicator();
Application.showMessage(ApplicationMessageType.Success,"Executed Successfully");

Conclusion

Data Action is very useful tool in Sap Analytical Cloud for large calculation, Some of feature In Analytical Cloud its little difficult in Back-end side. So using this way we can use Data Action to pass filters value to Data Action Parameter.