def confirm_wallet_event(gateway_event):
wallet_event = db.session.query(db.WalletsEvent).\
filter_by(source_id=gateway_event.id).first()
wallet_event.status = 'done'
db.commit()
return wallet_event
def set_default_flag(self, state):
if state != True and state != False:
raise ValueError("Requires True or False")
if state == True:
flag = 1
elif state == False:
flag = 0
db = database.connect()
cursor = database.get_cursor(db)
# Get id of own type
cursor.execute("select id from address_types where address_type = :input_type", \
input_type = self.get_type())
type_id = cursor.fetchone()[0]
if state == True and self.get_person():
# If address has a person remove other true flags so only one remains of this type
cursor.execute("update addresses set default_flag = 0 \
where person_id = :input_id and default_flag = 1 \
and address_type_id = :input_tid" , \
input_id = self.get_person().get_id(), input_tid = type_id)
database.commit(db)
cursor.execute("update addresses set default_flag = :input_flag \
where id = :input_id" , \
input_flag = flag, input_id = self.get_id())
database.commit(db)
database.disconnect(db)
def new_order(customer):
db = database.connect()
cursor = database.get_cursor(db)
returned_id = cursor.var(database.cx_Oracle.NUMBER)
cursor.execute("select id from order_statuses where order_status = :input_status", \
input_status = "pending")
status_id = cursor.fetchone()[0]
if isinstance(customer, person.Person):
cursor.execute("insert into orders (person_id, status_id) \
values (:input_pid, :input_sid) \
returning id into :output_id" , \
input_pid = customer.get_id(), input_sid = status_id, \
output_id = returned_id)
database.commit(db)
else:
raise ValueError("Requires valid person instance")
returned_id = int(returned_id.getvalue())
this_order = Order(returned_id)
database.disconnect(db)
ship_addr = customer.get_default_address("shipping")
if ship_addr:
this_order.modify_shipping_address(ship_addr)
return this_order
def dropCourse(self, idnum):
sidnum = str(idnum)
CRN = input(
"Enter the CRN code of the course you would like to remove")
sql_command = """SELECT 1 FROM COURSE WHERE CRN = """ + CRN + """;"""
database.cursor.execute(sql_command)
query_result = database.cursor.fetchall()
if query_result == []:
print("The CRN code does not exist")
return
else:
sql_command = """SELECT 1 FROM STUDENTCOURSE WHERE STUDENTID = """ + sidnum + """ AND CRN = """ + CRN + """; """
database.cursor.execute(sql_command)
query_result = database.cursor.fetchall()
if query_result == []:
print("The your not taking this course")
return
else:
sql_command = """DELETE FROM STUDENTCOURSE WHERE STUDENTID = """ + sidnum + """ AND CRN = """ + CRN + """; """
database.cursor.execute(sql_command)
sql_command = """SELECT TITLE FROM COURSE WHERE CRN = """ + CRN + """; """
database.cursor.execute(sql_command)
query_result = database.cursor.fetchall()
for i in query_result:
print(i)
print(" Has been removed from your courses to your courses")
database.commit()
return
def cmd_download_artist_schedule(*args):
"""queues retrieving more tracks by the specified artists"""
from ardj.database import DownloadRequest, commit
for arg in args[1:]:
if DownloadRequest.find_by_artist(arg) is None:
DownloadRequest(artist=arg, owner="console").put()
commit()
def adminadd(self):
dpt = input(
"What department are you adding the course to? insert '' marks for string inputs\n"
)
sql_command = """SELECT INSTRUCTOR.ID,INSTRUCTOR.NAME, INSTRUCTOR.SURNAME FROM INSTRUCTOR WHERE INSTRUCTOR.DEPT = """ + dpt + """ ;
"""
print(sql_command)
database.cursor.execute(sql_command)
query_result = database.cursor.fetchall()
print("Here are the available instructors from " + dpt + "\n")
for i in query_result:
print(i)
id = input(
"Enter the available instructors id too add this instructor to the course\n"
)
title = input("Enter the title of the course ''\n")
CRN = input("Create the CRN code for this course")
time = input("Enter time for course ''")
days = input("Enter days of course ''")
semester = input("Enter the semester for this course ''")
year = input("Enter the year for this course ''")
credits = input("Enter the amount of credits for this course")
sql_command = """ INSERT INTO COURSE VALUES (""" + title + """,""" + CRN + """,""" + dpt + """,""" + id + """,""" + time + """,""" + days + """,""" + semester + """,""" + year + """,""" + credits + """); """
print(sql_command)
database.cursor.execute(sql_command)
print(title + " Has been successfully added to the database")
database.commit()
def POST(self):
try:
args = web.input(track_id="", token=None)
logging.debug("Vote request: %s" % args)
sender = auth.get_id_by_token(args.token)
if sender is None:
raise web.forbidden("Bad token.")
if args.track_id.isdigit():
track_id = int(args.track_id)
else:
track_id = tracks.get_last_track_id()
weight = tracks.add_vote(track_id, sender, self.vote_value)
if weight is None:
return {"status": "error", "message": "No such track."}
database.commit()
message = 'OK, current weight of track #%u is %.04f.' % (track_id, weight)
return {
"status": "ok",
"message": message,
"id": track_id,
"weight": weight,
}
except web.Forbidden:
raise
except Exception, e:
log.log_error(str(e), e)
return {"status": "error", "message": str(e)}
def cmd_download_artist_schedule(*args):
"""queues retrieving more tracks by the specified artists"""
from ardj.database import DownloadRequest, commit
for arg in args[1:]:
if DownloadRequest.find_by_artist(arg) is None:
DownloadRequest(artist=arg, owner="console").put()
commit()
def __create_db(cports: list, changes: list, conn: sqlite3.Connection,
cursor: sqlite3.Cursor):
'''
This function writes data to sqlite
cports, changes: result from parser
conn, cursor: database related
'''
cursor.execute('DELETE FROM CPORTS')
sqlStr = db.genBaseSqlStr(cports, 'CPORTS')
for item in cports:
values = (int(item['processID']), item['processName'],
item['protocol'], item['localPort'], item['localAddress'],
item['remotePort'], item['remoteAddress'], item['state'],
item['processPath'], item['pathType'])
db.insertTable(cursor, sqlStr, values)
db.commit(conn)
cursor.execute('DELETE FROM CPORTS_CHANGELIST')
sqlStr = db.genBaseSqlStr(changes, 'CPORTS_CHANGELIST')
for item in changes:
values = (item['programName'], item['date'], item['status'],
item['protocol'], item['source'], item['destination'])
db.insertTable(cursor, sqlStr, values)
db.commit(conn)
return
def modify_address(self, new_address_reference):
# Ensuring address' ID is in addresses table AND it is a billing address
db = database.connect()
cursor = database.get_cursor(db)
cursor.execute("select id from addresses \
where id = :input_id" , \
input_id = new_address_reference.get_id())
address_id = cursor.fetchone()
if not address_id:
raise ValueError("Address not found in addresses table")
else:
address_id = address_id[0]
address_reference = address.Address(address_id)
if not address_reference.get_type() == "billing":
raise ValueError("Address must be billing type. Type is %s." %
address_reference.get_type())
cursor.execute("update credit_cards set billing_addr_id = :input_addr \
where id = :input_id" ,\
input_addr = address_id, input_id = self.get_id())
database.commit(db)
database.disconnect(db)
def modify_expiration_date(self, new_expiration_month,
new_expiration_year):
# Type- and value-checking inputs
if not isinstance(new_expiration_month, int):
raise ValueError("Expiration month must be integer value")
if not isinstance(new_expiration_year, int):
raise ValueError("Expiration year must be integer value")
if new_expiration_month > 12 or new_expiration_month < 1:
raise ValueError("Expiration month must be between 1 and 12")
if not len(str(new_expiration_year)) == 4:
raise ValueError("Expiration year must be 4 digit integer")
# Formatting the date
expiration_date = format_date(new_expiration_month,
new_expiration_year)
db = database.connect()
cursor = database.get_cursor(db)
cursor.execute("update credit_cards set expiration_date = :input_date \
where id = :input_id" ,\
input_date = expiration_date, input_id = self.get_id())
database.commit(db)
database.disconnect(db)
async def unregister(ctx: discord.ext.commands.Context):
if await isRegistered(ctx) == False:
return
if await inPublicChannel(
ctx,
msg=
f"Hey, <@{ctx.author.id}>, don't leave a team in public channels..."
):
return
if SETTINGS['registration'] == 'disabled':
await ctx.send("registration is disabled")
return
with db.db_session:
user = db.User.get(name=username(ctx))
if user == None:
logger.debug(f"user {username(ctx)} not registered to play.")
await ctx.send("You weren't registered")
return
if SETTINGS['_debug'] and SETTINGS['_debug_level'] > 0:
logger.debug(f"{user.name} left team {user.team.name}")
await ctx.send(f"Leaving team {user.team.name}... bye.")
unaffiliated = db.Team.get(name='__unaffiliated__')
user.team = unaffiliated
db.commit()
def create_token(login, login_type=None):
if login_type == "jid":
raise RuntimeError("Tokens can only be sent by email.")
token = Token.create(login)
TokenMailer(login, token["token"]).deliver()
commit()
return token
def new_warehouse(capacity,
street,
city,
state_string,
zip_code,
apartment_no=None):
# Adding new warehouse to database, and returning reference to that warehouse
db = database.connect()
cursor = database.get_cursor(db)
warehouse_address = address.Address.new_address(street, city, state_string, zip_code,\
"warehouse", apt_no = apartment_no)
address_id = warehouse_address.get_id()
if isinstance(capacity, (int, float)):
returned_id = cursor.var(database.cx_Oracle.NUMBER)
capacity = int(capacity)
cursor.execute("insert into warehouses \
(capacity, address_id) values (:input_capacity, :input_address) \
returning id into :new_warehouse_id" , \
input_capacity = capacity, input_address = address_id, \
new_warehouse_id = returned_id)
database.commit(db)
returned_id = int(returned_id.getvalue())
database.disconnect(db)
return Warehouse(returned_id)
def modify_quantity(self, product, new_quantity):
db = database.connect()
cursor = database.get_cursor(db)
if isinstance(new_quantity, int) and new_quantity >= 0:
product_id = product.get_id()
cursor.execute("select quantity from warehouse_to_product \
where product_id = :input_pid and warehouse_id = :input_wid" , \
input_pid = product_id, input_wid = self.get_id())
current_quantity = cursor.fetchone()
if current_quantity:
# Ensuring product is in warehouse stock
current_quantity = int(current_quantity[0])
if current_quantity == new_quantity:
# Do nothing if quantity doesn't change
pass
elif new_quantity == 0:
# Remove line in DB is new quantity is zero
cursor.execute("delete from warehouse_to_product \
where product_id = :input_pid and warehouse_id = :input_wid" , \
input_pid = product_id, input_wid = self.get_id())
database.commit(db)
else:
# Otherwise just update the quantity
cursor.execute("update warehouse_to_product set quantity = :input_quantity \
where product_id = :input_pid and warehouse_id = :input_wid" , \
input_quantity = new_quantity, input_pid = product_id, \
input_wid = self.get_id())
database.commit(db)
else:
raise ValueError("new quantity must be positive integer value")
database.disconnect(db)
def create_car_brand_table(conn):
""" The method creates a database table of car brands.
It crawls bilbasen.dk for a list of car brands, and creates
and stores these brands in a table called 'Brands' in the
database. The method is called each time the method
download_data_to_database is called.
"""
conn.request("GET", "/")
res = conn.getresponse()
content = res.read()
parsed_html = BeautifulSoup(content, from_encoding='utf8')
cur, con = database.connect_to_database()
# delete the old table
if database.check_if_table_exist(cur, 'Brands'):
database.delete_table(cur, 'Brands')
cur.execute("CREATE TABLE Brands(Brand CHAR(100))")
# extract car brands and insert to database table
brands_tags = parsed_html.find("optgroup", {"label": "Alle mærker"})
for child in brands_tags.children:
brand = smart_str(child.string)
if any(c.isalpha() for c in brand):
command = "INSERT INTO Brands(Brand) VALUES('%s')" \
% smart_str(brand)
cur.execute(command)
database.commit(cur, con)
def weekend(weekend_id=None):
uri = request.url_root + "api/v1/"
if request.method == "POST":
week_of = str(request.form["week_of"])
place = request.form["place"]
ok, weekend = database.post_weekend(week_of=week_of, place=place)
if ok:
database.commit()
return resource.weekend(uri, weekend), 201
return "Resource Already exists", 409
if request.method == "GET" and weekend_id:
ok, weekend = database.get_weekend(weekend_id)
if ok:
return resource.weekend(uri, weekend), 200
return "Resource not found", 404
if request.method == "GET":
ok , weekend = database.get_weekend()
if ok:
return resource.weekend(uri, weekend), 200
return "Resource not found", 404
if request.method == "DELETE" and weekend_id:
ok = database.del_weekend(weekend_id)
if ok:
database.commit()
return "", 200
return "Resource Not Found", 404
return "Resource Not Found", 404
def adminadduser(self):
choice = input("Too add a user:S for student or I for instructor\n")
if (choice == 'S'):
id = input("Enter a unique id for the student\n")
fname = input("Enter the first name of the student''\n")
sname = input("Enter the last name of the student''\n")
grady = input("Enter the students graduation year\n")
major = input("Enter the students major''\n")
email = input("Enter the students email''\n")
sql_command = """ INSERT INTO STUDENT VALUES (""" + id + """,""" + fname + """,""" + sname + """,""" + grady + """,""" + major + """,""" + email + """); """
database.cursor.execute(sql_command)
print(sname + " " + fname + " Has been added to the database\n")
database.commit()
elif (choice == 'I'):
id = input("Enter a unique id for the instructor\n")
fname = input("Enter the first name of the instructor''\n")
sname = input("Enter the last name of the instructor''\n")
title = input("Enter the instructors title''\n")
hirey = input("Enter the hire year for the instructor\n")
dept = input("Enter the insturtors dept.''\n")
email = input("Enter the instructors email''\n")
sql_command = """ INSERT INTO INSTRUCTOR VALUES (""" + id + """,""" + fname + """,""" + sname + """,""" + title + """,""" + hirey + """,""" + dept + """,""" + email + """); """
database.cursor.execute(sql_command)
print(sname + " " + fname + " Has been added to the database\n")
database.commit()
else:
print("Invalid choice\n")
def POST(self):
args = web.input(
token=None,
id=None,
title=None,
artist=None,
tag=[])
sender = auth.get_id_by_token(args.token)
if sender is None:
raise UsageError("bad token")
track = database.Track.get_by_id(int(args.id))
if track is None:
raise UsageError("track not found")
if args.artist:
track["artist"] = args.artist
log_debug("{0} set artist for track {1} to {2}",
sender, args.id, args.artist)
if args.title:
track["title"] = args.title
log_debug("{0} set title for track {1} to {2}",
sender, args.id, args.title)
track.put()
if args.tag:
track.set_labels(args.tag)
log_debug("{0} set labels for track {1} to {2}",
sender, args.id, ", ".join(args.tag))
database.commit()
return {"success": True}
def remove_product(self, product):
db = database.connect()
cursor = database.get_cursor(db)
product_id = product.get_id()
cursor.execute("select quantity from warehouse_to_product \
where product_id = :input_pid and warehouse_id = :input_wid" , \
input_pid = product_id, input_wid = self.get_id())
current_quantity = cursor.fetchone()
if current_quantity:
# The item is already in this table in the DB, just decriment quantity
decrimented_quantity = int(current_quantity[0]) - 1
if decrimented_quantity > 0:
# Removing one will not remove all instances of that product
cursor.execute("update warehouse_to_product set quantity = :input_quantity \
where product_id = :input_pid and warehouse_id = :input_wid" , \
input_quantity = decrimented_quantity, input_pid = product_id, \
input_wid = self.get_id())
database.commit(db)
else:
# Remove the line from the DB if product has quantity of zero
cursor.execute("delete from warehouse_to_product \
where product_id = :input_pid and warehouse_id = :input_wid" , \
input_pid = product_id, input_wid = self.get_id())
database.commit(db)
else:
# The item is not yet in the warehouse's stock, so do nothing
pass
database.disconnect(db)
def processRequest(req):
debug = wsgi_app.debug
c = req.path_info_pop()
func = getattr(cmd, 'cmd_' + c, None)
if func != None:
if debug:
debugprint('->', '/' + c, req.body)
try:
data = json.loads(req.body.decode('utf8'))
except:
return badRequest()
try:
resp = func(data)
resp = json.dumps(resp)
except:
database.rollback()
raise
else:
database.commit()
if debug:
debugprint('<-', resp)
return webob.Response(resp, 200)
return notFound()
def new_product(name,
type_string,
description=None,
nutrition_facts=None,
alcohol_content=None,
size=1):
# Adding new product to database, returning reference to new product instance
db = database.connect()
cursor = database.get_cursor(db)
# Getting type_id from type_string, ensuring it's a valid type string (returns int ID)
cursor.execute(
"select id from product_types where product_type = :input_type",
input_type=type_string)
type_id = cursor.fetchone()[0]
if type_id:
if isinstance(type_id, int):
# Adding new product to database (ONLY name and type_id) and retrieving generated product ID
returned_id = cursor.var(database.cx_Oracle.NUMBER)
cursor.execute("insert into products (name, product_type_id, product_size) values \
(:product_name, :ptype_id, :psize) returning id into :new_product_id" , product_name = name, \
ptype_id = type_id, new_product_id = returned_id, psize = size)
database.commit(db)
# Getting product reference
returned_id = int(returned_id.getvalue())
new_product = Product(returned_id)
# Now adding description, nutrition facts, and alcohol content (automatically generating defaults)
new_product.modify_description(description)
new_product.modify_nutrition_facts(nutrition_facts)
new_product.modify_alcohol_content(alcohol_content)
return new_product
def POST(self):
try:
args = web.input(track_id="", token=None)
logging.debug("Vote request: %s" % args)
sender = auth.get_id_by_token(args.token)
if sender is None:
raise web.forbidden("Bad token.")
if args.track_id.isdigit():
track_id = int(args.track_id)
else:
track_id = tracks.get_last_track_id()
weight = tracks.add_vote(track_id, sender, self.vote_value)
if weight is None:
return {"status": "error", "message": "No such track."}
database.commit()
message = 'OK, current weight of track #%u is %.04f.' % (track_id, weight)
return {
"status": "ok",
"message": message,
"id": track_id,
"weight": weight,
}
except web.Forbidden:
raise
except Exception, e:
log.log_exception(str(e), e)
return {"status": "error", "message": str(e)}
def remove(self):
db = database.connect()
cursor = database.get_cursor(db)
cursor.execute("delete from products where id = :input_id",
input_id=self.get_id())
database.commit(db)
database.disconnect(db)
def appendNew(cls, title: str, url: str,
image_url: str) -> 'BookmarkService':
"""
添加新的书签
"""
no_slash_url = url.split("#")[0]
base_url = no_slash_url.split('?')[0]
domain = no_slash_url.split('/')[2]
bookmark = BookMark.filter(BookMark.url == no_slash_url).first()
if not bookmark:
bookmark = BookMark(domain=domain,
title=title,
base_url=base_url,
url=no_slash_url,
image_url=image_url,
post_count=1,
hidden=0)
insert(bookmark)
commit()
conn = cls.redis_conn()
conn.set('NEWEST:alva', str(bookmark.id))
else:
bookmark.post_count += 1
commit()
return BookmarkService().withId(bookmark.id)
def modify_name(self, new_name):
db = database.connect()
cursor = database.get_cursor(db)
if new_name:
cursor.execute("update products set name = :name_string where id = :product_id", name_string = new_name, \
product_id = self.get_id())
database.commit(db)
database.disconnect(db)
def modify_size(self, new_size):
# ensuring it's a number:
db = database.connect()
cursor = database.get_cursor(db)
if isinstance(new_size, (int, float, long)):
cursor.execute("update products set product_size = :psize where id = :product_id", \
psize = new_size, product_id = self.get_id())
database.commit(db)
database.disconnect(db)
def remove(self):
c = database.cursor()
c.execute('''
DELETE FROM room_queue
WHERE item_id = ?'''
, (self.item_id,))
database.commit()
def GET(self):
args = web.input(track=None, token=None)
if args.track:
sender = auth.get_id_by_token(args.token)
console.on_queue("-s " + str(args.track), sender or "Anonymous Coward")
database.commit()
return {"status": "ok"}
def _store_city(resp):
"""Check cities exists in the DB, and if not stores them in the places table."""
city = resp['city']
row = database.query_row('SELECT COUNT(*) FROM Place WHERE Id = ?', (city['id'],))
if (row[0] == 0):
database.execute('INSERT INTO Place (Id, Name, CoordLon, CoordLat, Country) VALUES (?,?,?,?,?)', (city['id'], city['name'], city['coord']['lon'], city['coord']['lat'], city['country']))
database.commit()
app.logger.info('Place %s added', city['name'])
return city['id']
def new_person(username, password, first_name, last_name, type_string, middle_initial = None, \
salary = None, job_title = None):
db = database.connect()
cursor = database.get_cursor(db)
returned_id = cursor.var(database.cx_Oracle.NUMBER)
# getting person type ID from type_string
cursor.execute("select id from person_types where person_type = :input_type", \
input_type = type_string)
type_id = cursor.fetchone()
if type_id:
type_id = type_id[0]
else:
raise ValueError("Type given (%s) not valid person type" %
str(type_string))
hashed_password = hash_password(username, password)
if middle_initial:
# Trimming middle initial
middle_initial = middle_initial[0]
cursor.execute("insert into persons \
(username, password, first_name, middle_initial, last_name, \
person_type_id, balance) \
values (:input_username, :input_password, \
:input_first, :input_middle, :input_last, :input_type_id, 0) \
returning id into :output_id" , \
input_username = username, input_password = hashed_password, \
input_first = first_name, input_middle = middle_initial, \
input_last = last_name, input_type_id = type_id, \
output_id = returned_id)
database.commit(db)
else:
cursor.execute("insert into persons \
(username, password, first_name, last_name, \
person_type_id, balance) \
values (:input_username, :input_password, \
:input_first, :input_last, :input_type_id, 0) \
returning id into :output_id" , \
input_username = username, input_password = hashed_password, \
input_first = first_name, \
input_last = last_name, input_type_id = type_id, \
output_id = returned_id)
database.commit(db)
database.disconnect(db)
returned_id = returned_id.getvalue()
reference = Person(returned_id)
if salary:
reference.modify_salary(salary)
if job_title:
reference.modify_job_title(job_title)
return reference
def inventory_refill():
"""\
Sets the stock of all drugs in the inventory to its maximum capacity
"""
inventory = get_inventory()
for i in inventory:
i.stock = i.capacity
database.update_inventory(i)
database.commit()
def adminremove(self):
CRN = input("Enter the CRN for the course you want to remove")
database.cursor.execute("""SELECT TITLE FROM COURSE WHERE CRN = """ +
CRN + """ ;""")
print("Has been removed from database")
sql_command = """DELETE FROM COURSE WHERE CRN = """ + CRN + """; """
database.cursor.execute(sql_command)
sql_command = """DELETE FROM STUDENTCOURSE WHERE CRN = """ + CRN + """; """
database.cursor.execute(sql_command)
database.commit()
def modify_balance(self, addition):
db = database.connect()
cursor = database.get_cursor(db)
new_balance = self.get_balance() + addition
cursor.execute("update persons set balance = :input_balance where id = :input_id", \
input_balance = new_balance, input_id = self.get_id())
database.commit(db)
database.disconnect(db)
def GET(self):
args = web.input(track=None, token=None)
if not args.track:
raise UsageError("track id not specified (id)")
sender = auth.get_id_by_token(args.token)
console.on_queue("-s " + str(args.track), sender or "Anonymous Coward")
database.commit()
return {"success": True}
def change_teamname(self, oldname:str, newname:str):
"""Allows you to update a team if a user made a mistake or is undesirable..."""
team = db.Team.get(name=oldname)
resp = input(f"Are you sure you want to change team '{oldname}' to '{newname}'? [y/N]")
if resp == 'y':
team.name = newname
db.commit()
print("Done.")
return
print('Cancelling... ')
def modify_job_title(self, new_title):
db = database.connect()
cursor = database.get_cursor(db)
cursor.execute("update persons \
set job_title = :input_title \
where id = :input_id" , \
input_title = str(new_title), input_id = self.get_id())
database.commit(db)
database.disconnect(db)
def update_rank(self, rank):
self.__rank = rank
c = database.cursor()
c.execute('''
UPDATE room_queue
SET rank = ?
WHERE item_id = ?'''
, (rank, self.item_id))
database.commit()
def cmd_xmpp_send(*args):
"""send a Jabber message"""
if len(args) < 1:
print "Usage: ardj xmpp-send \"message text\" [recipient_jid]"
exit(1)
recipient = None
if len(args) > 1:
recipient = args[1].decode("utf-8")
from database import Message, commit
Message(message=args[0].decode("utf-8"), re=recipient).put()
commit()
def create(name, owner):
c = database.cursor()
c.execute('''
INSERT INTO rooms (
name
, owner)
VALUES(?, ?)'''
, (name, owner.user_id))
room_id = c.lastrowid
database.commit()
return room_id
def create(name, password):
password_hashed = pwd_context.encrypt(password)
c = database.cursor()
c.execute('''
INSERT INTO users (
name
, password)
VALUES(?, ?)'''
, (name, password_hashed))
user_id = c.lastrowid
database.commit()
return user_id
def create_test_table():
""" Create a test table to have specific data to test on. """
cur, con = database.connect_to_database()
if not database.check_if_table_exist(cur, 'testTable'):
sql = "CREATE TABLE testTable (Model CHAR(100), Link CHAR(100), \
Description MEDIUMTEXT, Kms INT(20), Year INT(4), \
Kml FLOAT(20), Kmt FLOAT(20), Moth CHAR(30), Trailer CHAR(30), \
Location CHAR(50), Price INT(20))"
database.query(cur, sql)
sql = "INSERT INTO testTable(Model, Link, Description, Kms, Year, \
Kml, Kmt, Moth, Trailer, Location, Price) VALUES('%s', '%s', '%s', \
'%s','%s','%s','%s','%s','%s','%s','%s')" % (
'Audi A1', 'www.test.dk/audi1', 'Audi A1 description text',
'100', '2000', '2', '6', '0', 'No trailer', 'Sjælland', '50000')
database.query(cur, sql)
sql = "INSERT INTO testTable(Model, Link, Description, Kms, Year, \
Kml, Kmt, Moth, Trailer, Location, Price) VALUES('%s', '%s', '%s', \
'%s','%s','%s','%s','%s','%s','%s','%s')" % (
'Audi A2', 'www.test.dk/audi2', 'Audi A2 description text',
'200', '2001', '3', '7', '0', 'No trailer', 'Fyn', '60000')
database.query(cur, sql)
sql = "INSERT INTO testTable(Model, Link, Description, Kms, Year, \
Kml, Kmt, Moth, Trailer, Location, Price) VALUES('%s', '%s', '%s', \
'%s','%s','%s','%s','%s','%s','%s','%s')" % (
'Audi A3', 'www.test.dk/audi3', 'Audi A3 description text',
'300', '2002', '4', '8', '0', 'No trailer', 'Bornholm', '70000')
database.query(cur, sql)
sql = "INSERT INTO testTable(Model, Link, Description, Kms, Year, \
Kml, Kmt, Moth, Trailer, Location, Price) VALUES('%s', '%s', '%s', \
'%s','%s','%s','%s','%s','%s','%s','%s')" % (
'Ford Mondeo', 'www.test.dk/ford1', 'Ford Mondeo description text',
'400', '2003', '5', '12', '0', 'No trailer', 'Jylland', '8000')
database.query(cur, sql)
sql = "INSERT INTO testTable(Model, Link, Description, Kms, Year, \
Kml, Kmt, Moth, Trailer, Location, Price) VALUES('%s', '%s', '%s', \
'%s','%s','%s','%s','%s','%s','%s','%s')" % (
'Mercedes SLK', 'www.test.dk/mercedes1',
'Mercedes SLK description text',
'500', '2004', '2', '4', '0', 'No trailer', 'Fyn', '100000')
database.query(cur, sql)
database.commit(cur, con)
def create(room_id, rank, service, url, title, duration, start_time):
c = database.cursor()
c.execute('''
INSERT INTO room_queue (
room_id
, rank
, service
, url
, title
, duration
, start_time)
VALUES(?, ?, ?, ?, ?, ?, ?)'''
, (room_id, rank, service, url, title, duration, start_time))
item_id = c.lastrowid
database.commit()
return item_id
def cmd_fix_artist_names(*args):
"""correct names according to Last.fm"""
from ardj.scrobbler import LastFM
from ardj.database import Track, commit
cli = LastFM().authorize()
if cli is None:
print "Last.fm authentication failed."
return False
names = Track.get_artist_names()
print "Correcting %u artists." % len(names)
for name in names:
new_name = cli.get_corrected_name(name)
if new_name is not None and new_name != name:
logging.info("Correcting artist name \"%s\" to \"%s\"" % (name.encode("utf-8"), new_name.encode("utf-8")))
Track.rename_artist(name, new_name)
commit()
def _store_measurements(place_id, resp):
"""Stores the measurement values in the database."""
i = 0
j = 0
for item in resp['list']:
m = {}
dt = datetime.datetime.utcfromtimestamp(int(item['dt']))
if 'main' in item:
if 'temp' in item['main']:
m[MT_TEMP] = item['main']['temp']
if 'temp_min' in item['main']:
m[MT_TEMP_MIN] = item['main']['temp_min']
if 'temp_max' in item['main']:
m[MT_TEMP_MAX] = item['main']['temp_max']
if 'pressure' in item['main']:
m[MT_PRESS] = item['main']['pressure']
if 'sea_level' in item['main']:
m[MT_PRESS_SEALEV] = item['main']['sea_level']
if 'grnd_level' in item['main']:
m[MT_PRESS_GRNDLEV] = item['main']['grnd_level']
if 'humidity' in item['main']:
m[MT_HUMID] = item['main']['humidity']
if 'wind' in item:
if 'speed' in item['wind']:
m[MT_WIND_SPEED] = item['wind']['speed']
if 'deg' in item['wind']:
m[MT_WIND_DEG] = item['wind']['deg']
if 'rain' in item:
if '3h' in item['rain']:
m[MT_RAIN_3H] = item['rain']['3h']
for key in m:
row = database.query_row('SELECT COUNT(*) FROM Measurement WHERE Time = ? AND Place = ? AND Type = ?', (dt, place_id, key))
if row[0] == 0:
(rowcount, lastid) = database.execute('INSERT INTO Measurement (Time, Place, Type, Value) VALUES (?,?,?,?)', (dt, place_id, key, m[key]))
i = i + 1
else:
j = j + 1
database.commit()
app.logger.info('%s measurements inserted for %s' % (i, place_id))
if j > 0:
app.logger.info('%s measurements skipped for %s' % (j, place_id))
def create_token(login, login_type):
if login_type == "jid":
login = resolve_alias(login)
tmp = tempfile.mktemp(suffix="", prefix="", dir="")
token = Token(token=tmp, login=login, login_type=login_type, active=1)
token.put(force_insert=True)
base_url = get_setting("web_api_root", "http://localhost:8080").rstrip("/")
url = "%s/api/auth?token=%s" % (base_url, tmp)
message = "A third-party application is requesting access to your account. If that was you, follow this link:\n%s\n" % url
if login_type == "jid":
msg = Message(re=login, message=message)
msg.put()
else:
run(["mail", "-s", "Your token", login], stdin_data=message)
commit()
return url
def profile(profile_id=None):
uri = request.url_root + "api/v1/"
if request.method == "POST":
name = request.form["name"]
number = request.form["number"]
company= request.form["company"]
title = request.form["title"]
address = request.form["address"]
ok, profile = database.post_profile(name=name, number= number,
company = company, title=title,
address=address)
if ok:
database.commit()
return resource.profile(uri, profile), 201
return "Resource already exists", 409
if request.method == "GET" and profile_id:
ok , profile = database.get_profile(profile_id)
if ok:
return resource.profile(uri, profile), 200
return "No Resource Found", 404
if request.method == "GET":
ok , profile = database.get_profile()
if ok:
return resource.profile(uri, profile), 200
return "No Resource Found", 404
if request.method == "DELETE" and profile_id:
ok = database.del_profile(profile_id)
if ok:
database.commit()
return "", 200
return "No Resource Found", 404
return "Resource Not Found", 404
def participants(participant_id=None):
uri = request.url_root + "api/v1/"
if request.method == "POST":
weekend_id = request.form["weekend_id"]
profile_id = request.form["profile_id"]
ok, participants = database.post_participants(weekend_id=weekend_id,
profile_id=profile_id)
if ok:
database.commit()
return resource.participant(uri, participants), 201
return "Resource already exists", 409
if request.args.get('weekend_id'):
weekend_id = request.args.get('weekend_id')
else:
weekend_id = None
if request.method == "GET" and weekend_id:
ok , participants = database.get_participants(weekend_id=weekend_id)
if ok:
return resource.participant(uri, participants), 200
return "Resource Not Found", 404
if request.method == "GET" and participant_id:
ok , participants = database.get_participants(participant_id=participant_id)
if ok:
return resource.participant(uri, participants), 200
return "Resource Not Found", 404
if request.method == "GET":
ok , participants = database.get_participants()
if ok:
database.commit()
return resource.participant(uri, participants), 200
return "Resource Not Found", 404
if request.method == "DELETE" and participant_id:
ok = database.del_participant(participant_id)
if ok:
database.commit()
return "", 200
return "No resource found", 404
return "Resource Not Found", 404
)''')
# Populate settings
good = False
settings = {'server':None,
'username': None,
'email': None,
'password': None}
while not good:
settings['server'] = "http://prodgame01.lordofultima.com/%s/"%raw_input("Server (e.g. 35 for world 23): ")
settings['username'] = raw_input("Bot's username: "******"LoU login email: ")
settings['password'] = raw_input("LoU password: "******"Is that correct? [Y/n]").strip().lower()
good = True
if ans and ans[0] == 'n':
good = False
for setting in settings:
cursor.execute('insert into settings (name, value) values (?, ?)', (setting.upper(), settings[setting]))
print "Database created."
database.commit()
cursor.close()
def process(text,sender,chatroom):
userary = ['void1', 'void2']
gods = ['mousepak','beobachten']
# bosstry lvl boss unit
if text.find('!boss') > -1 and chatroom == 'rivatein':
# check within alliance table
db = database.get_db()
d = db.execute('select player from alliance_names where player=?',(sender,))
p = None
for r in d:
p = r[0]
if p !=None:
bosstypes = {"d":0, "h":1, "m":2}
suggested_troops = {"berserker":[0,1,0,50,300,2000,4000,10000,15000,20000,30000,45000,60000], "ranger":[0,1,0,84,500,3334,6667,16667,25000,33334,50000,75000,100000], "guardian":[0,1,0,250,1500,10000,20000,50000,75000,100000,150000,225000,300000], "xbow":[1,0,0,63,375,2500,5000,12500,18750,25000,37500,56250,75000], "knight":[1,0,0,28,167,1112,2223,5556,8334,11112,16667,25000,33334], "mage":[0,0,1,36,215,1429,2858,7143,10715,14286,21429,32143,42858], "warlock":[0,0,1,21,125,834,1667,4167,6250,8334,12500,18750,25000], "templar":[0,1,0,100,600,4000,8000,20000,30000,40000,60000,90000,120000], "paladin":[1,0,0,42,250,1667,3334,8334,12500,16667,25000,37500,50000]}
research_bonus = [1, 0.99, 0.97, 0.94, 0.90, 0.85, 0.80, 0.75, 0.70, 0.65, 0.60, 0.55, 0.5]
try:
tokens = text.split()
cmd = tokens[0]
type = tokens[1][:1]
num = tokens[1][1:]
unit = tokens[2]
research_lvl = 0
if len(tokens) == 4:
research_lvl = int(tokens[3])
if research_lvl > 12:
research_lvl = 12
# print research_lvl
# print len(tokens)
troops = suggested_troops[unit][int(float(num))+2]
if suggested_troops[unit][bosstypes[type]] == 1:
troops = suggested_troops[unit][int(float(num))+2]*0.67
troops = troops * research_bonus[research_lvl]
response = 'suggested troop numbers to send : '+str(int(round(troops, 0)))
bot.whisper(response, sender)
except (IndexError, ValueError, TypeError, KeyError):
bot.whisper('try again: !boss {d|h|m}{1-10} type - type can be berserker, ranger, guardian, xbow, knight, mage, warlock, templar or paladin', sender)
bot.whisper('e.g. !boss d7 knight', sender)
return
# quit
if sender.lower() in gods and text.find('!die') >= 0:
bot.whisper('Cya!', sender)
sys.exit(0)
# restart all bots
if sender.lower() in gods and text.find('!restart') >= 0:
bot.whisper('Attempting restart!', sender)
os.system("pkill -f 'python run.py -r'")
# help
if text.find('!help') > -1 and len(text.split(' ',1)) == 1 and chatroom == 'rivatein':
# check within alliance table
db = database.get_db()
d = db.execute('select player from alliance_names where player=?',(sender,))
p = None
for r in d:
p = r[0]
bot.output('/whisper '+sender+' Available commands.. !fight, !shoot, !tickle and !parp.','C')
if p !=None:
bot.output('/whisper '+sender+' Most commands whisper to remove chat spam. Type `help name` to find out more about the function `name`. Available commands.. help, boss, check, city, claim, hunt.','C')
if sender in str(userary):
bot.output('/whisper '+sender+' *Privileged commands.. history','C')
if sender in str(gods):
bot.output('/whisper '+sender+' *Master commands.. alliance, die, msg, whisper','C')
return
if text.find('!help') > -1 and len(text.split(' ',1)) > 1 and chatroom == 'rivatein':
# check within alliance table
db = database.get_db()
d = db.execute('select player from alliance_names where player=?',(sender,))
p = None
for r in d:
p = r[0]
if p !=None:
try:
arg = text.split(' ',1)[1]
if arg == 'boss':
bot.output('/whisper '+sender+' info about boss command.','C')
elif arg == 'city':
bot.output('/whisper '+sender+' info about city command.','C')
else:
bot.output('/whisper '+sender+' Not found','C')
except ValueError:
bot.output('/whisper '+sender+' something went wrong','C')
except (Exception, e):
print 'Something bad just happened!\n', e
return
# record alliance members to database table
if sender.lower() in gods and text.find('!alliance') > -1 and chatroom == 'rivatein':
alliance_data = connect.getAllianceInfo(7)
db = database.get_db()
db.execute('delete from alliance_names')
db.execute('vacuum')
for index, item in enumerate(alliance_data):
rep = item['n']
db.execute('insert or replace into alliance_names (player) values (?)',(rep,))
database.commit()
bot.whisper('Done', sender)
# print(alliance_data)
return
# !history response
if text.find('!history') > -1 and chatroom == 'rivatein':
# check within alliance table
db = database.get_db()
d = db.execute('select player from alliance_names where player=?',(sender,))
p = None
for r in d:
p = r[0]
if p !=None:
histname = text.split(' ',1)[1]
lynxcmd = "lynx -source -auth=="+config.EXTUSER+":"+config.EXTPASS+" '"+config.EXTURL+"bot_lookup.php?world=39&player="+histname+"'"
histdata = os.popen(lynxcmd).read()
print(sender+' '+histdata)
histdata2 = histdata.split('#')
bot.output('/whisper '+sender+' '+histdata2[0],'C')
bot.output('/whisper '+sender+' '+histdata2[1],'C')
bot.output('/whisper '+sender+' '+histdata2[2],'C')
bot.output('/whisper '+sender+' '+histdata2[3],'C')
return
# !city info response
if text.find('!city') > -1 and chatroom == 'rivatein':
# check within alliance table
db = database.get_db()
d = db.execute('select player from alliance_names where player=?',(sender,))
p = None
for r in d:
p = r[0]
if p !=None:
print('room = '+chatroom)
cityxy = text.split(' ',1)[1]
cityxy = str(cityxy).replace("[coords]","")
cityxy = cityxy.replace("[/coords]","")
cityxy = cityxy.replace(" ","")
# print(cityxy)
cityx = cityxy.split(':')[0]
cityy = cityxy.split(':')[1]
lynxcmd = "lynx -source -auth="+config.EXTUSER+":"+config.EXTPASS+" '"+config.EXTURL+"bot_city.php?world=39&x="+cityx+"&y="+cityy+"'"
citydata = os.popen(lynxcmd).read()
# print(sender+' '+lynxcmd)
print(sender+' '+citydata)
citydata2 = citydata.split('#')
# print(len(citydata2))
# print(citydata2[0])
for botout in range(0, len(citydata2)-1):
bot.output('/whisper '+sender+' '+citydata2[botout],'C')
else:
bot.output('/whisper '+sender+' done','C')
return
# Make the bot speak
if sender.lower() in gods and text.find('!msg') > -1:
chandata = text.replace('!msg ','')
bot.output(chandata,'C')
return
# Make the bot whisper
if text.strip().find('!whisper') == 0 and sender.lower() in gods:
tokens = text.split()
cmd = tokens[0]
target = tokens[1]
message = ' '.join(tokens[2:])
bot.whisper(message, target)
return
# add claim
if text.find('!claim') > -1 and chatroom == 'rivatein':
# check within alliance table
db = database.get_db()
d = db.execute('select player from alliance_names where player=?',(sender,))
p = None
for r in d:
p = r[0]
if p !=None:
try:
arg = text.split(' ',1)[1]
xy = arg.split(':',1)
x = str(xy[0]).replace("[coords]","")
x = x.replace(" ","")
y = str(xy[1]).replace("[/coords]","")
if len(x) > 3 or len(y) > 3:
raise IndexError
if database.check_claim(x,y):
bot.whisper('Location already claimed by ' + database.check_claim(x,y) + '. :(', sender)
else:
database.add_claim(x,y,sender)
bot.whisper(x + ':' + y + ' successfully claimed!', sender)
except IndexError:
bot.whisper('Looks like you made a typo.', sender)
return
return
# check claim
if text.find('!check') > -1 and chatroom == 'rivatein':
# check within alliance table
db = database.get_db()
d = db.execute('select player from alliance_names where player=?',(sender,))
p = None
for r in d:
p = r[0]
if p !=None:
try:
arg = text.split(' ',1)[1]
xy = arg.split(':',1)
x = str(xy[0]).replace("[coords]","")
x = x.replace(" ","")
y = str(xy[1]).replace("[/coords]","")
if len(x) > 3 or len(y) > 3:
raise IndexError
if database.check_claim(x,y):
bot.whisper(x + ':' + y + ' is claimed by ' + database.check_claim(x,y) + '. :(', sender)
else:
bot.whisper(x + ':' + y + ' is not claimed yet!', sender)
except IndexError:
bot.whisper('Looks like you made a typo.', sender)
return
return
# Look for bosses, but only on whispers
if '!hunt' in text and 'rivate' in chatroom:
# check within alliance table
db = database.get_db()
d = db.execute('select player from alliance_names where player=?',(sender,))
p = None
for r in d:
p = r[0]
if p !=None:
cooldown = 60 * 5
x = None
y = None
cont = None
lvl_max = 10
lvl_min = 7
# Let's not give some people an unfair advantage...
if 'lekke' in sender.lower():
bot.whisper('Sorry Lekke, you are too good to use me...', sender)
return
# First check that player isn't spamming this function be enforcing a cool down period
now = int(time.time())
db = database.get_db()
resp = db.execute('select time from boss_cooldown where player = ?',(sender.lower(),))
last_req = resp.fetchone()
if last_req:
last_req = int(last_req[0])
elapsed = now - last_req
if elapsed < cooldown:
if sender.lower() in gods:
bot.whisper("Cheating... [elapsed is %d secs]"%elapsed, sender)
else:
def ptime(s): # pretty time
mins = s/60
secs = s%60
out = ""
if mins > 0:
out += '%d min, '%mins
out += '%d sec'%secs
return out
bot.whisper("Easy there killer :P Wait %s, then try again"%(ptime(cooldown - elapsed)))
return
try:
tokens = text.split()
cmd = tokens[0]
source = tokens[1]
source = str(source).replace("[coords]","")
source = source.replace("[/coords]","")
source = source.replace(" ","")
if len(tokens) > 2:
lvl_min = int(tokens[2])
if len(tokens) > 3:
lvl_max = int(tokens[3])
if ':' in source:
# source is a city
x,y = map(int, source.split(':'))
elif 'c' in source.lower():
# source is a continent
cont = int(source.lower().strip('c'))
x = ((cont % 10) * 100) + 50
y = ((cont / 10) * 100) + 50
else:
raise ValueError
except (IndexError, ValueError, TypeError):
bot.whisper('try again: !hunt city_coords OR cont [min level=7] [max level=10]>', sender)
bot.whisper('e.g. !hunt c22 8', sender)
return
db.execute('insert or replace into boss_cooldown (player, time) values (?, ?)',(sender,now))
database.commit()
if lvl_min < 1:
lvl_min = 1
if lvl_max > 10:
lvl_max = 10
if lvl_max < lvl_min:
lvl_min = lvl_max
# get all bosses on the continent
cells = world.pos2contcells(x,y)
world_data = connect.getWorldInfo(cells)
bosses = world.parse_bosses(world_data)
# limit to the lvl_min and continent we're interested in
bosses = [b for b in bosses if
b['alive'] and
b['level'] >= lvl_min and
b['level'] <= lvl_max and
world.get_cont(b['x'],b['y']) == world.get_cont(x,y)]
# sort by highest level first or distance depending on request
if cont:
bosses = sorted(bosses, key=lambda a: -a['level'])
else:
import math
bosses = sorted(bosses, key=lambda a: math.sqrt((x - a['x'])**2 + (y - a['y'])**2))
# restrict number returned
if len(bosses) > 10:
bosses = bosses[:10]
# create the response
if len(bosses) > 0:
response = ', '.join(["%s%d %03d:%03d"%(b['name'][0], b['level'], b['x'], b['y']) for b in bosses])
else:
response = 'None found, mate.'
bot.whisper(response, sender)
return
# Player commands
# if 'botx' in text.lower():
# bot.output(sender+' touched me in an inappropriate way','C')
# Random facts
if 'random' in text.lower() and 'botx' not in text.lower():
db = database.get_db()
resp = db.execute('select fact from random_facts order by random() limit 1')
responce = 'RANDOM..'+str(resp.fetchone())[3:-3]
bot.output(responce,chatroom)
def initdb(): create_schema() insert_testdata() database.commit()
def __del__(self):
logging.debug("Request finished, closing the transaction.")
database.commit()
"&IncludeLeasing=" + IncludeLeasing + "&Cartypes=" + \
Cartypes + "&HpFrom=" + HpFrom + "&HpTo=" + HpTo + "&page=" + page
conn.request("GET", query)
res = conn.getresponse()
content = res.read()
parsed_html = BeautifulSoup(content, from_encoding='utf8')
# in case of database error
except mdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit(1)
# close connection to database
if con:
database.commit(cur, con)
def main():
""" The method is the main method of the script.
The which will run by a command 'python bilbasen.py arg',
where arg is the number of pages to crawl. Do not provide any
argument if all pages should be crawled.
"""
if len(sys.argv) == 1:
download_data_to_database()
else:
download_data_to_database(sys.argv[1])
if __name__ == '__main__':
def POST(self):
database.commit()
return {"status": "ok"}
def cmd_queue_flush(*args):
"""delete everything from the queue"""
from ardj.database import Queue, commit
Queue.delete_all()
commit()
def create_edges_full_data_table():
print('create_edges_full_data_table')
connection = database.create_connection()
database.run_command('delete from edges_full_data', None, connection, True)
database.run_command('vacuum', None, connection, True)
sql_query = 'select a.id, a.shape_x_y, b.x from_x, b.y from_y, c.x to_x, c.y to_y \
from oulu_edges a \
inner join oulu_nodes b on a.`from` = b.id \
inner join oulu_nodes c on a.`to` = c.id'
count = 0
edges = database.query_all(sql_query, None, connection)
for edge in edges:
sequence = 1
if edge['shape_x_y'] is None:
distance = utility.measure_distance(edge['from_x'], edge['from_y'], edge['to_x'], edge['to_y'])
if distance <= config.MAX_DISTANCE:
sql_script = ' insert into "edges_full_data" ("edge_id", "sequence", "from_x", "from_y", "to_x", "to_y", "distance") \
values (?, ?, ?, ?, ?, ?, ?)'
parameter = (edge['id'], sequence, edge['from_x'], edge['from_y'], edge['to_x'], edge['to_y'], distance)
database.run_command(sql_script, parameter, connection, True)
sequence += 1
else:
splited_dots = split_line(edge['from_x'], edge['from_y'], edge['to_x'], edge['to_y'], config.MAX_DISTANCE)
for i in range(len(splited_dots)-1):
splited_distance = utility.measure_distance(splited_dots[i][0], splited_dots[i][1], splited_dots[i+1][0], splited_dots[i+1][1])
sql_script = ' insert into "edges_full_data" ("edge_id", "sequence", "from_x", "from_y", "to_x", "to_y", "distance") \
values (?, ?, ?, ?, ?, ?, ?)'
parameter = (edge['id'], sequence, splited_dots[i][0], splited_dots[i][1], splited_dots[i+1][0], splited_dots[i+1][1], splited_distance)
database.run_command(sql_script, parameter, connection, False)
sequence += 1
database.commit(connection)
else:
lines = edge['shape_x_y'].split(' ')
for i in range(len(lines)-1):
coord_from = lines[i].split(',')
coord_to = lines[i+1].split(',')
distance = sqrt(pow(float(coord_from[0]) - float(coord_to[0]), 2) + pow(float(coord_from[1]) - float(coord_to[1]), 2))
if distance <= config.MAX_DISTANCE:
sql_script = ' insert into "edges_full_data" ("edge_id", "sequence", "from_x", "from_y", "to_x", "to_y", "distance") \
values (?, ?, ?, ?, ?, ?, ?)'
parameter = (edge['id'], sequence, coord_from[0], coord_from[1], coord_to[0], coord_to[1], distance)
database.run_command(sql_script, parameter, connection, False)
sequence += 1
else:
splited_dots = split_line(coord_from[0], coord_from[1], coord_to[0], coord_to[1], config.MAX_DISTANCE)
for j in range(len(splited_dots)-1):
splited_distance = utility.measure_distance(splited_dots[j][0], splited_dots[j][1], splited_dots[j+1][0], splited_dots[j+1][1])
sql_script = ' insert into "edges_full_data" ("edge_id", "sequence", "from_x", "from_y", "to_x", "to_y", "distance") \
values (?, ?, ?, ?, ?, ?, ?)'
parameter = (edge['id'], sequence, splited_dots[j][0], splited_dots[j][1], splited_dots[j+1][0], splited_dots[j+1][1], splited_distance)
database.run_command(sql_script, parameter, connection, False)
sequence += 1
database.commit(connection)
count += 1
if count%1000 == 0:
print(count)
database.close_connection(connection, True)
print('finish create_edges_full_data_table')
