FilesExpand file tree

 master

/

10-sql.py

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

95 lines (60 loc) · 2.47 KB

 master

/

10-sql.py

Top

File metadata and controls

• Code
• Blame

95 lines (60 loc) · 2.47 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

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

90

91

92

########## Intro to SQLite and Creating a Database ##########

import sqlite3 #included with Python

conn = sqlite3.connect(":memory:")

#creating this goes off of the cwd. CD in terminal changes cwd

#so either cd to correct folder or figure out how to do a more concrete path:

#import os.path

#conn = sqlite3.connect(os.path.realpath('./books.db'))

#we use a cusror object to work with the database

c = conn.cursor()

#https://stackoverflow.com/questions/6318126/why-do-you-need-to-create-a-cursor-when-querying-a-sqlite-database

########## Create a Table with Python ##########

# Create table

c.execute('''CREATE TABLE books

(title TEXT, pages INTEGER)''')

########## Insert Data with Python ##########

# Insert a row of data

c.execute("INSERT INTO books VALUES ('Are You My Mother?', 72)")

# Save (commit) the changes

conn.commit()

########## Retrieve Data in Python ##########

c.execute('SELECT * FROM books WHERE title=?', ('Are You My Mother?',))

print(c.fetchone())

books = [

('The Digging-est Dog', 72),

('Goodnight Moon', 31),

('The Giving Tree', 66)

]

c.executemany('INSERT INTO books VALUES (?,?)', books)

conn.commit()

c.execute('SELECT * FROM books')

data = c.fetchall()

print(data)

########## Delete SQLite data in Python ##########

c.execute('SELECT * FROM books WHERE title=?', ('Are You My Mother?',))

print(c.fetchone())

c.execute('DELETE FROM books WHERE title=?', ('Are You My Mother?',)) #don't forget where lol

c.execute('SELECT * FROM books WHERE title=?', ('Are You My Mother?',))

print(c.fetchone())

########## Update SQLite Data in Python ##########

c.execute('SELECT * FROM books WHERE title=?', ('Goodnight Moon',))

print(c.fetchone())

c.execute('UPDATE books SET pages="30" WHERE title="Goodnight Moon"')

c.execute('SELECT * FROM books WHERE title=?', ('Goodnight Moon',))

print(c.fetchone())

conn.commit

c.close()

########## Create an SDK ##########

import booksSDK

from book import Book

book = Book("Are You My Mother?", 1000)

print("Added book id:", booksSDK.add_book(book))

print("Get book by title:", booksSDK.get_book_by_title(book.title))

print("Not a valid book:", booksSDK.get_book_by_title("yeet"))

booksSDK.add_book(Book('The Digging-est Dog', 76))

print("get books:", booksSDK.get_books())

book = booksSDK.update_book(book, book.title, 76)

print("Updated book:", book)

print("Deleted:", booksSDK.delete_book(book))

print("After delete:", booksSDK.get_book_by_title("Are You My Mother?"))

print("All books:", booksSDK.get_books())