Python query_database 예제들

예제 #1

파일: observations.py 프로젝트: AndreyShprengel/LuminousMossBosses

for key in args.keys():
    arguments[key] = args[key].value

from dbhelper import query_database, result_to_json
import cgihelper

whereStatement = ''
orderStatement = ' ORDER BY Date DESC, Time DESC'
limitStatement = ''

# get observation by imageid
if 'imageid' in arguments:
    whereStatement = ' WHERE images.ImageID = ' + arguments['imageid']
# get observations by a select range
if 'rangeid' in arguments:
    limitStatement = ' limit ' + arguments['rangeid'];
    orderStatement = ' ORDER BY images.ImageID'
# get observation if it has been verified
if 'isplant' in arguments:
    tmp = "IS NULL" if arguments['isplant'].lower() == 'null' else '= ' + arguments['isplant']
    whereStatement = ' WHERE IsSilene ' + tmp
# sql look up
try:
    sqlStatement = text('SELECT ObsID, DATE_FORMAT(Date,"%Y-%m-%d") as Date, TIME_FORMAT(Time, "%T") as Time, Latitude, Longitude, images.ImageID, IsSilene, FileName FROM observations LEFT JOIN images ON observations.ImageID=images.ImageID'+ whereStatement + orderStatement + limitStatement +';')
    result = query_database(sqlStatement)
except:
    sys.exit("Error occured. Database may be down. Try again later and check your parameters.")
#be aware that this call will close the result object, and it will not be useable afterward.
items = result_to_json(result)
print items

예제 #2

파일: sponsor_verify.py 프로젝트: Aardevarken/LuminousMossBosses

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
from flask import Flask, url_for, request
from sqlalchemy import text

import cgi
arguments = dict()
args = cgi.FieldStorage()
for key in args.keys():
  arguments[key] = args[key].value

from dbhelper import query_database, result_to_json
import cgihelper


# Marks an observation as silene or not.
# sponsor_verify.py?issilene=true/false/0/1&obsid=43
try:
  query_database(text('UPDATE observations SET IsSilene=' + arguments["issilene"] + ' WHERE ObsID=' + arguments["obsid"] + ';'))
except:
  sys.exit("Error occured. Database may be down. Try again later and check your parameters.")

예제 #3

파일: import_observations.py 프로젝트: AndreyShprengel/LuminousMossBosses

#!/usr/bin/python
import sys, os
import subprocess
from sqlalchemy import select, text

from dbhelper import query_database

for path in sys.argv[1:]:
  abspath = os.path.abspath(path)
  location = os.path.dirname(abspath)
  filename = os.path.basename(abspath)

  # Add to images table.
  query = text("INSERT INTO images SET Location = \'" + location + "\', FileName = \'" + filename + "\';")
  query_database(query)

  # Get auto assigned image id
  query = text("SELECT ImageID FROM images WHERE Location = \'" + location + "\' AND FileName = \'" + filename + "\';")
  for row in query_database(query):
    imageid = row['ImageID']
  print imageid 

  # Add observation.
  query = text("INSERT INTO observations SET ImageID = \'" + str(imageid) + "\', Date = CURDATE(), Time = CURTIME(), Latitude = RAND(), Longitude = RAND();")
  query_database(query)

  # Add flower objects.
  p = subprocess.Popen('./findflowers ' + abspath + ' 2> /dev/null', shell=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
  lines = p.stdout.readlines()
  p.wait()

예제 #4

파일: import_observations.py 프로젝트: AndreyShprengel/LuminousMossBosses

#!/usr/bin/python
import sys, os
import subprocess
from sqlalchemy import select, text

from dbhelper import query_database

for path in sys.argv[1:]:
    abspath = os.path.abspath(path)
    location = os.path.dirname(abspath)
    filename = os.path.basename(abspath)

    # Add to images table.
    query = text("INSERT INTO images SET Location = '" + location + "', FileName = '" + filename + "';")
    query_database(query)

    # Get auto assigned image id
    query = text("SELECT ImageID FROM images WHERE Location = '" + location + "' AND FileName = '" + filename + "';")
    for row in query_database(query):
        imageid = row["ImageID"]
    print imageid

    # Add observation.
    query = text(
        "INSERT INTO observations SET ImageID = '"
        + str(imageid)
        + "', Date = CURDATE(), Time = CURTIME(), Latitude = RAND(), Longitude = RAND();"
    )
    query_database(query)

    # Add flower objects.

예제 #5

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
from flask import Flask, url_for, request
from sqlalchemy import text

import cgi
arguments = dict()
args = cgi.FieldStorage()
for key in args.keys():
    arguments[key] = args[key].value

from dbhelper import query_database, result_to_json
import cgihelper

# Marks an observation as silene or not.
# sponsor_verify.py?issilene=true/false/0/1&obsid=43
try:
    query_database(
        text('UPDATE observations SET IsSilene=' + arguments["issilene"] +
             ' WHERE ObsID=' + arguments["obsid"] + ';'))
except:
    sys.exit(
        "Error occured. Database may be down. Try again later and check your parameters."
    )

예제 #6

파일: flowerdetections.py 프로젝트: AndreyShprengel/LuminousMossBosses

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
from flask import Flask, url_for, request
from sqlalchemy import text

import cgi
arguments = dict()
args = cgi.FieldStorage()
for key in args.keys():
    arguments[key] = args[key].value

from dbhelper import query_database, result_to_json
import cgihelper

# returns all observations, most recent first
try:
    result = query_database(
        text('SELECT * from detection_objects WHERE ParentImageID=' +
             arguments["imageid"] + ';'))
except:
    sys.exit(
        "Error occured. Database may be down. Try again later and check your parameters."
    )
#be aware that this call will close the result object, and it will not be useable afterward.
items = result_to_json(result)
print items

예제 #7

파일: flowerdetections.py 프로젝트: Aardevarken/LuminousMossBosses

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
from flask import Flask, url_for, request
from sqlalchemy import text

import cgi
arguments = dict()
args = cgi.FieldStorage()
for key in args.keys():
  arguments[key] = args[key].value

from dbhelper import query_database, result_to_json
import cgihelper


# returns all observations, most recent first
try:
  result = query_database(text('SELECT * from detection_objects WHERE ParentImageID=' + arguments["imageid"] + ';'))
except:
  sys.exit("Error occured. Database may be down. Try again later and check your parameters.")
#be aware that this call will close the result object, and it will not be useable afterward.
items = result_to_json(result)
print items

예제 #8

파일: imagedata.py 프로젝트: AndreyShprengel/LuminousMossBosses

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
from flask import Flask, url_for, request
from sqlalchemy import text

import cgi
arguments = dict()
args = cgi.FieldStorage()
for key in args.keys():
  arguments[key] = args[key].value

from dbhelper import query_database, result_to_json
import cgihelper


# returns all observations, most recent first
try:
  result = query_database(text('SELECT * FROM images WHERE ImageID=' + arguments["imageid"] + ';'))
except:
  sys.exit("Error occured. Database may be down. Try again later and check your parameters.")
#be aware that this call will close the result object, and it will not be useable afterward.
items = result_to_json(result)
print items