Icon

kn_​example_​db_​sqlite_​python_​sql

KNIME, Python and SQL with local SQLite database

KNIME, Python and SQL with local SQLite database
Some examples how to interact with a (local) SQLite database with SQL commands via Python (Jupyter Notebook)
in the /data/ folder is a Jupyter notebook kn_example_db_sqlite_python_sql.ipynb where you can explore the options you have with SQL and Python using a local database

THX J. Thelen for support and doing the KNIME & SQL nodes



KNIME, Python and SQL with local SQLite databaseSome examples how to interact with a (local) SQLite database with SQL commands via Python (Jupyter Notebook)in the /data/ folder is a Jupyter notebook kn_example_db_sqlite_python_sql.ipynb where you can explore the options you have with SQL and Python using a local database 030 Select all Orders from Customer named "ALFKI" 035 Calculate the total revenue for each product 040 Classify products as 'Expensive' or 'Cheap' based on their unit price 045 Calculate the total revenue for each product, and classify as 'High' or 'Low' based on revenue 050 Execute the SQL query to join the Products table with the revenue and revenue class data 055 Export result to Excel Play around with SQLite and Date and Time functionshttps://hub.knime.com/-/spaces/-/latest/~lr2kArL8kWy1rdfw/ 001 List all tables & 005 Get Information about specific table 010 Get all data from table Products & 015 Get number of CustomerIDs from Order table & 020 Write dataframe back to SQLite database 090 Execute the VAKUUM command to optimize the database file & 100 Close the SQLite connection Jupyter notebooks can be found also on the GitHub page:https://github.com/ml-score/knime_meets_python/tree/main/kn_example_db_sqlite_python_sql knime://knime.workflow/data/northwind.sqlitethere is also a zipped version you canrevert to if anything goes wrong ...001 select an overview of all TablesSELECT "$${Sname}$$" AS table_name, * FROM PRAGMA_TABLE_INFO('$${Sname}$$');=> 005 get Information of Table090 Execute the VACUUM command to optimize the database fileSQLite provides a command to "vacuum" or "compact" your database.This command is used to free up unused space and reduce the size of the database file.loop over tablesand get informations015 Get the number of CustomerIDs from the Orders tableSELECT * FROM #table# WHERE CustomerID='ALFKI'Customersget data back fromSQL database to KNIME 010 Get all data from the Table Products020 Write the dataframe back to the SQLite databaseCustomerID_NumOrders_from_KNIMEOrder Details, CAST(("UnitPrice" * "Quantity") AS REAL) AS "Total_Revenue"get data back fromSQL database to KNIME100 in the end close the SQLite connectionProductsCASE WHEN ... THEN ...ELSE ...END AS ...get data back fromSQL database to KNIMEget data back fromSQL database to KNIMECASE WHEN SUM... THEN ...ELSE ...END AS ...GROUP BY ...Order DetailsProductsget data back fromSQL database to KNIMEtest_fom_knime.xlsxSQLite Connector DB Query Reader DB Query Reader DB SQL Executor Table Row ToVariable Loop Start Loop End DB Query Reader DB Query DB Table Selector DB Reader DB Query Reader DB Writer DB Table Selector DB Query DB Reader DB ConnectionCloser DB Table Selector DB Query DB Reader DB Reader DB Query DB Table Selector DB Table Selector DB Reader Excel Writer KNIME, Python and SQL with local SQLite databaseSome examples how to interact with a (local) SQLite database with SQL commands via Python (Jupyter Notebook)in the /data/ folder is a Jupyter notebook kn_example_db_sqlite_python_sql.ipynb where you can explore the options you have with SQL and Python using a local database 030 Select all Orders from Customer named "ALFKI" 035 Calculate the total revenue for each product 040 Classify products as 'Expensive' or 'Cheap' based on their unit price 045 Calculate the total revenue for each product, and classify as 'High' or 'Low' based on revenue 050 Execute the SQL query to join the Products table with the revenue and revenue class data 055 Export result to Excel Play around with SQLite and Date and Time functionshttps://hub.knime.com/-/spaces/-/latest/~lr2kArL8kWy1rdfw/ 001 List all tables & 005 Get Information about specific table 010 Get all data from table Products & 015 Get number of CustomerIDs from Order table & 020 Write dataframe back to SQLite database 090 Execute the VAKUUM command to optimize the database file & 100 Close the SQLite connection Jupyter notebooks can be found also on the GitHub page:https://github.com/ml-score/knime_meets_python/tree/main/kn_example_db_sqlite_python_sql knime://knime.workflow/data/northwind.sqlitethere is also a zipped version you canrevert to if anything goes wrong ...001 select an overview of all TablesSELECT "$${Sname}$$" AS table_name, * FROM PRAGMA_TABLE_INFO('$${Sname}$$');=> 005 get Information of Table090 Execute the VACUUM command to optimize the database fileSQLite provides a command to "vacuum" or "compact" your database.This command is used to free up unused space and reduce the size of the database file.loop over tablesand get informations015 Get the number of CustomerIDs from the Orders tableSELECT * FROM #table# WHERE CustomerID='ALFKI'Customersget data back fromSQL database to KNIME 010 Get all data from the Table Products020 Write the dataframe back to the SQLite databaseCustomerID_NumOrders_from_KNIMEOrder Details, CAST(("UnitPrice" * "Quantity") AS REAL) AS "Total_Revenue"get data back fromSQL database to KNIME100 in the end close the SQLite connectionProductsCASE WHEN ... THEN ...ELSE ...END AS ...get data back fromSQL database to KNIMEget data back fromSQL database to KNIMECASE WHEN SUM... THEN ...ELSE ...END AS ...GROUP BY ...Order DetailsProductsget data back fromSQL database to KNIMEtest_fom_knime.xlsxSQLite Connector DB Query Reader DB Query Reader DB SQL Executor Table Row ToVariable Loop Start Loop End DB Query Reader DB Query DB Table Selector DB Reader DB Query Reader DB Writer DB Table Selector DB Query DB Reader DB ConnectionCloser DB Table Selector DB Query DB Reader DB Reader DB Query DB Table Selector DB Table Selector DB Reader Excel Writer

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: A9699459
Created at: 2023-03-29
On NodePit since: 2023-05-05
Last update: 2024-11-21
Created with KNIME version: v4.7.2
Tags: SQLSQLitePythonJupyter

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!