just a test for sql in jupyter nb

xlwings_test.ipynb

{"cells":[{"attachments":{},"cell_type":"markdown","metadata":{},"source":[" # USE XLWINGS AND SQLITE TO PULL DATA AND QUERY DATABASE\n","\n"," - Import xlwings so we can use Excel to manipulate the data\n","\n"," - Import sqlite3 \n"," \n"," - Import pandas"]},{"cell_type":"code","execution_count":1,"metadata":{},"outputs":[],"source":["import xlwings as xw\n","import sqlite3\n","import pandas as pd"]},{"attachments":{},"cell_type":"markdown","metadata":{},"source":[" ## USE EXCEL AS A DATAVIEWER/EDITOR TO CREATE TABLE\n","\n"," - Create a workbook and sheet to send data to a dataframe\n","\n"," - Type in the data we will use in a database\n","\n"," - Load the data back into python as a pandas dataframe\n","\n"," Note that in excel i have an index column and\n"," I selected all the cells that i wanted to import\n"," before using the load command"]},{"cell_type":"code","execution_count":2,"metadata":{},"outputs":[],"source":["wb = xw.Book()\n","sheet = wb.sheets[0]"]},{"attachments":{},"cell_type":"markdown","metadata":{},"source":[" ### A NOTE ABOUT MY TABLE IN EXCEL\n","\n"," In excel I have an index column and\n"," I selected all the cells that I wanted to import\n"," before using the load command"]},{"cell_type":"code","execution_count":3,"metadata":{},"outputs":[],"source":["df = xw.load(index=True, header=True)"]},{"attachments":{},"cell_type":"markdown","metadata":{},"source":[" We'll print the dataframe just to see that it\n"," looks accurate"]},{"cell_type":"code","execution_count":4,"metadata":{},"outputs":[{"name":"stdout","output_type":"stream","text":["           age          city\n","name                        \n","Juan      23.0         Miami\n","Victoria  34.0  Buenos Aires\n","Mary      43.0      Santiago\n"]}],"source":["print(df)"]},{"cell_type":"markdown","metadata":{},"source":[" Check the type and make sure this loaded from\n"," Excel to python as a pandas dataframe"]},{"cell_type":"code","execution_count":5,"metadata":{},"outputs":[{"data":{"text/plain":["pandas.core.frame.DataFrame"]},"execution_count":5,"metadata":{},"output_type":"execute_result"}],"source":["type(df)"]},{"attachments":{},"cell_type":"markdown","metadata":{},"source":[" ### CLOSE EXCEL AND LOAD DB FILE\n","\n"," - Close excel by clicking the x button in that window\n","\n"," - Use sqlite library to connect the dataframe\n","\n"," - Create a database file called xlwings_test.db\n","\n"," - Load data from df to database"]},{"cell_type":"code","execution_count":6,"metadata":{},"outputs":[],"source":["cnn = sqlite3.connect('xlwings_test.db')\n","df.to_sql('people', cnn)\n","%load_ext sql\n","%sql sqlite:///xlwings_test.db"]},{"cell_type":"markdown","metadata":{},"source":[" ## START USING SQL COMMANDS\n","\n"," Very easy to do in python/jupyter"]},{"cell_type":"code","execution_count":8,"metadata":{},"outputs":[{"name":"stdout","output_type":"stream","text":[" * sqlite:///xlwings_test.db\n","Done.\n"]},{"data":{"text/html":["<table>\n","    <tr>\n","        <th>name</th>\n","        <th>age</th>\n","        <th>city</th>\n","    </tr>\n","    <tr>\n","        <td>Juan</td>\n","        <td>23.0</td>\n","        <td>Miami</td>\n","    </tr>\n","    <tr>\n","        <td>Victoria</td>\n","        <td>34.0</td>\n","        <td>Buenos Aires</td>\n","    </tr>\n","    <tr>\n","        <td>Mary</td>\n","        <td>43.0</td>\n","        <td>Santiago</td>\n","    </tr>\n","</table>"],"text/plain":["[('Juan', 23.0, 'Miami'),\n"," ('Victoria', 34.0, 'Buenos Aires'),\n"," ('Mary', 43.0, 'Santiago')]"]},"execution_count":8,"metadata":{},"output_type":"execute_result"}],"source":["%%sql\n","SELECT *\n","FROM people"]},{"cell_type":"code","execution_count":9,"metadata":{},"outputs":[{"name":"stdout","output_type":"stream","text":[" * sqlite:///xlwings_test.db\n","Done.\n"]},{"data":{"text/html":["<table>\n","    <tr>\n","        <th>count(*)</th>\n","    </tr>\n","    <tr>\n","        <td>3</td>\n","    </tr>\n","</table>"],"text/plain":["[(3,)]"]},"execution_count":9,"metadata":{},"output_type":"execute_result"}],"source":["%%sql\n","SELECT count(*)\n","FROM people"]},{"cell_type":"code","execution_count":10,"metadata":{},"outputs":[{"name":"stdout","output_type":"stream","text":[" * sqlite:///xlwings_test.db\n","Done.\n"]},{"data":{"text/html":["<table>\n","    <tr>\n","        <th>age_sum</th>\n","    </tr>\n","    <tr>\n","        <td>100.0</td>\n","    </tr>\n","</table>"],"text/plain":["[(100.0,)]"]},"execution_count":10,"metadata":{},"output_type":"execute_result"}],"source":["%%sql\n","SELECT sum(age) as 'age_sum'\n","FROM people"]}],"metadata":{"kernelspec":{"display_name":".venv","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.10.0 (tags/v3.10.0:b494f59, Oct  4 2021, 19:00:18) [MSC v.1929 64 bit (AMD64)]"},"orig_nbformat":4,"vscode":{"interpreter":{"hash":"9a71a39cb9f04ce881bb5121e48c08d5a4168dfde49d27a6a046cb3d7e0b5c8a"}}},"nbformat":4,"nbformat_minor":2}