Sqlite Doesn't Bite
Import the requirements and connect to a database called data.db in the same directory as the script.
import sqlite3
from sqlite3 import Error
def db_connect():
try:
conn = sqlite3.connect('data.db')
return conn
except Error as e:
print(e)
return None
conn = db_connect()
Create a table called testing with auto-incrementing ID along with name and email columns.
def create_table(conn):
create_table = """CREATE TABLE IF NOT EXISTS testing (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL
);"""
conn.execute(create_table)
create_table(conn)
Insert row into table if not already present.
def insert_row(conn, name, email):
cur = conn.cursor()
cur.execute("SELECT * FROM testing WHERE name='{}'".format(name))
if not len(cur.fetchall()):
conn.execute(
"INSERT INTO testing (name, email) VALUES (?, ?);", (name, email))
conn.commit()
else:
print('Already exists in db')
insert_row(conn, 'impshum', '[email protected]')
Read all from database.
def read_db(conn):
cur = conn.cursor()
cur.execute("SELECT * FROM testing")
rows = cur.fetchall()
for row in rows:
print(row)
read_db(conn)
Search database.
def search_db(conn, column, query):
cur = conn.cursor()
cur.execute("SELECT * FROM testing WHERE {}='{}'".format(column, query))
rows = cur.fetchall()
for row in rows:
print(row)
search_db(conn, 'name', 'impshum')
And tying it all together in one main function, as an example, to use all the functions above.
def main():
conn = db_connect()
if conn:
with conn:
create_table(conn)
insert_row(conn, 'impshum', '[email protected]')
read_db(conn)
search_db(conn, 'name', 'impshum')
if __name__ == '__main__':
main()
Thanks for reading. x
Resources
- Python: https://python.org
- Sqlite3: https://docs.python.org/2/library/sqlite3.html