1"use strict";(globalThis.webpackChunkmikes_dev_notebook=globalThis.webpackChunkmikes_dev_notebook||[]).push([[954],{581738(n,e,s){s.r(e),s.d(e,{assets:()=>l,contentTitle:()=>c,default:()=>h,frontMatter:()=>d,metadata:()=>r,toc:()=>a});const r=JSON.parse('{"id":"IoT-and-Machine-Learning/ML/2023-06-30-sql-in-data-science-advanced/index","title":"SQL in Data Science - Slightly more Advanced Queries","description":"Cheat Sheet using Psycopg2 to interact with PostgreSQL Databases","source":"@site/docs/IoT-and-Machine-Learning/ML/2023-06-30-sql-in-data-science-advanced/index.md","sourceDirName":"IoT-and-Machine-Learning/ML/2023-06-30-sql-in-data-science-advanced","slug":"/IoT-and-Machine-Learning/ML/2023-06-30-sql-in-data-science-advanced/2023-06-30","permalink":"/docs/IoT-and-Machine-Learning/ML/2023-06-30-sql-in-data-science-advanced/2023-06-30","draft":false,"unlisted":false,"editUrl":"https://github.com/facebook/docusaurus/tree/main/packages/create-docusaurus/templates/shared/docs/IoT-and-Machine-Learning/ML/2023-06-30-sql-in-data-science-advanced/index.md","tags":[{"inline":true,"label":"Python","permalink":"/docs/tags/python"},{"inline":true,"label":"Machine Learning","permalink":"/docs/tags/machine-learning"},{"inline":true,"label":"Cheating","permalink":"/docs/tags/cheating"}],"version":"current","sidebarPosition":4370,"frontMatter":{"sidebar_position":4370,"slug":"2023-06-30","title":"SQL in Data Science - Slightly more Advanced Queries","authors":"mpolinowski","tags":["Python","Machine Learning","Cheating"],"description":"Cheat Sheet using Psycopg2 to interact with PostgreSQL Databases"},"sidebar":"tutorialSidebar","previous":{"title":"SQL in Data Science - Machine Learning","permalink":"/docs/IoT-and-Machine-Learning/ML/2023-07-02-sql-in-data-science-ml/2023-07-02"},"next":{"title":"SQL in Data Science - The Basics using Python","permalink":"/docs/IoT-and-Machine-Learning/ML/2023-06-27-sql-in-data-science-basics/2023-06-27"}}');var i=s(474848),t=s(28453);const d={sidebar_position:4370,slug:"2023-06-30",title:"SQL in Data Science - Slightly more Advanced Queries",authors:"mpolinowski",tags:["Python","Machine Learning","Cheating"],description:"Cheat Sheet using Psycopg2 to interact with PostgreSQL Databases"},c="SQL in Data Science",l={},a=[{value:"SQL and Python",id:"sql-and-python",level:2},{value:"Subqueries",id:"subqueries",level:3},{value:"Create Table from CSV",id:"create-table-from-csv",level:4},{value:"SELECT WHERE Subquery",id:"select-where-subquery",level:4},{value:"Joins",id:"joins",level:3},{value:"INNER JOIN",id:"inner-join",level:4},{value:"JOIN Aliases",id:"join-aliases",level:4},{value:"Multi INNER JOIN",id:"multi-inner-join",level:4},{value:"Self JOIN",id:"self-join",level:4},{value:"LEFT JOIN",id:"left-join",level:4},{value:"FULL OUTER JOIN",id:"full-outer-join",level:4},{value:"UNION",id:"union",level:4},{value:"Formating",id:"formating",level:3},{value:"Text Strings - Concatenations",id:"text-strings---concatenations",level:4},{value:"Text Strings - Trim",id:"text-strings---trim",level:4},{value:"Text Strings - SUBSTR, LOWER, UPPER",id:"text-strings---substr-lower-upper",level:4},{value:"Text Strings - STRFTIME",id:"text-strings---strftime",level:4},{value:"Case Statements",id:"case-statements",level:3},{value:"CASE WHEN THEN ELSE - One-Hot Encoding",id:"case-when-then-else---one-hot-encoding",level:4},{value:"CASE WHEN AND THEN ELSE - Multi-Classification",id:"case-when-and-then-else---multi-classification",level:4}];function o(n){const e={a:"a",blockquote:"blockquote",code:"code",h1:"h1",h2:"h2",h3:"h3",h4:"h4",header:"header",img:"img",li:"li",ol:"ol",p:"p",pre:"pre",strong:"strong",table:"table",tbody:"tbody",td:"td",th:"th",thead:"thead",tr:"tr",ul:"ul",...(0,t.R)(),...n.components};return(0,i.jsxs)(i.Fragment,{children:[(0,i.jsx)(e.p,{children:(0,i.jsx)(e.img,{alt:"Guangzhou, China",src:s(955386).A+"",width:"1500",height:"581"})}),"\n",(0,i.jsxs)(e.ul,{children:["\n",(0,i.jsxs)(e.li,{children:[(0,i.jsx)(e.a,{href:"#sql-in-data-science",children:"SQL in Data Science"}),"\n",(0,i.jsxs)(e.ul,{children:["\n",(0,i.jsxs)(e.li,{children:[(0,i.jsx)(e.a,{href:"#sql-and-python",children:"SQL and Python"}),"\n",(0,i.jsxs)(e.ul,{children:["\n",(0,i.jsxs)(e.li,{children:[(0,i.jsx)(e.a,{href:"#subqueries",children:"Subqueries"}),"\n",(0,i.jsxs)(e.ul,{children:["\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#create-table-from-csv",children:"Create Table from CSV"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#select-where-subquery",children:"SELECT WHERE Subquery"})}),"\n"]}),"\n"]}),"\n",(0,i.jsxs)(e.li,{children:[(0,i.jsx)(e.a,{href:"#joins",children:"Joins"}),"\n",(0,i.jsxs)(e.ul,{children:["\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#inner-join",children:"INNER JOIN"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#join-aliases",children:"JOIN Aliases"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#multi-inner-join",children:"Multi INNER JOIN"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#self-join",children:"Self JOIN"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#left-join",children:"LEFT JOIN"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#full-outer-join",children:"FULL OUTER JOIN"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#union",children:"UNION"})}),"\n"]}),"\n"]}),"\n",(0,i.jsxs)(e.li,{children:[(0,i.jsx)(e.a,{href:"#formating",children:"Formating"}),"\n",(0,i.jsxs)(e.ul,{children:["\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#text-strings---concatenations",children:"Text Strings - Concatenations"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#text-strings---trim",children:"Text Strings - Trim"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#text-strings---substr-lower-upper",children:"Text Strings - SUBSTR, LOWER, UPPER"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#text-strings---strftime",children:"Text Strings - STRFTIME"})}),"\n"]}),"\n"]}),"\n",(0,i.jsxs)(e.li,{children:[(0,i.jsx)(e.a,{href:"#case-statements",children:"Case Statements"}),"\n",(0,i.jsxs)(e.ul,{children:["\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#case-when-then-else---one-hot-encoding",children:"CASE WHEN THEN ELSE - One-Hot Encoding"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"#case-when-and-then-else---multi-classification",children:"CASE WHEN AND THEN ELSE - Multi-Classification"})}),"\n"]}),"\n"]}),"\n"]}),"\n"]}),"\n"]}),"\n"]}),"\n"]}),"\n",(0,i.jsx)(e.p,{children:(0,i.jsx)(e.a,{href:"https://github.com/mpolinowski/sql-for-data-science/tree/master/01_basic_scripts",children:"Github Repository"})}),"\n",(0,i.jsx)(e.header,{children:(0,i.jsx)(e.h1,{id:"sql-in-data-science",children:"SQL
1in Data Science"})}),"\n",(0,i.jsxs)(e.ol,{children:["\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"/docs/IoT-and-Machine-Learning/ML/2023-06-27-sql-in-data-science-basics/2023-06-27",children:"SQL in Data Science - The Basics using Python"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"/docs/IoT-and-Machine-Learning/ML/2023-06-30-sql-in-data-science-advanced/2023-06-30",children:"SQL in Data Science - Slightly more Advanced Queries"})}),"\n",(0,i.jsx)(e.li,{children:(0,i.jsx)(e.a,{href:"/docs/IoT-and-Machine-Learning/ML/2023-07-02-sql-in-data-science-ml/2023-07-02",children:"SQL in Data Science - Machine Learning"})}),"\n"]}),"\n",(0,i.jsx)(e.h2,{id:"sql-and-python",children:"SQL and Python"}),"\n",(0,i.jsx)(e.h3,{id:"subqueries",children:"Subqueries"}),"\n",(0,i.jsx)(e.p,{children:"Use Subqueries to trim down the dataset before running your search by a filter condition from a different SQL table. For this I split up the Penguins dataset used in part 1 into two tables:"}),"\n",(0,i.jsx)(e.h4,{id:"create-table-from-csv",children:"Create Table from CSV"}),"\n",(0,i.jsx)(e.p,{children:(0,i.jsx)(e.strong,{children:"Table A"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"uid"}),(0,i.jsx)(e.th,{children:"species"}),(0,i.jsx)(e.th,{children:"island"}),(0,i.jsx)(e.th,{children:"sex"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0985a3"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"MALE"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0985a4"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"FEMALE"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0985a5"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"FEMALE"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0985a7"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"FEMALE"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0985a8"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"MALE"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"..."}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{})]})]})]}),"\n",(0,i.jsx)(e.p,{children:(0,i.jsx)(e.strong,{children:"Table B"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"uid"}),(0,i.jsx)(e.th,{children:"culmen_length_mm"}),(0,i.jsx)(e.th,{children:"culmen_depth_mm"}),(0,i.jsx)(e.th,{children:"flipper_length_mm"}),(0,i.jsx)(e.th,{children:"body_mass_g"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0985a3"}),(0,i.jsx)(e.td,{children:"39.1"}),(0,i.jsx)(e.td,{children:"18.7"}),(0,i.jsx)(e.td,{children:"181"}),(0,i.jsx)(e.td,{children:"3750"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0985a4"}),(0,i.jsx)(e.td,{children:"39.5"}),(0,i.jsx)(e.td,{children:"17.4"}),(0,i.jsx)(e.td,{children:"186"}),(0,i.jsx)(e.td,{children:"3800"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0985a5"}),(0,i.jsx)(e.td,{children:"40.3"}),(0,i.jsx)(e.td,{children:"18.0"}),(0,i.jsx)(e.td,{children:"195"}),(0,i.jsx)(e.td,{children:"3250"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0985a7"}),(0,i.jsx)(e.td,{children:"36.7"}),(0,i.jsx)(e.td,{children:"19.3"}),(0,i.jsx)(e.td,{children:"193"}),(0,i.jsx)(e.td,{children:"3450"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0985a8"}),(0,i.jsx)(e.td,{children:"39.3"}),(0,i.jsx)(e.td,{children:"20.6"}),(0,i.jsx)(e.td,{children:"190"}),(0,i.jsx)(e.td,{children:"3650"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"..."}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{})]})]})]}),"\n",(0,i.jsx)(e.p,{children:"Run the following functions for both CSV files, respectively:"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"data_a = pd.read_csv ('dataset_penguins_a.csv') \ndf_a = pd.DataFrame(data_a)\n\n\ndef create_table():\n conn = None\n try:\n # read database configuration\n params = config()\n # connect to the PostgreSQL database\n conn = psycopg2.connect(**params)\n # create a new cursor\n cursor = conn.cursor(
1)\n # create the table\n cursor.execute('''\n\t\tCREATE TABLE penguins_a (\n\t\t\tuid CHAR(25) primary key,\n\t\t\tspecies VARCHAR(10),\n island VARCHAR(10),\n\t\t\tsex VARCHAR(10)\n\t\t\t)\n ''')\n conn.commit()\n # Close communication with the PostgreSQL database\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n"})}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def add_data():\n conn = None\n try:\n # read database configuration\n params = config()\n # connect to the PostgreSQL database\n conn = psycopg2.connect(**params)\n # create a new cursor\n cursor = conn.cursor()\n # insert data\n for row in df_a.itertuples():\n cursor.execute('INSERT INTO penguins_a (uid, species, island, culmen_length_mm, culmen_depth_mm, flipper_length_mm, body_mass_g, sex) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', (row.uid, row.species, row.island, row.culmen_length_mm, row.culmen_depth_mm, row.flipper_length_mm, row.body_mass_g, row.sex))\n # Commit the changes to the database\n conn.commit()\n # Close communication with the PostgreSQL database\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n"})}),"\n",(0,i.jsx)(e.h4,{id:"select-where-subquery",children:"SELECT WHERE Subquery"}),"\n",(0,i.jsx)(e.p,{children:"We can now query against table A after fulfilling a filter condition from table B:"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:'def get_penguins_subquery(unit, condition):\n """ query data from the penguins table """\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor()\n query = "SELECT uid, island, species FROM penguins_a WHERE uid IN (SELECT uid FROM penguins_b WHERE {0} {1})".format(unit, condition)\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == \'__main__\':\n # return specimen that weight more than 6kg\n results = get_penguins_subquery(unit=\'body_mass_g\', condition=\'>6000\')\n print(results)\n'})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"UID"}),(0,i.jsx)(e.th,{children:"Island"}),(0,i.jsx)(e.th,{children:"Species"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e098690"}),(0,i.jsx)(e.td,{children:"Biscoe"}),(0,i.jsx)(e.td,{children:"Gentoo"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"649bc5d39b21a1b93e0986a0"}),(0,i.jsx)(e.td,{children:"Biscoe"}),(0,i.jsx)(e.td,{children:"Gentoo"})]})]})]}),"\n",(0,i.jsx)(e.h3,{id:"joins",children:"Joins"}),"\n",(0,i.jsx)(e.h4,{id:"inner-join",children:"INNER JOIN"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_penguins_inner_join(column1, column2, column3):\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor()\n query = \"SELECT {0}, {1}, {2} FROM penguins_a INNER JOIN penguins_b ON penguins_a.uid = penguins_b.uid)\".format(column1, column2, column3)\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n # return species and island from table A and body mass from table B\n results = get_penguins_inner_join(column1='species', column2='island', column3='body_mass_g')\n print(results)\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Species"}),(0,i.jsx)(e.th,{children:"Island"}),(0,i.jsx)(e.th,{children:"Body Mass"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3750"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3800"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3250"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3450"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3650"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"..."}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{})]})]})]}),"\n",(0,i.jsx)(e.h4,{id:"join-aliases",children:"JOIN Aliases"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_penguins_alias_join(column1, column2, column3):\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor(
1)\n query = \"SELECT {0}, {1}, {2} FROM penguins_a AS a, penguins_b AS b WHERE a.uid = b.uid\".format(column1, column2, column3)\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n # return species and island from table A and body mass from table B\n results = get_penguins_alias_join(column1='species', column2='island', column3='body_mass_g')\n print(results)\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Species"}),(0,i.jsx)(e.th,{children:"Island"}),(0,i.jsx)(e.th,{children:"Body Mass"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3750"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3800"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3250"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3450"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3650"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"..."}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{})]})]})]}),"\n",(0,i.jsx)(e.h4,{id:"multi-inner-join",children:"Multi INNER JOIN"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_penguins_multi_inner_join(column1, column2, column3):\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor()\n query = \"SELECT {0}, {1}, {2} FROM ((penguins_a pa INNER JOIN penguins_b pb ON pa.uid = pb.uid) INNER JOIN penguins p ON pa.uid = p.uid)\".format(column1, column2, column3)\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n # return species from table A, island from the original table and body mass from table B\n results = get_penguins_multi_inner_join(column1='pa.species', column2='p.island', column3='pb.body_mass_g')\n print(results)\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Species"}),(0,i.jsx)(e.th,{children:"Island"}),(0,i.jsx)(e.th,{children:"Body Mass"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3750"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3800"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3250"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3450"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3650"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"..."}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{})]})]})]}),"\n",(0,i.jsx)(e.h4,{id:"self-join",children:"Self JOIN"}),"\n",(0,i.jsx)(e.p,{children:"AND statement for a second column to join on... but my tables only have one index in common... ordered by body mass:"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_penguins_self_join(column1, column2, column3):\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor(
1)\n query = \"SELECT {0} AS Species, {1} AS Island, {2} AS Mass FROM penguins_a a, penguins_b b WHERE a.uid = b.uid AND a.uid = b.uid ORDER BY b.body_mass_g\".format(column1, column2, column3)\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n # return species and island from table A and body mass from table B\n results = get_penguins_self_join(column1='a.species', column2='a.island', column3='b.body_mass_g')\n df = pd.DataFrame(results, columns=['Species', 'Island', 'Body Mass'])\n df.head()\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Species"}),(0,i.jsx)(e.th,{children:"Island"}),(0,i.jsx)(e.th,{children:"Body Mass"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"Chinstrap"}),(0,i.jsx)(e.td,{children:"Dream"}),(0,i.jsx)(e.td,{children:"2700"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Biscoe"}),(0,i.jsx)(e.td,{children:"2850"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Biscoe"}),(0,i.jsx)(e.td,{children:"2850"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"2900"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Biscoe"}),(0,i.jsx)(e.td,{children:"2900"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"..."}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{})]})]})]}),"\n",(0,i.jsx)(e.h4,{id:"left-join",children:"LEFT JOIN"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_penguins_left_join(column1, column2, column3):\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor()\n query = \"SELECT {0}, {1}, {2} FROM penguins_a a LEFT JOIN penguins_b b ON a.uid = b.uid\".format(column1, column2, column3)\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n # return species and island from table A and body mass from table B\n results = get_penguins_left_join(column1='a.species', column2='a.island', column3='b.body_mass_g')\n df = pd.DataFrame(results, columns=['Species', 'Island', 'Body Mass'])\n df.head()\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Species"}),(0,i.jsx)(e.th,{children:"Island"}),(0,i.jsx)(e.th,{children:"Body Mass"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3750"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3800"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3250"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3450"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3650"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"..."}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{})]})]})]}),"\n",(0,i.jsxs)(e.p,{children:["There is no difference between right and left joins - they all return the same as a full outer join as both tables contain the same ",(0,i.jsx)(e.code,{children:"uid"})," rows. There will be a difference once one row is missing in the other table - then it will either be dropped or all values from that table be listed as NULL depending on you approaching it from the left or the right."]}),"\n",(0,i.jsx)(e.p,{children:(0,i.jsx)(e.img,{alt:"SQL Joins",src:s(590778).A+"",width:"1850",height:"1080"})}),"\n",(0,i.jsx)(e.h4,{id:"full-outer-join",children:"FULL OUTER JOIN"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_penguins_full_join(column1, column2, column3):\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor(
1)\n query = \"SELECT {0}, {1}, {2} FROM penguins_a a FULL OUTER JOIN penguins_b b ON a.uid = b.uid\".format(column1, column2, column3)\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n # return species and island from table A and body mass from table B\n results = get_penguins_full_join(column1='a.species', column2='a.island', column3='b.body_mass_g')\n df = pd.DataFrame(results, columns=['Species', 'Island', 'Body Mass'])\n df.head()\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Species"}),(0,i.jsx)(e.th,{children:"Island"}),(0,i.jsx)(e.th,{children:"Body Mass"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3750"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3800"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3250"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3450"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"3650"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"..."}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{}),(0,i.jsx)(e.td,{})]})]})]}),"\n",(0,i.jsx)(e.h4,{id:"union",children:"UNION"}),"\n",(0,i.jsxs)(e.p,{children:["UNION does not really work here. It could be used if another survey comes in and you want all the ID that are associated to specimens of a group - species, island, sex... Select ",(0,i.jsx)(e.code,{children:"uid"})," in both tables and filter for the group you are looking for - this returns all the IDs from both tables:"]}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:'def get_penguins_union():\n """ query data from the penguins table """\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor()\n query = "SELECT uid FROM penguins_a1 WHERE island=\'Dream\' UNION SELECT uid FROM penguins_a2 WHERE island=\'Dream\'"\n print(query)\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n'})}),"\n",(0,i.jsx)(e.h3,{id:"formating",children:"Formating"}),"\n",(0,i.jsx)(e.h4,{id:"text-strings---concatenations",children:"Text Strings - Concatenations"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_penguins_concat_strings(column1, column2, filter):\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor()\n query = \"SELECT {0}, {1}, {0} || ' (' || {1} || ')' FROM penguins_a WHERE sex = '{2}'\".format(column1, column2, filter)\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n results = get_penguins_concat_strings(column1='species', column2='island', filter='MALE')\n df = pd.DataFrame(results, columns=['Species', 'Island', 'Concat'])\n df.head()\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Species"}),(0,i.jsx)(e.th,{children:"Island"}),(0,i.jsx)(e.th,{children:"Concat"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"Adelie (Torgersen)"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"Adelie (Torgersen)"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"Adelie (Torgersen)"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"Adelie (Torgersen)"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"Adelie (Torgersen)"})]})]})]}),"\n",(0,i.jsx)(e.h4,{id:"text-strings---trim",children:"Text Strings - Trim"}),"\n",(0,i.jsxs)(e.blockquote,{children:["\n",(0,i.jsxs)(e.p,{children:[(0,i.jsx)(e.code,{children:"TRIM"}),", ",(0,i.jsx)(e.code,{children:"RTRIM"}),", ",(0,i.jsx)(e.code,{children:"LTRIM"}
1)," to remove leading or trailing spaces from a string."]}),"\n"]}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-bash",children:"SELECT TRIM('#! ' FROM ' #SQL Tutorial! ') AS TrimmedString;\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsx)(e.tr,{children:(0,i.jsx)(e.th,{children:"TrimmedString"})})}),(0,i.jsx)(e.tbody,{children:(0,i.jsx)(e.tr,{children:(0,i.jsx)(e.td,{children:"SQL Tutorial"})})})]}),"\n",(0,i.jsx)(e.h4,{id:"text-strings---substr-lower-upper",children:"Text Strings - SUBSTR, LOWER, UPPER"}),"\n",(0,i.jsx)(e.p,{children:"Generate IDs from Substrings - e.g. Island Codes:"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_penguins_sub_strings(column1, column2, filter):\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor()\n query = \"SELECT {0}, {1}, UPPER(SUBSTR({1},1,3)) FROM penguins_a\".format(column1, column2, filter)\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n results = get_penguins_sub_strings(column1='species', column2='island')\n df = pd.DataFrame(results, columns=['Species', 'Island', 'Location Code'])\n df.head()\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Species"}),(0,i.jsx)(e.th,{children:"Island"}),(0,i.jsx)(e.th,{children:"Location Code"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"TOR"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"TOR"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"TOR"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"TOR"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"TOR"})]})]})]}),"\n",(0,i.jsx)(e.h4,{id:"text-strings---strftime",children:"Text Strings - STRFTIME"}),"\n",(0,i.jsx)(e.p,{children:"The penguins dataset does not contain timestamps ~ let's import another one that does:"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"tweet_data = pd.read_csv ('datasets_tweets.csv') \ndf = pd.DataFrame(tweet_data)\ndf = df.drop(['negativereason', 'negativereason_confidence', 'text', 'tweet_location', 'tweet_coord', 'negativereason_gold', 'airline_sentiment_gold'], axis=1)\ndf = df.drop_duplicates(subset=['tweet_id'], keep='first')\n\n \t \t \t \t \t \t \t \t \t \t \t \t \t \t\ndef create_table():\n conn = None\n try:\n # read database configuration\n params = config()\n # connect to the PostgreSQL database\n conn = psycopg2.connect(**params)\n # create a new cursor\n cursor = conn.cursor()\n # create the table\n cursor.execute('''\n\t\tCREATE TABLE tweets (\n\t\t\ttweet_id INT primary key,\n\t\t\tairline_sentiment VARCHAR,\n airline_sentiment_confidence FLOAT,\n airline VARCHAR,\n name VARCHAR,\n retweet_count INT,\n tweet_created TIMESTAMP,\n user_timezone VARCHAR\n\t\t\t)\n ''')\n conn.commit()\n # Close communication with the PostgreSQL database\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n"})}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def add_data():\n conn = None\n try:\n # read database configuration\n params = config()\n # connect to the PostgreSQL database\n conn = psycopg2.connect(**params)\n # create a new cursor\n cursor = conn.cursor(
1)\n # insert data\n for row in df.itertuples():\n cursor.execute('INSERT INTO tweets (tweet_id, airline_sentiment, airline_sentiment_confidence, airline, name, retweet_count, tweet_created, user_timezone) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', (row.tweet_id, row.airline_sentiment, row.airline_sentiment_confidence, row.airline, row.name ,row.retweet_count, row.tweet_created, row.user_timezone))\n # Commit the changes to the database\n conn.commit()\n # Close communication with the PostgreSQL database\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"tweet_id"}),(0,i.jsx)(e.th,{children:"airline_sentiment"}),(0,i.jsx)(e.th,{children:"airline_sentiment_confidence"}),(0,i.jsx)(e.th,{children:"airline"}),(0,i.jsx)(e.th,{children:"name"}),(0,i.jsx)(e.th,{children:"retweet_count"}),(0,i.jsx)(e.th,{children:"tweet_created"}),(0,i.jsx)(e.th,{children:"user_timezone"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"570306133677760513"}),(0,i.jsx)(e.td,{children:"neutral"}),(0,i.jsx)(e.td,{children:"1.0000"}),(0,i.jsx)(e.td,{children:"Virgin America"}),(0,i.jsx)(e.td,{children:"cairdin"}),(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"2015-02-24 11:35:52 -0800"}),(0,i.jsx)(e.td,{children:"Eastern Time (US & Canada)"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"570301130888122368"}),(0,i.jsx)(e.td,{children:"positive"}),(0,i.jsx)(e.td,{children:"0.3486"}),(0,i.jsx)(e.td,{children:"Virgin America"}),(0,i.jsx)(e.td,{children:"jnardino"}),(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"2015-02-24 11:15:59 -0800"}),(0,i.jsx)(e.td,{children:"Pacific Time (US & Canada)"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"570301083672813571"}),(0,i.jsx)(e.td,{children:"neutral"}),(0,i.jsx)(e.td,{children:"0.6837"}),(0,i.jsx)(e.td,{children:"Virgin America"}),(0,i.jsx)(e.td,{children:"yvonnalynn"}),(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"2015-02-24 11:15:48 -0800"}),(0,i.jsx)(e.td,{children:"Central Time (US & Canada)"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"570301031407624196"}),(0,i.jsx)(e.td,{children:"negative"}),(0,i.jsx)(e.td,{children:"1.0000"}),(0,i.jsx)(e.td,{children:"Virgin America"}),(0,i.jsx)(e.td,{children:"jnardino"}),(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"2015-02-24 11:15:36 -0800"}),(0,i.jsx)(e.td,{children:"Pacific Time (US & Canada)"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"570300817074462722"}),(0,i.jsx)(e.td,{children:"negative"}),(0,i.jsx)(e.td,{children:"1.0000"}),(0,i.jsx)(e.td,{children:"Virgin America"}),(0,i.jsx)(e.td,{children:"jnardino"}),(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"2015-02-24 11:14:45 -0800"}),(0,i.jsx)(e.td,{children:"Pacific Time (US & Canada)"})]})]})]}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_tweets_datetime_strings():\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor()\n query = \"SELECT tweet_created, EXTRACT(ISOYEAR FROM tweet_created), EXTRACT(MONTH FROM tweet_created), EXTRACT(Day FROM tweet_created), EXTRACT(DOW FROM tweet_created), CURRENT_DATE - tweet_created FROM tweets;\"\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n results = get_tweets_datetime_strings()\n df = pd.DataFrame(results, columns=['Timestamp', 'Year', 'Month', 'Day', 'Day of the Week', 'Days Since'])\n df.head()\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Timestamp"}),(0,i.jsx)(e.th,{children:"Year"}),(0,i.jsx)(e.th,{children:"Month"}),(0,i.jsx)(e.th,{children:"Day"}),(0,i.jsx)(e.th,{children:"Day of the Week"}),(0,i.jsx)(e.th,{children:"Days Since"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"2015-02-24 11:35:52"}),(0,i.jsx)(e.td,{children:"2015"}),(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"24"}),(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"3048 days 12:24:08"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"2015-02-24 11:15:59"}),(0,i.jsx)(e.td,{children:"2015"}),(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"24"}),(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"3048 days 12:44:01"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"2015-02-24 11:15:48"}),(0,i.jsx)(e.td,{children:"2015"}),(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"24"}),(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"3048 days 12:44:12"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"2015-02-24 11:15:36"}),(0,i.jsx)(e.td,{children:"2015"}),(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"24"}),(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"3048 days 12:44:24"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"2015-02-24 11:14:45"}),(0,i.jsx)(e.td,{children:"2015"}),(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"24"}),(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"3048 days 12:45:15"})]})]})]}),"\n",(0,i.jsx)(e.h3,{id:"case-statements",children:"Case Statements"}),"\n",(0,i.jsx)(e.h4,{id:"case-when-then-else---one-hot-encoding",children:"CASE WHEN THEN ELSE - One-Hot Encoding"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_penguins_binary_classification():\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor(
1)\n query = \"SELECT airline, airline_sentiment, CASE airline_sentiment WHEN 'negative' THEN 0 ELSE 1 END negative FROM tweets\"\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n results = get_penguins_binary_classification()\n df = pd.DataFrame(results, columns=['Airline', 'Sentiment', 'Binary Sentiment'])\n df.head()\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Airline"}),(0,i.jsx)(e.th,{children:"Sentiment"}),(0,i.jsx)(e.th,{children:"Binary Sentiment"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"Virgin America"}),(0,i.jsx)(e.td,{children:"neutral"}),(0,i.jsx)(e.td,{children:"1"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"Virgin America"}),(0,i.jsx)(e.td,{children:"positive"}),(0,i.jsx)(e.td,{children:"1"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"Virgin America"}),(0,i.jsx)(e.td,{children:"neutral"}),(0,i.jsx)(e.td,{children:"1"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"Virgin America"}),(0,i.jsx)(e.td,{children:"negative"}),(0,i.jsx)(e.td,{children:"0"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"Virgin America"}),(0,i.jsx)(e.td,{children:"negative"}),(0,i.jsx)(e.td,{children:"0"})]})]})]}),"\n",(0,i.jsx)(e.h4,{id:"case-when-and-then-else---multi-classification",children:"CASE WHEN AND THEN ELSE - Multi-Classification"}),"\n",(0,i.jsx)(e.pre,{children:(0,i.jsx)(e.code,{className:"language-py",children:"def get_penguins_multi_classification():\n \"\"\" query data from the penguins table \"\"\"\n results = []\n conn = None\n try:\n params = config()\n conn = psycopg2.connect(**params)\n cursor = conn.cursor()\n query = \"SELECT species, island, sex, CASE WHEN body_mass_g < 3000 THEN 'A' WHEN body_mass_g >= 3001 AND body_mass_g <=6000 THEN 'B' WHEN body_mass_g >= 6000 THEN 'C' ELSE 'something went wrong' END mass_class FROM penguins\"\n cursor.execute(query)\n penguins = cursor.fetchall()\n \n for penguin in penguins:\n results.append(penguin)\n\n cursor.close()\n except (Exception, psycopg2.DatabaseError) as error:\n print(error)\n finally:\n if conn is not None:\n return(results)\n conn.close()\n\n\nif __name__ == '__main__':\n results = get_penguins_multi_classification()\n df = pd.DataFrame(results, columns=['Species', 'Island', 'Sex', 'Mass Class'])\n df.head()\n"})}),"\n",(0,i.jsxs)(e.table,{children:[(0,i.jsx)(e.thead,{children:(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.th,{}),(0,i.jsx)(e.th,{children:"Species"}),(0,i.jsx)(e.th,{children:"Island"}),(0,i.jsx)(e.th,{children:"Sex"}),(0,i.jsx)(e.th,{children:"Mass Class"})]})}),(0,i.jsxs)(e.tbody,{children:[(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"0"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"MALE"}),(0,i.jsx)(e.td,{children:"B"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"1"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"FEMALE"}),(0,i.jsx)(e.td,{children:"B"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"2"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"FEMALE"}),(0,i.jsx)(e.td,{children:"B"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"3"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"FEMALE"}),(0,i.jsx)(e.td,{children:"B"})]}),(0,i.jsxs)(e.tr,{children:[(0,i.jsx)(e.td,{children:"4"}),(0,i.jsx)(e.td,{children:"Adelie"}),(0,i.jsx)(e.td,{children:"Torgersen"}),(0,i.jsx)(e.td,{children:"MALE"}),(0,i.jsx)(e.td,{children:"B"})]})]})]})]})}function h(n={}){const{wrapper:e}={...(0,t.R)(),...n.components};return e?(0,i.jsx)(e,{...n,children:(0,i.jsx)(o,{...n})}):o(n)}},590778(n,e,s){const r=s.p+"assets/images/SQL_Joins-5c1aba0ef39cd0c7c60aa840192c9663.png";s.d(e,["A",0,r])},955386(n,e,s){const r=s.p+"assets/images/photo-kt443t6d_64hdh43hfh6dgjdfhg4_d-5a33ed1aeac871d5b7a7594cc7d702c8.jpg";s.d(e,["A",0,r])},28453(n,e,s){s.d(e,{R:()=>d,x:()=>c});var r=s(296540);const i={},t=r.createContext(i);function d(n){const e=r.useContext(t);return r.useMemo(function(){return"function"==typeof n?n(e):{...e,...n}},[e,n])}function c(n){let e;return e=n.disableParentContext?"function"==typeof n.components?n.components(i):n.components||i:d(n.components),r.createElement(t.Provider,{value:e},n.children)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.