# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # First Python program that connects to a database

#

#Connect to SQLite
import sqlite3
#Specific exceptions SQLite may raise
from sqlite3 import Error 

#Create a connection object that represents the database
#Remember \\ for Windows
conn = sqlite3.connect('data\\people.db')

#Cursor object stores knowledge about transactions excuted against database
cur = conn.cursor()

cur.execute("select * from people;")
#This executes the command, but doesn't display automatically

rows = cur.fetchall()
rows

rows[3]


# + active=""
# cur.close()
# -

cur = conn.cursor()
cur.execute("select * from pets")
rows = cur.fetchall()
print(rows)
cur.close()

cur = conn.cursor()
petName = ('Malfoy',)
cur.execute('select * from pets where pet_name = ?', petName)
rows = cur.fetchall()
print(rows)
cur.close()

petName = 'Malfoy'
sqlString = 'select * from pets where pet_name = {}'.format(petName)
print(sqlString)

cur = conn.cursor()
petName = input('Enter pet name')
sqlString = 'select * from pets where pet_name = "{}"'.format(petName)
cur.execute(sqlString)
rows = cur.fetchall()
print(rows)
cur.close()

# # Execute SQL Query using Pandas

import pandas as pd

df = pd.read_sql_query(sqlString,conn)

df

# +
#Pandas eliminates the need for cursor
# -

# # Insert Rows with Python

# +
#Insert statement
person_first_name='Dorothy'
person_last_name='Martinez'
pet_name='Newbie'
person_sql_string="insert into people (first_name) values ('{}');".format(person_first_name)
pet_sql_string="insert into pets (pet_name, owner_id) values ('{}', last_insert_rowid());".format(pet_name)
print(person_sql_string)
print(pet_sql_string)

cur = conn.cursor()
cur.execute(person_sql_string)
cur.execute(pet_sql_string)
cur.close()

#Get list
sqlString2 = 'select * from people, pets;'
df = pd.read_sql_query(sqlString2,conn)
df
# -

conn.close()

# +
#conn.commit() to save to database
# -

# Using connection from above. Starting new exercise of adding random readings to new database.

# +
import time
import datetime
import random

def mock_accel_read():
    x = random.randrange(0, 10, 1)
    y = random.randrange(0, 10, 1)
    z = random.randrange(0, 10, 1)
    serial_no = "TestNumber"
    timestamp = datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S')
    return (x, y, z, serial_no, timestamp)


# -

x,y,z, serial_no, timestamp = mock_accel_read()

print(x,y,z, timestamp)

#Create a connection object that represents the database
#Remember \\ for Windows
conn2 = sqlite3.connect('data\\readings.db')

#Cursor object stores knowledge about transactions excuted against database
cur = conn2.cursor()

sql_string="insert into readings (x, y, z, serial_no, timestamp) values ('{0}','{1}','{2}','{3}','{4}');".format(x, y, z, serial_no, timestamp)

print(sql_string)

cur.execute(sql_string)
#This executes the command, but doesn't display automatically

cur.execute('select * from readings;')

rows = cur.fetchall()
rows

# Now can we add this to our pi-iot code? Wait, we didn't close the database. If we don't close it, data is not committed to the database... ie inaccessible

cur.execute('select count(*) from readings;')
result = cur.fetchall()
result
count = result[0][0]
count

conn2.commit()
conn2.close()


# Experimenting with os and os.path

import os

p = os.path.join("fu")

p

p = os.path.join(p, "next")

p

os.path.join("documents", "code", "piiot")



StarcoderdataJupyterScriptsDedupFiltered