def card_reader_job():
db2 = Database("nfc.db")
cursor = None
value = None
if nfc.nfc_polling():
value = nfc.read_from_card(2)
try:
value = value.split(',')
value = json.loads(value[0])
print(value)
cursor = db2.query(Card).select().filter(
Card.card_id == value['Card_ID']).execute()
if len(cursor.fetchall()) == 0:
print("has not permission")
else:
if nfc.gpio_init(6, 1):
nfc.gpio_write(6, 1)
time.sleep(.1)
nfc.gpio_write(6, 0)
print("has permission")
except:
import traceback
print(traceback.print_exc())
print("error")
del db2
del value
del cursor
class dbConector:
__instance = None # singleton reference
dbAgent = None
@staticmethod
def getInstance(): # Method to get the unique reference for the class
if dbConector.__instance == None:
dbConector()
return dbConector.__instance
def __init__(self):
if dbConector.__instance != None:
raise Exception("glitch in the matrix, talk to nio")
else:
dbConector.__instance = self
def doConection(
self): # Do the connection with DB users in the same directory
if not isfile(
'Users.db'): # If is the first time, it will create the DB
self.dbAgent = Database("Users.db")
self.dbAgent.query(Person, Groups).create().execute()
else:
self.dbAgent = Database("Users.db")
def closeConection(self) -> None:
self.dbAgent.close()
def doConection(
self): # Do the connection with DB users in the same directory
if not isfile(
'Users.db'): # If is the first time, it will create the DB
self.dbAgent = Database("Users.db")
self.dbAgent.query(Person, Groups).create().execute()
else:
self.dbAgent = Database("Users.db")
def update_info(input_name):
with Database(os.getcwd().replace("\\", "/") + "/shadow.db") as db:
_user = db.query(Manage).filter(
Manage.site_name == input_name).select().execute().fetchone()
print("[1] Site idx : %s" % (_user[0])) #idx
print("[2] Site name : %s" % (_user[1])) #site_name
print("[3] Site url : %s" % (_user[2])) # site_url
print("[4] Site id : %s" % (_user[3])) # user_id
print("[5] Site pw : %s" % (_user[4])) # user_pw
update_type = input(">>")
if update_type == str(2): #hostname
_hostname = input("new hostname : ")
pass
elif update_type == str(3): #hosturl
_url = input("new host url : ")
pass
elif update_type == str(4): #ID
_ID = input("new ID : ")
pass
elif update_type == str(5): #PW
_Password = input("new Password : ")
pass
else:
pass
pass
def select_user(input_id=''):
row_fetch = list()
with Database(os.getcwd().replace("\\", "/") + "/shadow.db") as db:
#select 출력문
for row in db.query(Manage).select().execute():
row_fetch.append(row)
#print(pbkdf2_sha256.hash("asd"))
return row_fetch
def insert_user(__site_name, __site_url, __user_id, __user_pw, __content=''):
with Database(os.getcwd().replace("\\", "/") + "/shadow.db") as db:
#데이터베이스 객체 생성
objectname = __site_name
objectname = Manage(site_name=__site_name,
site_url=__site_url,
user_id=__user_id,
user_pw=__user_pw,
content=__content,
date=settime.get_time())
#데이터베이스 객체 insert
db.query().insert(objectname).execute()
pass
def job():
db1 = Database("nfc.db")
chip_id = "0281d7950e5b"
server = "http://yourdomain.ir/api/cards/" + chip_id
r = None
try:
r = requests.get(server)
except:
pass
if r is not None:
data = r.json()['data']
ids = []
cursor = db1.query(Card).select().execute()
for row in cursor:
row = dict_factory(cursor, row)
del row['id']
ids.append(row)
ids = list(ids)
print("this is ids : ", ids)
print("this is data : ", data)
for item in ids:
if item in data:
print("has")
else:
print("not has")
db1.query(Card).delete().filter(
Card.card_id == item['card_id']).execute()
for item in data:
# check card not exists
cursor = db1.query(Card).select().filter(
Card.card_id == item['card_id']).execute()
if len(cursor.fetchall()) == 0:
card = Card(card_id=item['card_id'])
db1.query(Card).insert(card).execute()
cursor = db1.query(Card).select().execute()
print("this is fetchall : ", cursor.fetchall())
if __name__ == '__main__':
# add filemode="w" to overwrite
logging.basicConfig(filename="sample.log",
level=logging.DEBUG,
format=('%(asctime)s: '
'%(filename)s: '
'%(levelname)s: '
'%(funcName)s(): '
'%(lineno)d: '
'%(message)s'),
datefmt="%Y-%m-%d %H:%M:%S")
with Database("test.db") as db:
# создание таблиц;
db.query(Post, User).create().execute()
# insert
user1 = User(id=1, name='User1')
user2 = User(id=2, name='User2')
user3 = User(id=3, name='User3')
post1 = Post(id=1, name='Post1', id_user=user1.id)
post2 = Post(id=2, name='Post2', id_user=user2.id)
post3 = Post(id=3, name='Post3', id_user=user3.id)
db.query().insert(user1, user2, user3, post1, post2, post3).execute()
# select с указанием необходимых столбцов + автоджоин таблиц, на которые есть fk;
class Card(BaseTable):
__table_name__ = 'card'
id = IntegerField(primary_key=True, auto_increment=True)
card_id = TextField(not_null=True)
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
if not os.path.isfile("nfc.db"):
db = Database("nfc.db")
db.query(Card).create().execute()
def job():
db1 = Database("nfc.db")
chip_id = "0281d7950e5b"
server = "http://yourdomain.ir/api/cards/" + chip_id
r = None
try:
r = requests.get(server)
except:
pass
if r is not None:
data = r.json()['data']
def __init__(self):
is_db_exists = os.path.isfile(settings.DB_NAME)
self._db = Database(settings.DB_NAME)
if not is_db_exists:
self._create_db_for_first_use()
class DB:
"The main database object"
tables = (User, Settings,)
def __init__(self):
is_db_exists = os.path.isfile(settings.DB_NAME)
self._db = Database(settings.DB_NAME)
if not is_db_exists:
self._create_db_for_first_use()
@property
def database(self):
"Returns the database"
return self._db
def _create_db_for_first_use(self, skip_error=False):
logger.debug("Creating tables")
# Create the tables
for table in self.tables:
try:
self.database.query(table).create().execute()
except sqlite3.OperationalError:
if not skip_error:
logger.error(
"Could not created table %s",
table.__table_name__
)
raise
logger.warning(
"Could not created table %s",
table.__table_name__
)
self.insert(Settings())
def select(self, select_from, *condition_expressions, logical_operator='AND'):
"Returns the values from database based on conditions"
query = self._db.query(select_from).select()
if condition_expressions:
query = query.filter(
*condition_expressions,
logical_operator_inner=logical_operator
)
result = query.execute()
result_list = []
for res in result:
result_list.append(select_from(*res))
return result_list
def insert(self, obj):
"Inserts the record"
self.database.query().insert(obj).execute()
def delete(self, obj):
"Deletes record from database based on id"
self.database.query(obj.__class__).delete().filter(
obj.__class__.id == obj.id
).execute()
def update(self, obj, *parts):
"Updates the database"
if not parts:
# Update all fields
parts = tuple(obj.fields())
kwargs = {}
for part in parts:
kwargs[part] = getattr(obj, part)
self.database.query(obj.__class__).update(
**kwargs
).filter(obj.__class__.id == obj.id).execute()
def __enter__(self):
return self
def __exit__(self, *args, **kwargs):
self.database.close()
def __del__(self):
# pylint:disable=bare-except
try:
self._db.close()
except:
pass
def create_db():
with Database(os.getcwd().replace("\\", "/") + "/shadow.db") as db:
#테이블 생성
user = db.query(Manage).create().execute()
pass
def delete_site(input_num):
with Database(os.getcwd().replace("\\", "/") + "/shadow.db") as db:
db.query(Manage).filter(
Manage.idx == int(input_num)).delete().execute()
pass
def select_user_pw(input_name):
row_fetch = list()
with Database(os.getcwd().replace("\\", "/") + "/shadow.db") as db:
res = db.query(Manage).filter(
Manage.site_name == input_name).select().execute().fetchone()
return res
#logger configure:
logging.basicConfig(filename=os.getcwd().replace("\\", "/") + "/error.log",
level=logging.DEBUG,
format=('%(asctime)s: '
'%(filename)s: '
'%(levelname)s: '
'%(funcName)s(): '
'%(lineno)d: '
'%(message)s'),
datefmt="%Y-%m-%d %H:%M:%S")
#create_db()
#insert_user(__site_name="i2sec", __site_url="i2sec.co.kr", __user_id="rltmd", __user_pw="0000")
#select_user()
with Database(
"C:/Users/namki/Desktop/python_BasicProject/pm-python/shadow.db"
) as db:
try:
c = ntplib.NTPClient()
response = c.request('europe.pool.ntp.org', version=3)
print(ctime(response.tx_time)) #시간출력
except ntplib.NTPException:
print(ctime())
#테이블 생성
#db.query(Manage).create().execute()
#데이터베이스 객체 생성
#user1 = Manage(site_name="naver",site_url="www.naver.com",user_id="rltmd1004",user_pw="0000",content='',date=ctime(response.tx_time))
#데이터베이스 객체 insert