· 7 years ago · Dec 22, 2018, 01:36 AM
1{
2 "cells": [
3 {
4 "cell_type": "markdown",
5 "metadata": {},
6 "source": [
7 "## Creating a database for the organizations and Counting them: "
8 ]
9 },
10 {
11 "cell_type": "code",
12 "execution_count": 1,
13 "metadata": {},
14 "outputs": [
15 {
16 "data": {
17 "text/plain": [
18 "'/home/zeski/Documents/Data_Science/SQL/SQL_DBS'"
19 ]
20 },
21 "execution_count": 1,
22 "metadata": {},
23 "output_type": "execute_result"
24 }
25 ],
26 "source": [
27 "import sqlite3\n",
28 "import os\n",
29 "\n",
30 "os.chdir('/home/zeski/Documents/Data_Science/SQL/SQL_DBS')\n",
31 "os.getcwd()"
32 ]
33 },
34 {
35 "cell_type": "code",
36 "execution_count": 2,
37 "metadata": {},
38 "outputs": [],
39 "source": [
40 "conn= sqlite3.connect('organizations.sqlite')\n",
41 "c = conn.cursor()"
42 ]
43 },
44 {
45 "cell_type": "code",
46 "execution_count": 3,
47 "metadata": {},
48 "outputs": [
49 {
50 "data": {
51 "text/plain": [
52 "<sqlite3.Cursor at 0x7f68dc0b9f80>"
53 ]
54 },
55 "execution_count": 3,
56 "metadata": {},
57 "output_type": "execute_result"
58 }
59 ],
60 "source": [
61 "c.execute('''\n",
62 " CREATE TABLE IF NOT EXISTS Counts(org TEXT, count INTEGER)\n",
63 "''')"
64 ]
65 },
66 {
67 "cell_type": "code",
68 "execution_count": 4,
69 "metadata": {},
70 "outputs": [],
71 "source": [
72 "fname= input('Enter file name: ')\n",
73 "\n",
74 "if (len(fname)<1): fname = 'mbox.txt'\n",
75 "fh = open(fname)\n",
76 "orgas= []\n",
77 "for line in fh:\n",
78 " if not line.startswith('From: '): continue\n",
79 " piece = line.strip('\\n').split('@')\n",
80 " org = piece[1]\n",
81 " #orgas.append(piece[1])\n",
82 " c.execute('''\n",
83 " SELECT count FROM Counts WHERE org = ? \n",
84 " ''', (org,))\n",
85 " row = c.fetchone()\n",
86 "\n",
87 " if row is None:\n",
88 " c.execute('''\n",
89 " INSERT INTO Counts (org, count)\n",
90 " VALUES(?, 1)\n",
91 " ''', (org,))\n",
92 " else:\n",
93 " c.execute('''\n",
94 " UPDATE Counts SET count = count +1 WHERE org = ?\n",
95 " ''',(org,))\n",
96 "conn.commit()"
97 ]
98 },
99 {
100 "cell_type": "code",
101 "execution_count": 5,
102 "metadata": {},
103 "outputs": [
104 {
105 "data": {
106 "text/plain": [
107 "[('iupui.edu', 536),\n",
108 " ('umich.edu', 491),\n",
109 " ('indiana.edu', 178),\n",
110 " ('caret.cam.ac.uk', 157),\n",
111 " ('vt.edu', 110),\n",
112 " ('uct.ac.za', 96),\n",
113 " ('media.berkeley.edu', 56),\n",
114 " ('ufp.pt', 28),\n",
115 " ('gmail.com', 25),\n",
116 " ('et.gatech.edu', 17),\n",
117 " ('txstate.edu', 17),\n",
118 " ('whitman.edu', 17),\n",
119 " ('lancaster.ac.uk', 14),\n",
120 " ('bu.edu', 14),\n",
121 " ('stanford.edu', 12),\n",
122 " ('unicon.net', 9),\n",
123 " ('loi.nl', 9),\n",
124 " ('rsmart.com', 8),\n",
125 " ('ucdavis.edu', 1),\n",
126 " ('fhda.edu', 1),\n",
127 " ('utoronto.ca', 1)]"
128 ]
129 },
130 "execution_count": 5,
131 "metadata": {},
132 "output_type": "execute_result"
133 }
134 ],
135 "source": [
136 "sql = 'SELECT * FROM Counts ORDER BY count DESC'\n",
137 "\n",
138 "c.execute(sql)\n",
139 "c.fetchall()"
140 ]
141 },
142 {
143 "cell_type": "code",
144 "execution_count": 6,
145 "metadata": {},
146 "outputs": [],
147 "source": [
148 "c.close()\n",
149 "conn.close()"
150 ]
151 }
152 ],
153 "metadata": {
154 "kernelspec": {
155 "display_name": "Python 3",
156 "language": "python",
157 "name": "python3"
158 },
159 "language_info": {
160 "codemirror_mode": {
161 "name": "ipython",
162 "version": 3
163 },
164 "file_extension": ".py",
165 "mimetype": "text/x-python",
166 "name": "python",
167 "nbconvert_exporter": "python",
168 "pygments_lexer": "ipython3",
169 "version": "3.6.5"
170 }
171 },
172 "nbformat": 4,
173 "nbformat_minor": 2
174}