Last active
April 25, 2023 16:11
-
-
Save jsteinshouer/29f86ad2b31a1285c0b36f1e5e7ee6c1 to your computer and use it in GitHub Desktop.
Jupyter Notebook - cfquery
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"cells": [ | |
{ | |
"attachments": {}, | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"# CFQuery Example\n", | |
"\n", | |
"An example of using [cfquery](https://cfdocs.org/cfquery) in a Jupyter Notebook.\n", | |
"\n", | |
"For this demo we are going to use SQLite so first we will use some [magic commands](https://ipython.readthedocs.io/en/stable/interactive/magics.html) to install the SQLite JDBC driver.\n", | |
"\n", | |
"The `%install` magic command calls the CommandBox `install` command and passes the parameter to it. " | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 9, | |
"metadata": { | |
"vscode": { | |
"languageId": "cfml" | |
} | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
"\u001b[0m\u001b[32m | Installing package [jar:https://github.com/xerial/sqlite-jdbc/releases/download/3.41.0.1/sqlite-jdbc-3.41.0.1.jar]\u001b[0m\n", | |
"\u001b[32m" | |
] | |
} | |
], | |
"source": [ | |
"%install jar:https://github.com/xerial/sqlite-jdbc/releases/download/3.41.0.1/sqlite-jdbc-3.41.0.1.jar" | |
] | |
}, | |
{ | |
"attachments": {}, | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"There is also a magic command named `%loadjar` that takes a file path as a parameter. It will load a `jar` file for us." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 3, | |
"metadata": { | |
"vscode": { | |
"languageId": "cfml" | |
} | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
"Loaded /workspace/notebooks/lib/sqlite-jdbc-3.41.0.1/sqlite-jdbc-3.41.0.1.jar\n" | |
] | |
} | |
], | |
"source": [ | |
"%loadjar lib/sqlite-jdbc-3.41.0.1/sqlite-jdbc-3.41.0.1.jar" | |
] | |
}, | |
{ | |
"attachments": {}, | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"Now we can define our datasource as a structure. This is a Lucee only feature." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 2, | |
"metadata": { | |
"vscode": { | |
"languageId": "cfml" | |
} | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
"{\n", | |
" \u001b[94m\"CONNECTIONSTRING\"\u001b[0m:\u001b[92m\"jdbc:sqlite:./cfquery_demo.db\"\u001b[0m,\n", | |
" \u001b[94m\"CLASS\"\u001b[0m:\u001b[92m\"org.sqlite.JDBC\"\u001b[0m\n", | |
"}" | |
] | |
} | |
], | |
"source": [ | |
"ds = {\n", | |
" class: 'org.sqlite.JDBC',\n", | |
" connectionString: 'jdbc:sqlite:./cfquery_demo.db',\n", | |
"};" | |
] | |
}, | |
{ | |
"attachments": {}, | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"We then use `queryExecute` to create a table named `demo`." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 3, | |
"metadata": { | |
"vscode": { | |
"languageId": "cfml" | |
} | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [] | |
} | |
], | |
"source": [ | |
"\n", | |
"queryExecute( sql='CREATE TABLE IF NOT EXISTS demo( id int, name varchar(250))', options={ datasource : ds } );" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"Optionally delete all records in the table." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 4, | |
"metadata": { | |
"vscode": { | |
"languageId": "cfml" | |
} | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [] | |
} | |
], | |
"source": [ | |
"queryExecute( sql='DELETE FROM demo', options={ datasource : ds } );" | |
] | |
}, | |
{ | |
"attachments": {}, | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"Insert some records into the table." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 4, | |
"metadata": { | |
"vscode": { | |
"languageId": "cfml" | |
} | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
"INSERT INTO demo( id, name)\n", | |
"VALUES\n", | |
"(1, 'Test 1'),\n", | |
"(2, 'Test 2'),\n", | |
"(3, 'Test 3'),\n", | |
"(4, 'Test 4')" | |
] | |
} | |
], | |
"source": [ | |
"\n", | |
"sql = \"\n", | |
" INSERT INTO demo( id, name) \n", | |
" VALUES\n", | |
" (1, 'Test 1'),\n", | |
" (2, 'Test 2'),\n", | |
" (3, 'Test 3'),\n", | |
" (4, 'Test 4')\n", | |
"\";\n", | |
"\n", | |
"queryExecute( sql=sql, options={ datasource : ds } );" | |
] | |
}, | |
{ | |
"attachments": {}, | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"Select all the records." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 5, | |
"metadata": { | |
"vscode": { | |
"languageId": "cfml" | |
} | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [] | |
} | |
], | |
"source": [ | |
"myRecordSet = queryExecute( sql='\n", | |
" SELECT id,name \n", | |
" FROM demo\n", | |
"', options={ datasource : ds } );" | |
] | |
}, | |
{ | |
"attachments": {}, | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"Loop over the recordset and output the names." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 7, | |
"metadata": { | |
"vscode": { | |
"languageId": "cfml" | |
} | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
"Test 1\n", | |
"Test 2\n", | |
"Test 3\n", | |
"Test 4" | |
] | |
} | |
], | |
"source": [ | |
"myRecordSet.each( (row) => writeOutput( row.name & chr(10)) )" | |
] | |
}, | |
{ | |
"attachments": {}, | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"Or you can use a `for` loop as well." | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 8, | |
"metadata": { | |
"vscode": { | |
"languageId": "cfml" | |
} | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
"Test 1\n", | |
"Test 2\n", | |
"Test 3\n", | |
"Test 4" | |
] | |
} | |
], | |
"source": [ | |
"for ( row in myRecordSet ) {\n", | |
" writeOutput( row.name & chr(10))\n", | |
"}" | |
] | |
} | |
], | |
"metadata": { | |
"kernelspec": { | |
"display_name": "CFML (Script)", | |
"language": "CFML", | |
"name": "cfscript" | |
}, | |
"language_info": { | |
"file_extension": ".cfm", | |
"mimetype": "text/x-javascript", | |
"name": "CFScript" | |
}, | |
"orig_nbformat": 4 | |
}, | |
"nbformat": 4, | |
"nbformat_minor": 2 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment