onlyforbopi
8/19/2018 - 2:57 PM
Python.Modules.Database.Sqlite
Python.Databases.Modules.Sqlite #python #Python #sqlite #Modules #PythonModules #DB #db #Database #Databases #database
- create_new_db.py
- create_table.py
- insert_example.py
- insert_example2.py
- db_example.py
- validate_check.py
- SQL examples
- Necessary modules and installation
/* -- */
/* Select all tables in db from master */
SELECT tbl_name FROM sqlite_master WHERE type = 'table';
/* -- */
/* Select to get info on specific table */
SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'Products6';
/* -- */
/* Select to get current date / time / timestamp */
SELECT current_time, current_date, current_timestamp
/* -- Count Table entries -- */
SELECT COUNT(*) AS "RECORDS" FROM Products6
/* -- */
/* DROP table from db */
DROP table Products2
/* -- */
/* Create table in db */
CREATE TABLE Products12321312
(ProductID integer,
Name text,
Price real,
NewPrice real,
primary key(ProductID))
/* -- */
/* Sample Update two values */
UPDATE Products6
SET Price=5000, NewPrice=10000
WHERE Name='Mary'
/* -- */
/* Sample Update 1 value */
UPDATE Products6
SET Price=5000
WHERE Name='Mary'
/* -- */
/* Sample update one liner */
UPDATE Products6 SET Price=6000 WHERE Name='Mary'
/* -- */
/* Sample Select 1 value */
SELECT *
FROM Products6
WHERE ProductID='1'
/* -- */
/* Select with 2 parameters */
SELECT * FROM Products6
WHERE Price >= 1000 AND Price <= 10000
/* -- */
/* Select with OR - one liner */
SELECT * FROM Products6 WHERE Name='Mary' OR Name='John'
/* -- */
/* Select with order by */
SELECT ProductID, Name FROM Products6
GROUP BY Name
/* -- */
/* Select with GROUPBY / ORDERBY */
SELECT ProductID, Name FROM Products6
GROUP BY Name
ORDER BY Name, ProductID
/* -- */
/* Select with AND / OR */
SELECT * FROM Products6
WHERE NAME='John'
AND NEWPRICE=100.0
OR NEWPRICE=101.0
/* -- */
/* Select with wildcard */
SELECT * FROM Products6 Where Name glob '*an*'
/* -- */
/* Select with wildcard v2 */
SELECT * FROM Products6 Where Name LIKE '%an%'
/* -- */
/* Select with use of 'as' */
SELECT Name, Price, NewPrice AS POUTSES
FROM Products6
/* -- */
/* Select with use of range of values in where */
SELECT *
FROM Products6
WHERE Price in (1000, 10000)
/* -- */
/* Select using range of values and 'between' */
SELECT *
FROM Products6
WHERE Price BETWEEN 1000 AND 15000
/* -- */
/* Select with 'not null' */
SELECT * FROM Products6 WHERE Price IS NOT NULL
/* -- */
/* Select with the use of 'not in' */
SELECT *
FROM Products6
WHERE Price not in (1000, 10000)
/* -- */
/* Sample insert v1 */
INSERT INTO Products6 values
(2, 'Mary', 200, 240)
/* -- */
/* Sample insert v2 */
INSERT INTO Products6 VALUES (5, 'Panos', 10000, 20000)
/* -- */
/* Sample insert v3 with 'Where' */
INSERT INTO Products7 (ProductID, Name, Price, NewPrice)
SELECT *
FROM Products6
WHERE Name='John'
/* -- */
/* Sample delete v2 */
//* this will delete only when condition is matched */
DELETE FROM table_name
WHERE [condition];
/* this will delete all */
DELETE FROM COMPANY;
/* -- */
/* Sample delete v2 with Where */
DELETE FROM Products6
WHERE ProductID = 2
/* -- */
/* Sample SUBSELECT (Select within select) */
SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
# -*- coding: utf-8 -*-
import os
import sys
import sqlite3
log_file = open("log_db_file.txt", "a")
db_name = sys.argv[1]
print(str(db_name))
def validatedbname(db_name):
filename, file_extension = os.path.splitext(db_name)
if file_extension != '.db':
print("Incorrent naming. File needs to suffix .db. Terminating")
sys.exit(1)
else:
print("File provided in correct format. Proceeding with script")
def checkifexists(db_name):
if os.path.isfile(db_name):
response = input("The file {0} already exists, do you wish to recreate it? (y/n)".format(db_name))
if response == 'y' or response == 'Y':
return True
else:
print("Terminating script.")
sys.exit(1)
else:
print("File not located. New {0} file will be created".format(db_name))
return True
def createdb(db_name):
with sqlite3.connect(db_name) as db:
try:
cursor = db.cursor()
except:
print("Cannot establish cursor at {0}".format(db_name))
return False
else:
print("Database {0} created succesfully.".format(db_name))
return True
############################################################
# # Create new db
# # Notes:
# # Will create a new db file if one doesnt exist.
# # If it doesnt exist it will connect to existing db.
# # No conflict problems.
# with sqlite3.connect(db_name) as db:
# cursor = db.cursor()
# # or
# db_name2 = "oeo.db"
# db2 = sqlite3.connect(db_name2)
# cursor2 = db2.cursor()
# # with try/except
# with sqlite3.connect(db_name) as db:
# try:
# cursor = db.cursor()
# except:
# print("Could not establish connection to {0}".format(db_name))
# # or
# db_name2 = "oeo.db"
# try:
# db2 = sqlite3.connect(db_name2)
# cursor2 = db2.cursor()
# except:
# print("Could not establish connection to {0}".format(db_name))
validatedbname(db_name)
checkifexists(db_name)
createdb(db_name)
# -*- coding: utf-8 -*-
import os
import sys
import sqlite3
log_file = open("log_db_file.txt", "w")
def strtotupleentire(string):
output = ()
output = output + (string,)
return output
def create_table(db_name, table_name, sql):
r"""
Name: create_table
Function: create_table(db_name, table_name, sql)
Description: creates a new table in database <db_name>
The table will be named <table_name>
The creation command will be provided in the <sql> string.
Input: db_name (string) -> Name of the db file
table_name (string) -> Name of table we intend to create
sql (string) -> Full SQLite command string to create the table
Output: None
Usage1: See below 2 examples
Notes: Control is performed whether the table_name provided
already exists in the db, then a prompt appears
ToDo:
1. Make version with no prompt (override in parameters)
"""
with sqlite3.connect(db_name) as db:
cursor = db.cursor()
# check for table
log_file.write("Checking for existence of db table\n")
cursor.execute("select name from sqlite_master where name=?",(table_name,))
#cursor.execute("select name from sqlite_master where name=*")
log_file.write("Loading query results into result var\n")
result = cursor.fetchall()
print(result)
log_file.write(str(result))
keep_table = True
if len(result) == 1:
response = input("The table {0} already exists, do you wish to recreate it? (y/n)".format(table_name))
if response == "y":
keep_table = False
print("The table {0} will be recreated, all existing data will be lost".format(table_name))
cursor.execute("DROP TABLE if exists {0}".format(table_name))
db.commit()
else:
print("The existing table was kept")
else:
keep_table = False
if not keep_table:
cursor.execute(sql)
db.commit()
# with sqlite3.connect('example.db') as db:
# pass
def get_all_tables(db_name):
with sqlite3.connect(db_name) as db:
cursor = db.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())
def check_if_table_exists(db_name, table_name):
with sqlite3.connect(db_name) as db:
cursor = db.cursor()
try:
cursor.execute("select name from sqlite_master where name=?",(table_name,))
except:
print("Could not execute query")
else:
result = cursor.fetchall()
if len(result) == 1:
return True
else:
return False
def check_if_table_exists2(db_name, table_name):
with sqlite3.connect(db_name) as db:
cursor = db.cursor()
try:
cursor.execute("""select name
from sqlite_master
where name=?""",(table_name,))
except:
print("Could not execute query")
else:
result = cursor.fetchall()
if len(result) == 1:
return True
else:
return False
if __name__ == "__main__":
db_name = "ppartbackup.db"
sql = """ CREATE TABLE {0}
(ProductID integer,
Name text,
Price real,
NewPrice real,
primary key(ProductID))"""
sql2 = """ CREATE TABLE Products10
(ProductID integer,
Name text,
Price real,
NewPrice real,
primary key(ProductID))"""
create_table(db_name, "ppartbackup5" ,sql.format("ppartbackup5"))
create_table(db_name, "clients" ,sql.format("clients"))
#create_table(db_name, "Products4" ,sql2)
get_all_tables(db_name)
conn = sqlite3.connect('example.db')
c = conn.cursor()
conn.close()
print("newstring")
print(strtotupleentire("newstring"))
print("#####")
db_name2 = "oeo.db"
db2 = sqlite3.connect(db_name2)
cursor2 = db2.cursor()
#cursor2.execute(sql2)
cursor2.execute("SELECT name FROM sqlite_master WHERE type='table';")
#print(cursor2.fetchall())
print("#####")
print(check_if_table_exists("oeo.db", 'Products2'))
print(check_if_table_exists2("oeo.db", 'Products2'))
print(get_all_tables("oeo.db"))
import sys
import sqlite3
db_name = sys.argv[1]
data_person_name = [('Michael', 'Fox'),
('Adam', 'Miller'),
('Andrew', 'Peck'),
('James', 'Shroyer'),
('Eric', 'Burger2')]
print (db_name)
con = sqlite3.connect(db_name)
c = con.cursor()
# IMPORTANT - WE DO NOT HAVE TO SPECIFY THE INTEGER
c.executemany('INSERT INTO PhoneMap(first_name, last_name) VALUES (?,?)', data_person_name)
for row in c.execute('SELECT * FROM PhoneMap'):
print (row)
con.commit()
import os
import sys
import sqlite3
log_file = open("log_db_file.txt", "a")
db_name = sys.argv[1]
# table = sys.argv[2]
# name = sys.argv[3]
# surname = sys.argv[4]
# addr = sys.argv[5]
# phone = sys.argv[6]
print(str(db_name))
def add_record(table, inte, name_str, surname_str, address_str, phone_str):
with sqlite3.connect(db_name) as db:
cursor = db.cursor()
try:
db.execute("""INSERT INTO {0}(
PersonID,
Name,
Surname,
Address,
Phone) values (?,?,?,?)""".format(table), (name_str, surname_str, address_str, phone_str))
db.commit()
except sqlite3.IntegrityError:
print('Record already exists')
add_record("Contacts", 10, "Mitsos", "Milopoulos", "AgGrig18", "21080808080")
import os
import sys
import sqlite3
log_file = open("log_db_file.txt", "w")
db_name = sys.argv[1]
print(str(db_name))
def create_table(db_name, table_name, sql):
r"""
Name: create_table
Function: create_table(db_name, table_name, sql)
Description: creates a new table in database <db_name>
The table will be named <table_name>
The creation command will be provided in the <sql> string.
Input: db_name (string) -> Name of the db file
table_name (string) -> Name of table we intend to create
sql (string) -> Full SQLite command string to create the table
Output: None
Usage1: See below 2 examples
Notes: Control is performed whether the table_name provided
already exists in the db, then a prompt appears
ToDo:
1. Make version with no prompt (override in parameters)
"""
with sqlite3.connect(db_name) as db:
cursor = db.cursor()
# check for table
log_file.write("Checking for existence of db table\n")
cursor.execute("select name from sqlite_master where name=?",(table_name,))
#cursor.execute("select name from sqlite_master where name=*")
log_file.write("Loading query results into result var\n")
result = cursor.fetchall()
print(result)
log_file.write(str(result))
keep_table = True
if len(result) == 1:
response = input("The table {0} already exists, do you wish to recreate it? (y/n)".format(table_name))
if response == "y":
keep_table = False
print("The table {0} will be recreated, all existing data will be lost".format(table_name))
cursor.execute("DROP TABLE if exists {0}".format(table_name))
db.commit()
else:
print("The existing table was kept")
else:
keep_table = False
if not keep_table:
cursor.execute(sql)
db.commit()
sql="""CREATE TABLE {0}
(PersonID integer,
Name text,
Surname text,
Address text,
Phone text,
primary key(PersonID))"""
sql="""CREATE TABLE PhoneMap
(name_id INTEGER PRIMARY KEY,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL)"""
#create_table(db_name, "PhoneMap", sql.format("Contacts"))
# -*- coding: utf-8 -*-
import os
import sys
import sqlite3
log_file = open("log_db_file.txt", "a")
db_name = sys.argv[1]
print(str(db_name))
def validatedbname(db_name):
filename, file_extension = os.path.splitext(db_name)
if file_extension != '.db':
print("Incorrent naming. File needs to suffix .db. Terminating")
sys.exit(1)
else:
print("File provided in correct format. Proceeding with script")
def checkifexists(db_name):
if os.path.isfile(db_name):
response = input("The file {0} already exists, do you wish to recreate it? (y/n)".format(db_name))
if response == 'y' or response == 'Y':
return True
else:
print("Terminating script.")
sys.exit(1)
else:
print("File not located. New {0} file will be created".format(db_name))
return True
def createdb(db_name):
with sqlite3.connect(db_name) as db:
try:
cursor = db.cursor()
except:
print("Cannot establish cursor at {0}".format(db_name))
return False
else:
print("Database {0} created succesfully.".format(db_name))
return True
############################################################
# # Create new db
# # Notes:
# # Will create a new db file if one doesnt exist.
# # If it doesnt exist it will connect to existing db.
# # No conflict problems.
# with sqlite3.connect(db_name) as db:
# cursor = db.cursor()
# # or
# db_name2 = "oeo.db"
# db2 = sqlite3.connect(db_name2)
# cursor2 = db2.cursor()
# # with try/except
# with sqlite3.connect(db_name) as db:
# try:
# cursor = db.cursor()
# except:
# print("Could not establish connection to {0}".format(db_name))
# # or
# db_name2 = "oeo.db"
# try:
# db2 = sqlite3.connect(db_name2)
# cursor2 = db2.cursor()
# except:
# print("Could not establish connection to {0}".format(db_name))
validatedbname(db_name)
checkifexists(db_name)
createdb(db_name)