· 7 years ago · Nov 18, 2018, 01:42 PM
1{
2 "cells": [
3 {
4 "cell_type": "markdown",
5 "metadata": {},
6 "source": [
7 "# Practice using sqlite3"
8 ]
9 },
10 {
11 "cell_type": "raw",
12 "metadata": {},
13 "source": [
14 "# Connecting to Education Database"
15 ]
16 },
17 {
18 "cell_type": "code",
19 "execution_count": null,
20 "metadata": {},
21 "outputs": [],
22 "source": [
23 "import sqlite3"
24 ]
25 },
26 {
27 "cell_type": "code",
28 "execution_count": null,
29 "metadata": {},
30 "outputs": [],
31 "source": [
32 "connection = sqlite3.connect('education.db') # Database will be created since it does not already exist\n",
33 "cursor = connection.cursor()\n",
34 "\n",
35 "print (\"Opened database successfully\");"
36 ]
37 },
38 {
39 "cell_type": "code",
40 "execution_count": null,
41 "metadata": {},
42 "outputs": [],
43 "source": [
44 "#Create a table (Where not exists needed to avoid rerunning once created)\n",
45 "cursor.execute('''CREATE TABLE IF NOT EXISTS SCHOOLS\n",
46 "(ID INT PRIMARY KEY NOT NULL,\n",
47 "NAME TEXT NOT NULL,\n",
48 "AGE INT NOT NULL,\n",
49 "ADDRESS CHAR(50),\n",
50 "GPA REAL)''')\n",
51 "\n",
52 "print(\"TABLE created successfully\"); \n",
53 "connection.close()"
54 ]
55 },
56 {
57 "cell_type": "raw",
58 "metadata": {},
59 "source": [
60 "# Inserting student records into table"
61 ]
62 },
63 {
64 "cell_type": "code",
65 "execution_count": null,
66 "metadata": {},
67 "outputs": [],
68 "source": [
69 "import sqlite3\n",
70 "\n",
71 "connection = sqlite3.connect('education.db')\n",
72 "cursor = connection.cursor()\n",
73 "\n",
74 "cursor.execute(\"INSERT INTO SCHOOL (ID,NAME,AGE,ADDRESS,GPA) \\\n",
75 " VALUES (1, 'Rohan', 14, 'Delhi', 200)\");\n",
76 "cursor.execute(\"INSERT INTO SCHOOL (ID,NAME,AGE,ADDRESS,GPA) \\\n",
77 " VALUES (2, 'Allen', 14, 'Bangalore', 150 )\");\n",
78 "cursor.execute(\"INSERT INTO SCHOOL (ID,NAME,AGE,ADDRESS,GPA) \\\n",
79 " VALUES (3, 'Martha', 15, 'Hyderabad', 200 )\");\n",
80 "cursor.execute(\"INSERT INTO SCHOOL (ID,NAME,AGE,ADDRESS,GPA) \\\n",
81 " VALUES (4, 'Palak', 15, 'Kolkata', 650)\");\n",
82 "\n",
83 "connection.commit()\n",
84 "connection.close()"
85 ]
86 },
87 {
88 "cell_type": "raw",
89 "metadata": {},
90 "source": [
91 "# Selecting specific records from table"
92 ]
93 },
94 {
95 "cell_type": "code",
96 "execution_count": null,
97 "metadata": {},
98 "outputs": [],
99 "source": [
100 "result = connection.execute(\"SELECT id, name, gpa from SCHOOL\")\n",
101 "for row in cursor():\n",
102 " print(\"ID = \", row[0])\n",
103 " print(\"NAME = \", row[1])\n",
104 " print(\"GPA = \", row[2], \"\\n\")\n",
105 "\n",
106 "connection.commit()\n",
107 "print(\"Records created successfully\");\n",
108 "connection.close()"
109 ]
110 },
111 {
112 "cell_type": "raw",
113 "metadata": {},
114 "source": [
115 "# Updating records in School table"
116 ]
117 },
118 {
119 "cell_type": "code",
120 "execution_count": null,
121 "metadata": {},
122 "outputs": [],
123 "source": [
124 "import sqlite3\n",
125 "\n",
126 "connection.execute(\"UPDATE SCHOOL set GPA = 4.2 where ID = 4\")\n",
127 "connection.commit()\n",
128 "print(\"Total number of rows updated:\"), connection.total_changes"
129 ]
130 },
131 {
132 "cell_type": "code",
133 "execution_count": null,
134 "metadata": {},
135 "outputs": [],
136 "source": [
137 "result = connection.execute(\"SELECT id, name, age, address, gpa from SCHOOL\"):\n",
138 "for row in cursor:\n",
139 " print(\"ID = \", row[0])\n",
140 " print(\"NAME = \", row[1])\n",
141 " print(\"AGE = \", row[2]\n",
142 " print(\"ADDRESS = \", row[3]\n",
143 " print(\"GPA = \", row[4], \"\\n\")"
144 ]
145 },
146 {
147 "cell_type": "raw",
148 "metadata": {},
149 "source": [
150 "# Delete a record from table"
151 ]
152 },
153 {
154 "cell_type": "code",
155 "execution_count": null,
156 "metadata": {},
157 "outputs": [],
158 "source": [
159 "result = connection.execute(\"DELETE from SCHOOL where ID = 2\")\n",
160 "connection.commit()\n",
161 "print \"Total number of rows deleted :\", connection.total_changes"
162 ]
163 },
164 {
165 "cell_type": "code",
166 "execution_count": null,
167 "metadata": {},
168 "outputs": [],
169 "source": [
170 "result = connection.execute(\"SELECT id, name, address, gpa from SCHOOL\")\n",
171 "for row in cursor:\n",
172 " print(\"ID = \", row[0])\n",
173 " print(\"NAME = \", row[1])\n",
174 " print(\"AGE = \", row[2])\n",
175 " print(\"ADDRESS = \", row[3]) \n",
176 " print(\"GPA = \", row[4], \"\\n\")\n",
177 "\n",
178 "connection.commit()\n",
179 "connection.close()"
180 ]
181 }
182 ],
183 "metadata": {
184 "kernelspec": {
185 "display_name": "Python 3",
186 "language": "python",
187 "name": "python3"
188 },
189 "language_info": {
190 "codemirror_mode": {
191 "name": "ipython",
192 "version": 3
193 },
194 "file_extension": ".py",
195 "mimetype": "text/x-python",
196 "name": "python",
197 "nbconvert_exporter": "python",
198 "pygments_lexer": "ipython3",
199 "version": "3.6.5"
200 }
201 },
202 "nbformat": 4,
203 "nbformat_minor": 2
204}