MySQL & SQLite

Alan · Alan · commit 8439c6dacb20 · 2019-03-23T18:18:25.000+08:00
diff --git a/Chapter16/extract_from_class.py b/Chapter16/extract_from_class.py
diff --git a/Chapter16/extract_from_tag.py b/Chapter16/extract_from_tag.py
diff --git a/Chapter16/extract_from_wikipedia.py b/Chapter16/extract_from_wikipedia.py
diff --git a/Chapter18/connect_database.py b/Chapter18/connect_database.py
@@ -0,0 +1,4 @@
+import sqlite3
+
+con_obj = sqlite3.connect('test.db')
+print('Database connected successfully!!')
diff --git a/Chapter18/create_insert_data.py b/Chapter18/create_insert_data.py
@@ -0,0 +1,15 @@
+import pymysql as db
+
+con_obj = db.connect('localhost', 'test_user', 'test123', 'test')
+with con_obj:
+	cur_obj = con_obj.cursor()
+	cur_obj.execute('DROP TABLE IF EXISTS books')
+	cur_obj.execute('CREATE TABLE books(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(100))')
+	cur_obj.execute("INSERT INTO books(Name) VALUES('Harry Potter')")
+	cur_obj.execute("INSERT INTO books(Name) VALUES('Lord of the rings')")
+	cur_obj.execute("INSERT INTO books(Name) VALUES('Murder on the Orient Express')")
+	cur_obj.execute("INSERT INTO books(Name) VALUES('The adventures of Sherlock Holmes')")
+	cur_obj.execute("INSERT INTO books(Name) VALUES('Death on the Nile')")
+
+print('Table Created!!')
+print('Data inserted successfully!')
diff --git a/Chapter18/create_table.py b/Chapter18/create_table.py
@@ -0,0 +1,8 @@
+import sqlite3
+
+con_obj = sqlite3.connect('test.db')
+with con_obj:
+    cur_obj = con_obj.cursor()
+    cur_obj.execute("""CREATE TABLE books(title text, author text)""")
+
+print('Table created')
diff --git a/Chapter18/delete_data.py b/Chapter18/delete_data.py
@@ -0,0 +1,10 @@
+# import MySQLdb as db
+import pymysql as db
+
+con_obj = db.connect('localhost', 'test_user', 'test123', 'test')
+cur_obj = con_obj.cursor()
+cur_obj.execute('DELETE FROM books WHERE Id = 5')
+try:
+	con_obj.commit()
+except:
+	con_obj.rollback()
diff --git a/Chapter18/delete_sqlite_data.py b/Chapter18/delete_sqlite_data.py
@@ -0,0 +1,12 @@
+import sqlite3
+
+con_obj = sqlite3.connect('test.db')
+with con_obj:
+    cur_obj = con_obj.cursor()
+    sql = """
+        DELETE FROM books
+        WHERE author = 'John Smith'
+    """
+    cur_obj.execute(sql)
+
+print('Data deleted successfully!!')
diff --git a/Chapter18/get_database_version.py b/Chapter18/get_database_version.py
@@ -0,0 +1,10 @@
+import MySQLdb as mdb
+import sys
+
+con_obj = mdb.connect('localhost', 'test_user', 'test123', 'test')
+cur_obj = con_obj.cursor()
+cur_obj.execute('SELECT VERSION()')
+version = cur_obj.fetchone()
+print('Database version: %s ' % version)
+
+con_obj.close()
diff --git a/Chapter18/insert_data.py b/Chapter18/insert_data.py
@@ -0,0 +1,13 @@
+import sqlite3
+
+con_obj = sqlite3.connect('test.db')
+with con_obj:
+    cur_obj = con_obj.cursor()
+    cur_obj.execute("INSERT INTO books VALUES ('Pride and Prejudice', 'Jane Austen')")
+    cur_obj.execute("INSERT INTO books VALUES ('Harry Potter', 'J.K Rowling')")
+    cur_obj.execute("INSERT INTO books VALUES ('The Lord of the Rings', 'J. R. R. Tolkien')")
+    cur_obj.execute("INSERT INTO books VALUES ('Murder on the Orient Express', 'Agatha Christie')")
+    cur_obj.execute("INSERT INTO books VALUES ('A Study in Scarlet', 'Arthur Conan Doyle')")
+    con_obj.commit()
+
+print('Data inserted successfully!!')
diff --git a/Chapter18/retrieve_data.py b/Chapter18/retrieve_data.py
@@ -0,0 +1,10 @@
+# import MySQLdb as db
+import pymysql as db
+
+con_obj = db.connect('localhost', 'test_user', 'test123', 'test')
+with con_obj:
+	cur_obj = con_obj.cursor()
+	cur_obj.execute('SELECT * FROM books')
+	records = cur_obj.fetchall()
+	for r in records:
+		print(r)
diff --git a/Chapter18/retrieve_sqlite_data.py b/Chapter18/retrieve_sqlite_data.py
@@ -0,0 +1,9 @@
+import sqlite3
+
+con_obj = sqlite3.connect('test.db')
+cur_obj = con_obj.execute('SELECT title, author FROM books')
+for row in cur_obj:
+    print('Title = ', row[0])
+    print('Author = ', row[1], '\n')
+
+con_obj.close()
diff --git a/Chapter18/update_data.py b/Chapter18/update_data.py
@@ -0,0 +1,10 @@
+# import MySQLdb as db
+import pymysql as db
+
+con_obj = db.connect('localhost', 'test_user', 'test123', 'test')
+cur_obj = con_obj.cursor()
+cur_obj.execute('UPDATE books SET Name = "Fantastic Beasts" WHERE Id = 1')
+try:
+	con_obj.commit()
+except:
+	con_obj.rollback()
diff --git a/Chapter18/update_sqlite_data.py b/Chapter18/update_sqlite_data.py
@@ -0,0 +1,13 @@
+import sqlite3
+
+con_obj = sqlite3.connect('test.db')
+with con_obj:
+    cur_obj = con_obj.cursor()
+    sql = """
+        UPDATE books
+        SET author = 'John Smith'
+        WHERE author = 'J.K Rowling'
+    """
+    cur_obj.execute(sql)
+
+print('Data updated successfully!!')
