async def get_table(request: Request):
req = Dict(
{item[0]: item[1]
for item in request.query_params.multi_items()})
cnxn = Connection(cfg.db_system, cfg.db_server, cfg.db_uid, cfg.db_pwd,
req.base)
schema = req.get('schema', None)
if cnxn.system == 'postgres' and schema:
base_path = req.base + '.' + req.schema
else:
base_path = req.base or schema
dbo = Database(cnxn, base_path)
table = Table(dbo, req.table)
grid = Grid(table)
table.limit = int(req.get('limit', 30))
table.offset = int(req.get('offset', 0))
if req.get('filter', None):
grid.set_search_cond(req['filter'])
if req.get('sort', None):
grid.sort_columns = json.loads(req.sort)
# todo: handle sort
pkey_vals = None
if ('prim_key' in req and req.prim_key):
pkey_vals = json.loads(req.prim_key)
return {'data': grid.get(pkey_vals)}
def RegisterAdmin(name,
username,
password,
contact_no,
register_date,
access_token='1234'):
db, cursor = Connection()
search_sql = f"select username from admin where username = '******';"
insert_sql = f"""insert into admin(name,username,password,contact_no,register_date,access_token)
values('{name}','{username}','{password}','{contact_no}','{register_date}','{access_token}');
"""
flag = 0
try:
cursor.execute(search_sql)
rs = cursor.fetchall()
# print(rs)
if (len(rs) > 0):
flag = 1
# print(rs[3])
except Exception as e:
print(e)
print("Error connecting to database")
else:
if (flag == 1):
print("username already taken")
# return False
elif (flag == 0):
cursor.execute(insert_sql)
db.commit()
print("Registered successfully as admin")
# return True
finally:
db.close()
def on_status(self, status):
"""
Storing streaming tweets in the SQL database.
The returned status object has all the information about a tweet.
"""
# Exclude all retweets.
if status.retweeted or 'RT @' in status.text:
return True
id_str = status.id_str
created_at = status.created_at
text = status.text
user_location = status.user.location
tweet = {"text": text, "label": None}
tweet_processor = PreProcessTweets()
processed_tweet = tweet_processor.process_tweets([tweet])
polarity = classifier.classify(extract_features(processed_tweet[0]))
print(polarity)
print(processed_tweet)
print(extract_features(processed_tweet[0]))
insert_sql = """
INSERT INTO {}
(id_str, created_at, text, polarity, user_location)
VALUES (%s, %s, %s, %s, %s)
""".format(settings.table_name)
val = (id_str, created_at, text, polarity, user_location)
with Connection() as db:
db.create_table_if_not_existed(settings.table_name)
db.execute(insert_sql, val)
def RegisterCustomer(customer_name,customer_contact,customer_address,customer_type,customer_email,register_date):
db,cursor = Connection()
search_sql = f"select * from customer where customer_contact = '{customer_contact}';"
insert_sql =f"""insert into customer(customer_name,customer_contact,customer_address,customer_type,
customer_email,register_date)
values('{customer_name.lower()}','{customer_contact}','{customer_address}','{customer_type}',
'{customer_email}','{register_date}');
"""
flag = 0
try:
cursor.execute(search_sql)
rs = cursor.fetchall()
# print(rs)
if(len(rs)>0):
flag = 1
except Exception as e:
print(e)
print("Error connecting to database")
else:
if(flag==1):
# return False
print("Customer is already registered")
elif(flag==0):
cursor.execute(insert_sql)
db.commit()
print("Successfully created account")
# return True
finally:
db.close()
def linear():
"""
Each module will return a list of tuples, one tuple for customer
and one for products. Each tuple will contain 4 values:
- the number of records processed (int),
- the record count in the database prior to running (int),
- the record count after running (int),
- the time taken to run the module (float).
"""
logger.info("Drop all documents")
with Connection():
util_drop_all()
start = time.perf_counter()
num_cust_records = ingest_customer_csv(False)
cust_elapsed = time.perf_counter() - start
num_prod_records = ingest_product_csv(False)
prod_elapsed = time.perf_counter() - cust_elapsed
num_rental_records = ingest_rental_csv(False)
rental_elapsed = time.perf_counter() - (prod_elapsed + cust_elapsed)
ret_list = [
('customer', num_cust_records, 0, num_cust_records, cust_elapsed),
('product', num_prod_records, 0, num_prod_records, prod_elapsed),
('rental', num_rental_records, 0, num_rental_records, rental_elapsed)
]
return ret_list
def ingest_customer_csv(with_lock):
"""
Ingest csv function to combine extract and import gen functions,
and populate data from generator in database
"""
record_count = int(0)
# Extract the CSV file from the zip archive
extract_csv(DATA_ZIP_FILENAME, CUST_CSV_FILENAME, EXTRACT_PATH, with_lock)
# Create a CSV import generator (next yields one db row)
import_generator = import_csv_gen(EXTRACT_PATH + CUST_CSV_FILENAME)
# Skip over the title row
next(import_generator)
# Iterate over all other rows
with Connection():
while True:
try:
data = next(import_generator)
if len(data) != 8:
logger.error(f'Data item count: {len(data)}')
continue
# extract items from list and add document to database
customer = Customer(
user_id=data[CUST_USERID],
name=data[CUST_NAME],
last_name=data[CUST_LAST_NAME],
address=data[CUST_ADDRESS],
phone_number=data[CUST_PHONE],
email=data[CUST_EMAIL],
status=True if data[CUST_STATUS] == 'Active' else False,
credit_limit=int(data[CUST_CREDIT_LIMIT]))
customer.save() # This will perform an insert
record_count += 1
except StopIteration:
break
return record_count
def PlaceOrder(customer_id,product_id,create_date):
buying_quantity = int(input("Enter quantity : "))
search_sql = f"select product_quantity,product_price from product where product_id = {product_id};"
# product_quantity,product_price
db,cursor = Connection()
#print(create_date)
try:
cursor.execute(search_sql)
rs = cursor.fetchall()
product_price = int(rs[0][1])
# print(product_price)
# print(f"Type of product id : {type(rs[0][0])}")
if(int(rs[0][0]) >= buying_quantity):
insert_sql = f"""insert into buying(customer_id,product_id,
buying_quantity,total_bill,buying_date)
values({customer_id},{product_id},{buying_quantity},{product_price*buying_quantity},
'{create_date}'
);
"""
cursor.execute(insert_sql)
update_sql = f"""update product set product_quantity = product_quantity - {buying_quantity}
where product_id = {product_id};
"""
cursor.execute(update_sql)
db.commit()
print("Added to your cart!!")
else:
print("Out of Stock")
except Exception as e:
print(e)
db.rollback()
else:
db.close()
def ingest_product_csv(with_lock):
"""
Ingest csv function to combine extract and import gen functions,
and populate data from generator in database
"""
record_count = int(0)
# Extract the CSV file from the zip archive
extract_csv(DATA_ZIP_FILENAME, PROD_CSV_FILENAME, EXTRACT_PATH, with_lock)
# Create a CSV import generator (next yields one db row)
import_generator = import_csv_gen(EXTRACT_PATH + PROD_CSV_FILENAME)
# Skip over the title row
next(import_generator)
# Iterate over all other rows
with Connection():
while True:
try:
data = next(import_generator)
if len(data) != 4:
logger.error(f'Data item count: {len(data)}')
continue
# extract items from list and add document to database
product = Product(product_id=data[PROD_ID],
description=data[PROD_DESC],
product_type=data[PROD_TYPE],
quantity_available=data[PROD_QTY])
product.save() # This will perform an insert
record_count += 1
except StopIteration:
break
return record_count
def ingest_rental_csv(with_lock):
"""
Ingest csv function to combine extract and import gen functions,
and populate data from generator in database
"""
record_count = int(0)
# Extract the CSV file from the zip archive
extract_csv(DATA_ZIP_FILENAME, RENTAL_CSV_FILENAME, EXTRACT_PATH,
with_lock)
# Create a CSV import generator (next yields one db row)
import_generator = import_csv_gen(EXTRACT_PATH + RENTAL_CSV_FILENAME)
# Skip over the title row
next(import_generator)
# Iterate over all other rows
with Connection():
while True:
try:
data = next(import_generator)
if len(data) != 6:
logger.error(f'Data item count: {len(data)}')
continue
# extract items from list and add document to database
rental = Rental(product_id=data[RENTAL_PROD_ID],
user_id=data[RENTAL_USER_ID])
rental.save() # This will perform an insert
record_count += 1
except StopIteration:
break
return record_count
def add_key(cube, ex_id, key, secret, passphrase):
app.logger.debug('[%s] Adding keys' % (cube))
# Add initial transactions to database
if add_balances(ex_id, key, secret, passphrase, cube):
try:
# Encrypt keys and add connection
conn = Connection(
user_id=cube.user_id,
cube_id=cube.id,
exchange_id=ex_id,
key=e(key),
secret=e(secret),
passphrase=e(passphrase)
)
conn.save_to_db()
app.logger.info('[%s] Added API key for Ex_ID: %s' % (cube, ex_id))
message = 'API keys added successfully. Exchange connection is live!'
cube.log_user_action("save_api_keys")
cube.update_charts = 1
cube.save_to_db()
return message
except Exception as error:
app.logger.debug('[%s] Trouble adding API key for Ex_ID: %s' % (cube, ex_id))
app.logger.debug(error)
remove_balances(ex_id, cube)
db_session.delete(cube)
db_session.commit()
raise
else:
remove_balances(ex_id, cube)
db_session.delete(cube)
db_session.commit()
message = 'There was a problem adding your API keys.'
return message
def main():
"""
Ensure you application will create an empty database if one doesn’t exist
when the app is first run. Call it customers.db
"""
# Standalone function to initialize logging
logger.add(stdout, level='WARNING')
logger.add("logfile_{time}.txt", level='INFO')
logger.enable(__name__)
with Connection():
util_drop_all()
ingest_customer_csv(CSV_PATH_DBG + CUST_CSV_FILENAME)
ingest_product_csv(CSV_PATH_DBG + PROD_CSV_FILENAME)
ingest_rental_csv(CSV_PATH_DBG + RNTL_CSV_FILENAME)
db_dict = show_available_products()
print(db_dict)
db_dict = show_rentals('prd002')
print(db_dict)
def extract_data(table_name):
sql = """
SELECT * from {}
""".format(table_name)
db = Connection()
df = pd.read_sql(sql, db.conn)
return df
def main():
"""This is the main entry point for the program to control gravimetrics """
arguments = process_args()
# Double check all data has been correctly set
if 'host' not in arguments or\
'user' not in arguments or\
'password' not in arguments or\
'database' not in arguments:
print("Error setting database credentials")
print(arguments)
sys.exit(1)
connection = Connection()
connection.create_connection(arguments['host'], arguments['user'],
arguments['password'], arguments['database'])
# If we're testing use the pi name test,
# else we just wanna use the host name
plants = connection.get_plants(
arguments['pi'] if not arguments['testing_mode'] else "1")
# If the retrieval
if plants is None:
sys.stderr.write("No plant information found\nCheck connection\n")
sys.exit(1)
for plant in plants:
perform_reading_update(arguments['pi'], plant, connection,
arguments['watering_mode'])
print("| SUMMARY OF BENCH {0}".format(arguments['pi']))
for plant in plants:
print(plant)
async def save_table(request: Request):
req = await request.json()
base = req['base_name']
cnxn = Connection(cfg.db_system, cfg.db_server, cfg.db_uid, cfg.db_pwd,
base) #TODO
dbo = Database(cnxn, base)
tbl = Table(dbo, req['table_name'])
return {'data': tbl.save(req['records'])}
def clear(agent_id=None, all_agents=False):
"""
Clears the database.
:param agent_id: For an agent.
:param all_agents: For all agents.
:return: Message.
"""
# Clear DB
conn = Connection(common.database_path)
regex = re.compile(r'^\d{,3}-\S+$')
db_agents_list = []
if not int(all_agents):
raw_str = r'^' + "{}".format(int(agent_id)).zfill(3) + r'-\S+$'
regex = re.compile(raw_str)
for db_agent in conn.getDbsName():
if (regex.search(db_agent) != None):
db_agents_list.append(db_agent)
if (db_agents_list.count() <= 0):
raise OssecAPIException(1600)
for db_agent in db_agents_list:
conn.connect(db_agent)
if conn.getDb() != None:
doc = conn.getDb()['pm_event']
if doc != None:
doc.drop()
conn.vacuum()
doc = conn.getDb()['pmCounterInfo']
if doc != None:
doc.drop()
conn.vacuum()
# Clear OSSEC info
if int(all_agents):
rootcheck_files = glob('{0}/queue/rootcheck/*'.format(
common.ossec_path))
else:
if agent_id == "000":
rootcheck_files = [
'{0}/queue/rootcheck/rootcheck'.format(common.ossec_path)
]
else:
agent_info = Agent(agent_id).get_basic_information()
rootcheck_files = glob(
'{0}/queue/rootcheck/({1}) {2}->rootcheck'.format(
common.ossec_path, agent_info['name'], agent_info['ip']))
for rootcheck_file in rootcheck_files:
if path.exists(rootcheck_file):
remove(rootcheck_file)
return "Rootcheck database deleted"
def test_00setup():
global ret_result
# connect and drop the current database
with Connection():
util_drop_all()
# create the test database
ret_result = linear()
def test_00setup():
# connect and drop the current database
with Connection():
util_drop_all()
# create the test database
ingest_customer_csv(CSV_PATH_DBG + CUST_CSV_FILENAME)
ingest_product_csv(CSV_PATH_DBG + PROD_CSV_FILENAME)
ingest_rental_csv(CSV_PATH_DBG + RNTL_CSV_FILENAME)
async def update_schema(request: Request):
req = await request.json()
base = req['base']
config = Dict(json.loads(req['config']))
cnxn = Connection(cfg.db_system, cfg.db_server, cfg.db_uid, cfg.db_pwd,
base) #TODO
dbo = Database(cnxn, base)
schema_name = dbo.schema
schema = Schema(schema_name)
schema.update(dbo, config)
def export_sql(base: str, table: str, dialect: str):
cnxn = Connection(cfg.db_system, cfg.db_server, cfg.db_uid, cfg.db_pwd,
base) #TODO
dbo = Database(cnxn, base)
table = Table(dbo, table)
ddl = table.export_ddl(dialect)
response = StreamingResponse(io.StringIO(ddl), media_type="txt/plain")
response.headers[
"Content-Disposition"] = f"attachment; filename={table.name}.sql"
return response
def login(response: Response, brukernavn: str, passord: str):
Connection(cfg.db_system, cfg.db_server, brukernavn, passord, cfg.db_name)
timestamp = time.time()
token = jwt.encode(
{
"uid": brukernavn,
"pwd": passord,
"timestamp": timestamp
}, cfg.secret_key)
response.set_cookie(key="session", value=token, expires=cfg.timeout)
return {"success": True}
def get_children(base: str, table: str, primary_key: str):
cnxn = Connection(cfg.db_system, cfg.db_server, cfg.db_uid, cfg.db_pwd,
base) #TODO
base_path = base or schema
dbo = Database(cnxn, base_path)
tbl = Table(dbo, table)
tbl.offset = 0
tbl.limit = 30
pk = json.loads(primary_key)
record = Record(dbo, tbl, pk)
return {'data': record.get_children()}
def get_record(base: str, table: str, primary_key: str, schema: str = None):
cnxn = Connection(cfg.db_system, cfg.db_server, cfg.db_uid, cfg.db_pwd,
base) #TODO
if cnxn.system == 'postgres' and schema:
base_path = base + '.' + schema
else:
base_path = base or schema
dbo = Database(cnxn, base_path)
tbl = Table(dbo, table)
pk = json.loads(primary_key)
record = Record(dbo, tbl, pk)
return {'data': record.get()}
def DeleteProduct(product_id):
db, cursor = Connection()
delete_sql = f"delete from product where product_id={product_id};"
try:
cursor.execute(delete_sql)
db.commit()
except Exception:
db.rollback()
print("Some error in deleting the product")
else:
print("Product deleted successfully")
finally:
db.close()
def GetAllCustomers():
db,cursor = Connection()
search_sql = "select * from customer;"
try:
cursor.execute(search_sql)
rs = cursor.fetchall()
except Exception:
print("Error in loading customers")
else:
print("Customer Id".center(20)+"Customer Name".center(30)+"Customer contact".center(30))
for x in rs:
print(f"{str(x[0]).center(20)}{x[1].center(30)}{x[2].center(30)}")
finally:
db.close()
def add_new_connection(json_data):
session = init()
new_connection = Connection(question_id=json_data["connection"][0], connected_to_id=json_data["connection"][1])
session.add(new_connection)
try:
session.commit()
session.close()
return {"message":"Success"}
except:
session.rollback()
session.close()
print("Error: Could not add connection")
return{"message":"Error"}
def UpdateQuantity(product_id, product_quantity):
try:
db, cursor = Connection()
update_sql = f"""
update product set product_quantity = product_quantity + {product_quantity}
where product_id like '{product_id}';
"""
cursor.execute(update_sql)
except Exception:
db.rollback()
else:
db.commit()
print("Quantity updated successfully !")
finally:
db.close()
def create_app(config):
app = Flask(__name__)
app.config.from_object(configurations.get(config, 'development'))
# initialise flask restplus routes
api.init_app(app, authorizations=authorizations)
app.register_blueprint(todo)
db = Connection(app.config.get('DATABASE_URL'))
app.db = db
# import views
import apps.cinema.views
return app, db
async def get_select(request: Request):
# todo: skal ikke behøve alias
req = Dict(
{item[0]: item[1]
for item in request.query_params.multi_items()})
cnxn = Connection(cfg.db_system, cfg.db_server, cfg.db_uid, cfg.db_pwd,
req.base) #TODO
dbo = Database(cnxn, req.base)
tbl = Table(dbo, req.table)
if 'key' in req:
key = json.loads(req.key)
colname = key[-1]
else:
colname = self.get_primary_key()[-1]
col = Column(tbl, colname)
data = col.get_select(req)
return data
def last_scan(agent_id):
"""
Gets the last scan of the agent.
:param agent_id: Agent ID.
:return: Dictionary: end, start.
"""
# Connection
db_url = common.database_path
conn = Connection(db_url)
conn.connect(conn.getDbById(str(agent_id).zfill(3)))
if (conn.getDb() == None):
raise OssecAPIException(1600)
data = {}
lastSyscheckEndTime = None
lastSyscheckEndTimeObj = list((conn.getDb()['pm_event'].find({
"log":
'Ending syscheck scan.'
}).sort([('date_last', -1)]).limit(1)))[0]
if (lastSyscheckEndTimeObj != None):
lastSyscheckEndTime = lastSyscheckEndTimeObj.get('date_last')
if lastSyscheckEndTime != None:
data['end'] = (lastSyscheckEndTime +
timedelta(seconds=timeoffset)).__str__()
else:
data['end'] = lastSyscheckEndTime.__str__()
lastSyscheckStartTime = None
lastSyscheckStartTimeObj = list((conn.getDb()['pm_event'].find({
"log":
'Starting syscheck scan.'
}).sort([('date_last', -1)]).limit(1)))[0]
if (lastSyscheckStartTimeObj != None):
lastSyscheckStartTime = lastSyscheckStartTimeObj.get('date_last')
if lastSyscheckStartTime != None:
data['start'] = (lastSyscheckStartTime +
timedelta(seconds=timeoffset)).__str__()
else:
data['start'] = lastSyscheckStartTime.__str__()
return data
def __listen(self):
print("Listening...")
cursor = Connection().getInstance()
while True:
data, clientInfo = self.incoming.receive()
clientIP, clientPort = clientInfo
print("DEBUG: Received from " + clientIP + ":" + str(clientPort))
print("DEBUG: ", data)
packet = Packet(raw_data=data)
type = packet.get_type()
print("DEBUG: Type ->", type)
if type == PacketType.MESSAGE:
print("message: ", packet.get())
if type == PacketType.PING:
outgoing_packet = Packet(PacketType.PING)
outgoing_packet.add("pong")
self.outgoing.send(outgoing_packet, clientIP, clientPort)
if type == PacketType.LOGIN:
outgoing_packet = Packet(PacketType.LOGIN)
login = packet.get()
password = packet.get()
print("DEBUG: Login ->", login)
print("DEBUG: Password ->", password)
querry = "SELECT * FROM `users` WHERE `username`=? AND `password`=?"
cursor.execute(querry, (login, password))
users = cursor.fetchall()
if len(users) == 1:
user = users[0]
print("DEBUG: Login success")
outgoing_packet.add(1)
self.users.append([user[0], user[1], clientIP, clientPort])
else:
print("DEBUG: Login failed")
outgoing_packet.add(0)
self.outgoing.send(outgoing_packet, clientIP, clientPort)
