forked from ariannedee/python-environments
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathexample_1_sql.py
More file actions
89 lines (67 loc) · 2.36 KB
/
example_1_sql.py
File metadata and controls
89 lines (67 loc) · 2.36 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
import os
import random
import sqlite3
from sqlite3 import Error
def get_path(filename):
this_directory = os.path.dirname(__file__)
full_path = os.path.join(this_directory, filename)
return full_path
def sql_connection():
try:
path = get_path('test_sql.db')
con = sqlite3.connect(path)
return con
except Error:
print(Error)
def create_table(connection):
cursor = connection.cursor()
# Check if table already exists
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='words'")
rows = cursor.fetchall()
# If not, create table
if len(rows) == 0:
cursor.execute("CREATE TABLE words(word TEXT, difficult INTEGER)")
connection.commit()
def insert_word(connection, word, difficult):
connection.cursor().execute("INSERT INTO words (word, difficult) VALUES(?, ?)", (word, difficult))
connection.commit()
def fetch_words(connection, difficult=None):
cursor = connection.cursor()
if difficult is None:
cursor.execute('SELECT word FROM words')
else:
values = (1,) if difficult else (0,)
cursor.execute('SELECT word FROM words WHERE difficult=?', values)
rows = cursor.fetchall()
return [row[0] for row in rows]
def get_random_word(connection, difficult):
possible_words = fetch_words(connection, difficult)
word = random.choice(possible_words)
return word
def load_words(connection):
path = get_path('words.txt')
with open(path, 'r') as file:
for line in file.readlines():
word = line.strip().lower()
insert_word(connection, word, difficult=0)
path = get_path('hard_words.txt')
with open(path, 'r') as file:
for line in file.readlines():
word = line.strip().lower()
insert_word(connection, word, difficult=1)
connection.commit()
if __name__ == '__main__':
con = sql_connection()
create_table(con)
word_list = fetch_words(con)
# Load words if empty
if len(word_list) == 0:
load_words(con)
word_list = fetch_words(con)
assert len(word_list) > 200
print(f"There are {len(word_list)} words in the database")
easy_word = get_random_word(con, difficult=0)
print(f"Easy word: {easy_word}")
difficult_word = get_random_word(con, difficult=1)
print(f"Difficult word: {difficult_word}")
con.close()