· 9 years ago · Oct 31, 2016, 06:20 AM
1import sqlite3
2
3database = ''
4table = ''
5column_1 = ''
6column_2 = ''
7
8conn = sqlite3.connect('''{}.db'''.format(database))
9c = conn.cursor()
10
11c.execute('''DROP TABLE IF EXISTS {}'''.format(table))
12c.execute('''CREATE TABLE {}({} TEXT, {} TEXT)'''.format(table, column_1, column_2))
13conn.commit()
14
15c.execute('''
16INSERT INTO table_name(
17column_name
18)
19VALUES (?)
20''', ('value',))
21
22c.execute('''
23UPDATE table_name
24SET column_name=(?)
25WHERE column_name=(?)
26''', ('value', 'value',))
27conn.commit()
28
29#----------------------------
30#----------------------------
31#----------------------------
32
33conn = sqlite3.connect(':memory:')
34c = conn.cursor()
35
36conn = sqlite3.connect('''database_name.db''')
37c = conn.cursor()
38
39conn = sqlite3.connect('''{}.db'''.format('database_name'))
40c = conn.cursor()
41
42#----------------------------
43#----------------------------
44#----------------------------
45
46c.execute('''
47DROP TABLE
48table_name
49''')
50
51c.execute('''
52DROP TABLE IF EXISTS
53table_name
54''')
55
56#----------------------------
57#----------------------------
58#----------------------------
59
60c.execute('''
61CREATE TABLE
62table_name(
63column_name TEXT,
64column_name REAL
65)''')
66
67c.execute('''
68CREATE TABLE IF NOT EXISTS table_name(
69column_name TEXT,
70column_name REAL
71)''')
72
73#----------------------------
74#----------------------------
75#----------------------------
76
77c.execute('''
78INSERT INTO table_name(
79column_name
80)
81VALUES (?)
82''', ('value',))
83
84c.execute('''
85INSERT INTO table_name(
86column_name,
87column_name
88)
89VALUES (?, ?)
90''', ('value','value'))
91
92#----------------------------
93#----------------------------
94#----------------------------
95
96c.execute('''
97UPDATE table_name
98SET column_name=(?)
99WHERE column_name=(?)
100''',
101 ('value', 'value',))
102
103#----------------------------
104#----------------------------
105#----------------------------
106
107c.execute('''
108INSERT INTO table_name(
109column_name
110)
111VALUES (?)
112''', ('value',))
113c.execute('''
114DELETE FROM table_name
115WHERE column_name=(?)
116''', ('value', ))
117conn.commit()
118
119
120c.execute('''
121DELETE FROM table_name
122WHERE column_name=(?)
123''', ('value',))
124conn.commit()
125
126#----------------------------
127#----------------------------
128#----------------------------
129
130c.execute('''DELETE FROM subsequent WHERE url LIKE "%itunes%"''')
131
132#----------------------------
133#----------------------------
134#----------------------------
135
136
137c.execute('''
138SELECT *
139FROM table_name
140''')
141for i in c.fetchall():
142 print(i)
143print(i[0] for i in c.fetchall())
144
145
146c.execute('''
147SELECT column_name
148FROM table_name
149''')
150for i in c.fetchall():
151 print(i)
152print(i[0] for i in c.fetchall())
153
154
155c.execute('''
156SELECT column_name
157FROM table_name
158LIMIT 1
159''')
160print(c.fetchone())
161
162
163c.execute('''
164SELECT *
165FROM table_name(
166column_name
167)
168WHERE column_name=(?)
169''',
170 ('value', ))
171for i in c.fetchall():
172 print(i)
173print(i[0] for i in c.fetchall())
174
175c.execute('''
176SELECT column_name
177FROM table_name
178WHERE column_name=(?)
179LIMIT 1
180''',
181 ('value',))
182print(c.fetchone())
183
184
185c.execute('''
186SELECT EXISTS(
187SELECT 1 FROM table_name
188WHERE column_name=(?)
189LIMIT 1
190)''',
191 ('value', ))
192print(c.fetchone())
193
194
195#----------------------------
196#----------------------------
197#----------------------------
198
199"""
200ALTER TABLE
201ADD COLUMN
202DROP COLUMN
203ALTER COLUMN
204INSERT INTO
205UPDATE
206SET
207DELETE
208SELECT
209SELECT DISTINCT
210SELECT *
211FROM
212WHERE
213AND
214OR
215IN
216IS
217IS NOT
218NULL
219JOIN
220ON
221"""
222
223#----------------------------
224#----------------------------
225#----------------------------
226
227"""
228SELECT * FROM table_name JOIN table_name
229SELECT column_name, column_name FROM table_name JOIN table_name
230SELECT column_name, column_name FROM table_name JOIN table_name WHERE column_name='value'
231
232SELECT * FROM table_name JOIN table_name ON table_name.column_name = table_name.column_name
233SELECT * FROM table_name JOIN table_name ON table_name.column_name = table_name.column_name WHERE table_name.column_name = 'value'
234
235SELECT column_name, column_name FROM table_name JOIN table_name ON table_name.column_name = table_name.column_name
236
237
238SELECT name, number, record FROM names JOIN numbers ON names.id = numbers.id JOIN records ON numbers.number = records.id WHERE name = 'sue'
239"""
240
241#----------------------------
242#----------------------------
243#----------------------------
244
245"""
246type | name | tbl_name | rootpage | sql
247
248SELECT * FROM sqlite_master;
249SELECT name FROM sqlite_master WHERE type='table';
250
251SELECT EXISTS(SELECT 1 FROM table_name WHERE column_name=('value') LIMIT 1);
252"""
253
254#----------------------------
255#----------------------------
256#----------------------------
257
258"""
259SELECT index FROM names
260
261SELECT name
262FROM names
263
264SELECT names.name, numbers.number, records.record FROM names JOIN numbers ON names.index=numbers.index JOIN records ON numbers.index=records.index
265
266
267select name from names
268
269select number from numbers
270
271
272select names.name, numbers.number from names JOIN numbers ON names.index=numbers.index; WHERE name='sue'
273SELECT * FROM names JOIN numbers ON names.id=numbers.id WHERE numbers.number = 10
274
275SELECT * FROM names JOIN numbers ON names.id=numbers.id JOIN records
276
277SELECT number, record FROM numbers JOIN records ON numbers.id = records.id
278
279
280
281SELECT name, number FROM names JOIN numbers ON names.id = numbers.id
282SELECT number, record FROM numbers JOIN records ON numbers.id = records.id
283
284SELECT name, record FROM names JOIN numbers ON names.id = numbers.id JOIN records ON numbers.number = records.id
285
286
287SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type!='meta' ORDER BY tbl_name, type DESC, name
288
289
290SELECT number FROM numbers UNION SELECT name FROM names
291"""