def test_indexes(self):
"""
Tests creation/altering of indexes
"""
# Create the table
with connection.schema_editor() as editor:
editor.create_model(Author)
editor.create_model(Book)
# Ensure the table is there and has the right index
self.assertIn("title", connection.introspection.get_indexes(connection.cursor(), Book._meta.db_table))
# Alter to remove the index
new_field = CharField(max_length=100, db_index=False)
new_field.set_attributes_from_name("title")
with connection.schema_editor() as editor:
editor.alter_field(Book, Book._meta.get_field_by_name("title")[0], new_field, strict=True)
# Ensure the table is there and has no index
self.assertNotIn("title", connection.introspection.get_indexes(connection.cursor(), Book._meta.db_table))
# Alter to re-add the index
with connection.schema_editor() as editor:
editor.alter_field(Book, new_field, Book._meta.get_field_by_name("title")[0], strict=True)
# Ensure the table is there and has the index again
self.assertIn("title", connection.introspection.get_indexes(connection.cursor(), Book._meta.db_table))
# Add a unique column, verify that creates an implicit index
with connection.schema_editor() as editor:
editor.add_field(Book, BookWithSlug._meta.get_field_by_name("slug")[0])
self.assertIn("slug", connection.introspection.get_indexes(connection.cursor(), Book._meta.db_table))
# Remove the unique, check the index goes with it
new_field2 = CharField(max_length=20, unique=False)
new_field2.set_attributes_from_name("slug")
with connection.schema_editor() as editor:
editor.alter_field(BookWithSlug, BookWithSlug._meta.get_field_by_name("slug")[0], new_field2, strict=True)
self.assertNotIn("slug", connection.introspection.get_indexes(connection.cursor(), Book._meta.db_table))
def submit(request):
try:
uid=request.COOKIES['me_uid']
except KeyError:
return HttpResponseRedirect('http://www.me.uestc.edu.cn/stu/index.php/Login/')
stu=Information.objects.get(uid=uid)
if request.method == 'POST':
file_obj = request.FILES.get('file')
if file_obj:
try:
image = Image.open(file_obj)
except IOError:
return HttpResponse("<h1>上传失败，只能上传图片哦</h1>")
image = image.resize((120,144),Image.ANTIALIAS)
image_name = "/static/upload/%s.jpg " %uid
image.save(open('static/upload/%s.jpg'%uid,'w+'), format="JPEG")
cursor = connection.cursor()
cursor.execute("update portrait set portrait ='%s' where uid = %s "% (image_name,uid))
l=[]
values=[]
sql='update information set '
for key in request.POST:
if key=='file':
continue
if not request.POST.get(key)=='':
l.append(key+" = '%s'"%request.POST.get(key))
if l:
sql=sql+','.join(l)+" where uid = '%s'"%uid
cursor = connection.cursor()
cursor.execute(sql)
return HttpResponseRedirect('information/')
return render_to_response('submit.html',{'stu':stu,})
def test_adding_arrayfield_with_index(self):
"""
ArrayField shouldn't have varchar_patterns_ops or text_patterns_ops indexes.
"""
table_name = 'postgres_tests_chartextarrayindexmodel'
call_command('migrate', 'postgres_tests', verbosity=0)
with connection.cursor() as cursor:
like_constraint_columns_list = [
v['columns']
for k, v in list(connection.introspection.get_constraints(cursor, table_name).items())
if k.endswith('_like')
]
# Only the CharField should have a LIKE index.
self.assertEqual(like_constraint_columns_list, [['char2']])
# All fields should have regular indexes.
with connection.cursor() as cursor:
indexes = [
c['columns'][0]
for c in connection.introspection.get_constraints(cursor, table_name).values()
if c['index'] and len(c['columns']) == 1
]
self.assertIn('char', indexes)
self.assertIn('char2', indexes)
self.assertIn('text', indexes)
call_command('migrate', 'postgres_tests', 'zero', verbosity=0)
with connection.cursor() as cursor:
self.assertNotIn(table_name, connection.introspection.table_names(cursor))
def update_city_coords():
""" Update geolocation_uscity records with valid coordinates. Creates coords
if they do not exist.
"""
cursor = connection.cursor()
cursor_insert = connection.cursor()
cursor.execute("""
SELECT id, ST_AsText(ST_Centroid(geom)) "point"
FROM prep_city_fix
WHERE coordinate_id IS NULL and geom IS NOT NULL
""")
for city in cursor:
point = GEOSGeometry(city[1])
point.y = Decimal(str(point.y)).quantize(Decimal('.0000000001'))
point.x = Decimal(str(point.x)).quantize(Decimal('.0000000001'))
try:
coord = Coordinate.objects.get(
latitude=point.y, longitude=point.x)
except Coordinate.DoesNotExist:
coordinate = Coordinate()
coordinate.latitude = point.y
coordinate.longitude = point.x
coordinate.rad_lat = radians(coordinate.latitude)
coordinate.rad_lon = radians(coordinate.longitude)
coordinate.sin_rad_lat = sin(coordinate.rad_lat)
coordinate.cos_rad_lat = cos(coordinate.rad_lat)
coordinate.save()
coord = Coordinate.objects.get(
latitude=point.y, longitude=point.x)
cursor_insert.execute("""
UPDATE prep_city_fix
SET coordinate_id = %s
WHERE id = %s and coordinate_id is null;
""" % (coord.id, city[0]))
transaction.commit_unless_managed()
def test_default_connection_thread_local(self):
"""
The default connection (i.e. django.db.connection) is different for
each thread (#17258).
"""
# Map connections by id because connections with identical aliases
# have the same hash.
connections_dict = {}
connection.cursor()
connections_dict[id(connection)] = connection
def runner():
# Passing django.db.connection between threads doesn't work while
# connections[DEFAULT_DB_ALIAS] does.
from django.db import connections
connection = connections[DEFAULT_DB_ALIAS]
# Allow thread sharing so the connection can be closed by the
# main thread.
connection.allow_thread_sharing = True
connection.cursor()
connections_dict[id(connection)] = connection
for x in range(2):
t = threading.Thread(target=runner)
t.start()
t.join()
# Each created connection got different inner connection.
self.assertEqual(
len(set(conn.connection for conn in connections_dict.values())),
3)
# Finish by closing the connections opened by the other threads (the
# connection opened in the main thread will automatically be closed on
# teardown).
for conn in connections_dict.values():
if conn is not connection:
conn.close()
def gamesOwnedArray(steam_id):
print('did we get here? monkey 2')
returnArray=[]
tester = connection.cursor()
tester2 = connection.cursor()
tester.execute("SELECT appID FROM polls_Owns WHERE steamID=%s", [steam_id])
x=0
#print("@@@@@@@@@@@@@@@@@@@@@")
for row in tester:
returnArray.append([])
#print(row[0])
returnArray[x].append(row[0])
try:
tester2.execute("SELECT tags, score FROM polls_Game WHERE app_ID=%s", [row[0]])
for row2 in tester2:
if len(returnArray[x]) < 2:
#print(row2[0])
for elem in tagChecker(row2[0]):
returnArray[x].append(elem)
for elem in scoreSplitter(int(row2[1])):
returnArray[x].append(elem)
except:
print("missing game:")
returnArray[x].append(1)
#print(returnArray[x])
x=x+1
#print(returnArray)
#print("@@@@@@@@@@@@@@@@@@@@@")
return returnArray
def reset_sequence(model):
"""
Reset the ID sequence for a model.
"""
sql = connection.ops.sequence_reset_sql(no_style(), [model])
for cmd in sql:
connection.cursor().execute(cmd)
def test_no_interpolation_on_sqlite(self):
# Regression for #17158
# This shouldn't raise an exception
query = "SELECT strftime('%Y', 'now');"
connection.cursor().execute(query)
self.assertEqual(connection.queries[-1]['sql'],
str_prefix("QUERY = %(_)s\"SELECT strftime('%%Y', 'now');\" - PARAMS = ()"))
def allGamesArray():
returnArray=[]
tester = connection.cursor()
tester2 = connection.cursor()
tester.execute("SELECT app_ID FROM polls_Game WHERE 1=1")
x=0
#print("@@@@@@@@@@@@@@@@@@@@@")
for row in tester:
returnArray.append([])
#print(row[0])
returnArray[x].append(row[0])
try:
tester2.execute("SELECT tags, score, app_ID FROM polls_Game WHERE app_ID=%s", [row[0]])
for row2 in tester2:
if len(returnArray[x]) < 2:
#print(row2[0])
for elem in tagChecker(row2[0]):
returnArray[x].append(elem)
for elem in scoreSplitter(int(row2[1])):
returnArray[x].append(elem)
except:
print("missing game:")
#print(returnArray[x])
x=x+1
#print(returnArray)
#print('\n')
#print(len(returnArray))
#print("@@@@@@@@@@@@@@@@@@@@@")
return returnArray
def test_raw_insert(self):
with self.assertNumQueries(1):
self.assertListEqual(
list(Test.objects.values_list('name', flat=True)),
[])
with self.assertNumQueries(1):
cursor = connection.cursor()
cursor.execute(
"INSERT INTO cachalot_test (name, public) "
"VALUES ('test1', %s)", [1 if self.is_sqlite else 'true'])
cursor.close()
with self.assertNumQueries(1):
self.assertListEqual(
list(Test.objects.values_list('name', flat=True)),
['test1'])
with self.assertNumQueries(1):
cursor = connection.cursor()
cursor.execute(
"INSERT INTO cachalot_test (name, public) "
"VALUES ('test2', %s)", [1 if self.is_sqlite else 'true'])
cursor.close()
with self.assertNumQueries(1):
self.assertListEqual(
list(Test.objects.values_list('name', flat=True)),
['test1', 'test2'])
def regex_lookup(self, lookup_type):
# If regex_lookup is called before it's been initialized, then create
# a cursor to initialize it and recur.
from django.db import connection
connection.cursor()
return connection.ops.regex_lookup(lookup_type)
def test_include_partitions(self):
"""inspectdb --include-partitions creates models for partitions."""
with connection.cursor() as cursor:
cursor.execute('''\
CREATE TABLE inspectdb_partition_parent (name text not null)
PARTITION BY LIST (left(upper(name), 1))
''')
cursor.execute('''\
CREATE TABLE inspectdb_partition_child
PARTITION OF inspectdb_partition_parent
FOR VALUES IN ('A', 'B', 'C')
''')
out = StringIO()
partition_model_parent = 'class InspectdbPartitionParent(models.Model):'
partition_model_child = 'class InspectdbPartitionChild(models.Model):'
partition_managed = 'managed = False # Created from a partition.'
try:
call_command('inspectdb', table_name_filter=inspectdb_tables_only, stdout=out)
no_partitions_output = out.getvalue()
self.assertIn(partition_model_parent, no_partitions_output)
self.assertNotIn(partition_model_child, no_partitions_output)
self.assertNotIn(partition_managed, no_partitions_output)
call_command('inspectdb', table_name_filter=inspectdb_tables_only, include_partitions=True, stdout=out)
with_partitions_output = out.getvalue()
self.assertIn(partition_model_parent, with_partitions_output)
self.assertIn(partition_model_child, with_partitions_output)
self.assertIn(partition_managed, with_partitions_output)
finally:
with connection.cursor() as cursor:
cursor.execute('DROP TABLE IF EXISTS inspectdb_partition_child')
cursor.execute('DROP TABLE IF EXISTS inspectdb_partition_parent')
def test_foreign_data_wrapper(self):
with connection.cursor() as cursor:
cursor.execute('CREATE EXTENSION IF NOT EXISTS file_fdw')
cursor.execute('CREATE SERVER inspectdb_server FOREIGN DATA WRAPPER file_fdw')
cursor.execute('''\
CREATE FOREIGN TABLE inspectdb_iris_foreign_table (
petal_length real,
petal_width real,
sepal_length real,
sepal_width real
) SERVER inspectdb_server OPTIONS (
filename '/dev/null'
)
''')
out = StringIO()
foreign_table_model = 'class InspectdbIrisForeignTable(models.Model):'
foreign_table_managed = 'managed = False'
try:
call_command('inspectdb', stdout=out)
output = out.getvalue()
self.assertIn(foreign_table_model, output)
self.assertIn(foreign_table_managed, output)
finally:
with connection.cursor() as cursor:
cursor.execute('DROP FOREIGN TABLE IF EXISTS inspectdb_iris_foreign_table')
cursor.execute('DROP SERVER IF EXISTS inspectdb_server')
cursor.execute('DROP EXTENSION IF EXISTS file_fdw')
def initializeTables():
table_prefix = settings.OPENID_TABLE_PREFIX
connection.cursor()
tablenames = {
'associations_table': table_prefix + 'openid_associations',
'nonces_table': table_prefix + 'openid_nonces',
}
types = {
'postgresql': sqlstore.PostgreSQLStore,
'mysql': sqlstore.MySQLStore,
'sqlite3': sqlstore.SQLiteStore,
}
try:
s = types[settings.DB_STORE](connection.connection,
**tablenames)
except KeyError:
raise ImproperlyConfigured, \
"Database engine %s not supported by OpenID library" % \
(settings.DATABASE_ENGINE,)
try:
s.createTables()
except (SystemExit, KeyboardInterrupt, MemoryError), e:
raise
def consultarSatisfaccionPrograma(request, corrida):
#Variables que contienen los resultados
porcentajeUno = 0
porcentajedos = 0
results = []
#consulta los resultados de los estudiantes que inscribieron un curso
cursorUno = connection.cursor()
cursorUno.execute('select CAST(AVG(case when COALESCE(a.asignadas,0)= COALESCE(b.capacidad,0) THEN 100 ELSE 0 end) as integer) , count(*) estudiantes from (select count(*) asignadas, asig.estudiante_id estudiante from siscupos_asignaturasugerida asig where asig."preAsignacionCurso_id" = %s group by asig.estudiante_id ) a RIGHT OUTER JOIN (select count(*) capacidad, estudiante_id estudiante from siscupos_asignaturaxestudianteasig asig where estado = \'0\' and asig."preAsignacionCurso_id" = %s group by estudiante_id) b ON b.estudiante = a.estudiante where b.capacidad = 1', [corrida, corrida])
resultadoUno = cursorUno.fetchall()
for row in resultadoUno:
porcentajeUno = row[0]
#consulta los resultados de los estudiantes que inscribieron dos cursos
cursordos = connection.cursor()
cursordos.execute('select CAST(AVG(case when COALESCE(a.asignadas,0)= 2 THEN 100 WHEN COALESCE(a.asignadas,0)= 1 THEN 50 ELSE 0 end) as integer), count(*) estudiantes from (select count(*) asignadas, asig.estudiante_id estudiante from siscupos_asignaturasugerida asig where asig."preAsignacionCurso_id" = %s group by asig.estudiante_id ) a RIGHT OUTER JOIN (select count(*) capacidad, estudiante_id estudiante from siscupos_asignaturaxestudianteasig asig where estado = \'0\' and asig."preAsignacionCurso_id" = %s group by estudiante_id) b ON b.estudiante = a.estudiante where b.capacidad > 1', [corrida, corrida])
resultadodos = cursordos.fetchall()
for row in resultadodos:
porcentajedos = row[0]
#Carga los resultados en un objeto JSon
p1 = {'tipo': '1', 'porcentaje': porcentajeUno}
p2 = {'tipo': '2', 'porcentaje': porcentajedos}
results.append(p1)
results.append(p2)
return HttpResponse(json.dumps(results ), content_type='application/json; charset=UTF-8')
def debug_view(request):
"""This view is for ephemeral debugging of issues"""
context = {}
# Map of key -> val that will get displayed in a big table in the debug
# view in the order they were inserted
debug_info = OrderedDict()
# Add IP address related headers to figure out rate-limiting issues. #1475993
for meta_header in ['HTTP_X_FORWARDED_FOR', 'REMOTE_ADDR', 'HTTP_X_REAL_IP']:
debug_info['request.META["' + meta_header + '"]'] = request.META.get(meta_header, 'none')
# Add table counts for all non-pg tables
with connection.cursor() as cursor:
cursor.execute("""
SELECT c.relname
FROM pg_catalog.pg_class c
WHERE c.relkind = 'r'
""")
tables = cursor.fetchall()
for tablename in sorted(tables):
tablename = tablename[0]
if tablename.startswith(('pg_', 'sql_', 'lock')):
continue
with connection.cursor() as cursor:
cursor.execute('SELECT count(*) FROM %s' % tablename)
debug_info['%s count' % tablename] = cursor.fetchone()[0]
context['debug_info'] = debug_info
context['title'] = 'Debug information'
return render(request, 'admin/debug_view.html', context)
def test_prevent_rollback(self):
with transaction.atomic():
Reporter.objects.create(first_name="Tintin")
sid = transaction.savepoint()
# trigger a database error inside an inner atomic without savepoint
with self.assertRaises(DatabaseError):
with transaction.atomic(savepoint=False):
connection.cursor().execute(
"SELECT no_such_col FROM transactions_reporter"
)
# prevent atomic from rolling back since we're recovering manually
self.assertTrue(transaction.get_rollback())
transaction.set_rollback(False)
transaction.savepoint_rollback(sid)
self.assertQuerysetEqual(Reporter.objects.all(),
['<Reporter: Tintin>'])
self.assertAtomicSignalCalls(
# Enter atomic transaction block.
enter_block_atomic_signal_call_sequence(True) +
# Create Reporter.
create_model_atomic_signal_call_sequence() +
# Enter and leave atomic transaction block.
enter_block_atomic_signal_call_sequence(False, savepoint=False) +
leave_block_atomic_signal_call_sequence(False, False,
savepoint=False) +
# Leave atomic transaction with recovered rollback.
leave_block_atomic_signal_call_sequence(True, True)
)
def test_default_connection_thread_local(self):
"""
Ensure that the default connection (i.e. django.db.connection) is
different for each thread.
Refs #17258.
"""
connections_set = set()
connection.cursor()
connections_set.add(connection)
def runner():
# Passing django.db.connection between threads doesn't work while
# connections[DEFAULT_DB_ALIAS] does.
from django.db import connections
connection = connections[DEFAULT_DB_ALIAS]
# Allow thread sharing so the connection can be closed by the
# main thread.
connection.allow_thread_sharing = True
connection.cursor()
connections_set.add(connection)
for x in range(2):
t = threading.Thread(target=runner)
t.start()
t.join()
# Check that each created connection got different inner connection.
self.assertEqual(
len(set([conn.connection for conn in connections_set])),
3)
# Finish by closing the connections opened by the other threads (the
# connection opened in the main thread will automatically be closed on
# teardown).
for conn in connections_set:
if conn is not connection:
conn.close()
def insert_csv(filename):
"""
Creates a table which represents which papers each paper is
most similar to.
"""
reader = csv.reader(open(filename))
cursor = connection.cursor()
cursor.execute(
'DROP TABLE IF EXISTS TopFives;'
'CREATE TABLE TopFives(parentId INT, childId INT, rank INT);'
)
cursor.close()
i = 0
for line in reader:
print i
i+=1
cursor = connection.cursor()
cursor.execute(
'INSERT INTO TopFives (parentId, childId, rank) VALUES '
'('
'(SELECT PaperId FROM Papers WHERE DOI = %s),'
'(SELECT PaperId FROM Papers WHERE DOI = %s),'
'%s );',[line[0].encode('UTF-8'),line[1].encode('UTF-8'),line[2].encode('UTF-8')])
cursor.close()
def global_stats_gen():
table='ubuzima_globalstatistic'
try:
cursor = connection.cursor()
cursor.execute("drop table %s" % table)
management.call_command('syncdb')
cursor = connection.cursor()
cursor.execute('select * from ubuzima_report_fields')
for r in cursor.fetchall():
rpt=Report.objects.get(pk=r[1])
f=Field.objects.get(pk=r[2])
stat=GlobalStatistic( field = f, report = rpt, report_type = rpt.type , key = f.type.key ,unknown_loc=rpt.is_unknown_loc(), original =rpt.location, district = LocationShorthand.objects.get(original=rpt.location).district ,province = LocationShorthand.objects.get(original=rpt.location).province, created = rpt.created)
stat.save()
#print stat
l=LastRecord.objects.all().order_by("-id")[0]
l.report=rpt
l.report_field=r[0]
l.field=f
l.save()
cursor.close()
except Exception,e:
raise e
def handler(self, request, q_der):
try:
from django.db import connection
connection.cursor() # Reconnect to mysqld if necessary
self.start_new_transaction()
serverCA = rpki.irdb.models.ServerCA.objects.get()
rpkid = serverCA.ee_certificates.get(purpose = "rpkid")
irdbd = serverCA.ee_certificates.get(purpose = "irdbd")
q_cms = rpki.left_right.cms_msg(DER = q_der)
q_msg = q_cms.unwrap((serverCA.certificate, rpkid.certificate))
self.cms_timestamp = q_cms.check_replay(self.cms_timestamp, request.path)
if self.debug:
logger.debug("Received: %s", ElementToString(q_msg))
if q_msg.get("type") != "query":
raise rpki.exceptions.BadQuery("Message type is {}, expected query".format(
q_msg.get("type")))
r_msg = Element(rpki.left_right.tag_msg, nsmap = rpki.left_right.nsmap,
type = "reply", version = rpki.left_right.version)
try:
for q_pdu in q_msg:
getattr(self, "handle_" + q_pdu.tag[len(rpki.left_right.xmlns):])(q_pdu, r_msg)
except Exception, e:
logger.exception("Exception processing PDU %r", q_pdu)
r_pdu = SubElement(r_msg, rpki.left_right.tag_report_error,
error_code = e.__class__.__name__)
r_pdu.text = str(e)
if q_pdu.get("tag") is not None:
r_pdu.set("tag", q_pdu.get("tag"))
if self.debug:
logger.debug("Sending: %s", ElementToString(r_msg))
request.send_cms_response(rpki.left_right.cms_msg().wrap(
r_msg, irdbd.private_key, irdbd.certificate))
def test_primary_key(self):
"""
Tests altering of the primary key
"""
# Create the table
with connection.schema_editor() as editor:
editor.create_model(Tag)
# Ensure the table is there and has the right PK
self.assertTrue(
connection.introspection.get_indexes(connection.cursor(), Tag._meta.db_table)['id']['primary_key'],
)
# Alter to change the PK
new_field = SlugField(primary_key=True)
new_field.set_attributes_from_name("slug")
with connection.schema_editor() as editor:
editor.remove_field(Tag, Tag._meta.get_field_by_name("id")[0])
editor.alter_field(
Tag,
Tag._meta.get_field_by_name("slug")[0],
new_field,
)
# Ensure the PK changed
self.assertNotIn(
'id',
connection.introspection.get_indexes(connection.cursor(), Tag._meta.db_table),
)
self.assertTrue(
connection.introspection.get_indexes(connection.cursor(), Tag._meta.db_table)['slug']['primary_key'],
)
def __init__(self, table_name, columns, insert_rows, delete_ids):
self.table_name = table_name
sql_up = []
from django.db import connection # so we can use escape_string
connection.cursor() # Opens connection if not already open
def escape(v):
if v is None:
return 'null'
v = unicode(v) # In case v is an integer or long
# escape_string wants a bytestring
#escaped = connection.connection.escape_string(v.encode('utf8'))
escaped = v.replace("'", "''").encode('utf8')
# We get bugs if we use bytestrings elsewhere, so convert back to unicode
# http://sourceforge.net/forum/forum.php?thread_id=1609278&forum_id=70461
return u"'%s'" % escaped.decode('utf8')
for row in insert_rows:
values = ', '.join(map(escape, row))
sql_up.append(
self.insert_row_sql % (
table_name, ', '.join(map(str, columns)), values
)
)
if delete_ids:
sql_down = [self.delete_rows_sql % (table_name, ', '.join(map(str, delete_ids)))]
else:
sql_down = ["SELECT 1"]
super(InsertRows, self).__init__(
sql_up = sql_up,
sql_down = sql_down,
)
def suppression(request,ip):
cursor=connection.cursor()
#Requete Info Host
cursor.execute('''SELECT hotes.ip,mac,hostname,os,localisation,type_machine,commentaires,application.nom AS appli_nom FROM hotes
LEFT JOIN application_hote ON application_hote.ip=hotes.ip
LEFT JOIN application ON application.id=application_hote.id_application
WHERE hotes.ip = %s LIMIT 1''', [ip])
infoHost=dictfetchall(cursor)
cursor.close()
if request.method == 'POST':
try:
confirmation=request.POST['confirmation']
except (KeyError):
return render(request,'serveurs/suppression.html',{'erreur':'Cochez pour supprimer','machine':infoHost})
cursor=connection.cursor()
cursor.execute('DELETE FROM hotes WHERE ip = %s', [ip])
cursor.close()
return redirect ('serveurs:liste')
else:
return render(request,'serveurs/suppression.html',{'machine':infoHost})
def DetailProdByPidPost2(request):
proID = None;
if request.method == "POST":
proID = request.POST.get('pid',None)
else:
proID = request.GET.get('pid',None)
if proID == None:
return HttpResponse("Invalid request, pid is missing")
prod_query = "select p.product_id, p.name, p.price, p.description, p.product_url, p.brand, p.merchant, p.color, c.cid, c.name, i.pic_url, i.img_id from (products as p left join categories as c on p.cid=c.cid) left join images as i on p.product_id=i.product_id where p.product_id = %s order by i.img_type asc" % (str(proID))
cursor1 = connection.cursor()
cursor1.execute(prod_query)
prod_result = cursor1.fetchall()
if len(prod_result) >= 1:
catID = prod_result[0][8]
prod_results = list(Products.objects.filter(cid=catID))
rowsNum = len(prod_results)
offset = 0
if rowsNum > 4:
offset = int(math.floor(random.random()*(rowsNum-4)))
recommended_query = "select p.product_id, p.name, p.price, p.cid, i.pic_url, i.img_id from products as p left join images as i on p.product_id=i.product_id where p.product_id!= %s and p.cid = %s and i.img_type='P' limit %s, 4"
cursor2 = connection.cursor()
query_string = 'select p.product_id, p.name, p.price, p.cid, i.pic_url, i.img_id from products as p left join images as i on p.product_id=i.product_id where p.product_id!= %s and p.cid = %s and i.img_type="P" limit %s, 4' % (str(proID), str(catID), str(offset))
cursor2.execute(query_string)
recommend_result = cursor2.fetchall()
recommend_result_formatted_dict = formatRecommendJson(recommend_result)
prod_json_result = formatProDetailJson2(prod_result, recommend_result_formatted_dict)
return HttpResponse(prod_json_result)
def companys(request):
#sql="SELECT DISTINCT c.name company,COUNT(bus_id) amount_bus,phone_no FROM brs_cmu_bus b JOIN brs_cmu_bus_company c ON c.name = b.company_name_id JOIN brs_cmu_phoneno p ON p.company_name_id = c.name GROUP BY c.name,phone_no ORDER BY c.name ASC"
sql="select name from brs_cmu_bus_company"
cursor = connection.cursor()
cursor.execute(sql)
companys_name = namedtuplefetchall(cursor)
companys_name_check = len(companys_name)
cursor.close()
companys_phones=None
companys_phones_check=0
company=None
if request.POST:
company = request.POST.get('company')
sql="select phone_no from brs_cmu_phoneno where company_name_id='%s'"%(company)
cursor = connection.cursor()
cursor.execute(sql)
companys_phones = namedtuplefetchall(cursor)
companys_phones_check = len(companys_phones)
cursor.close()
context = {
"company":company,
"companys_name": companys_name,
"companys_name_check":companys_name_check,
"companys_phones": companys_phones,
"companys_phones_check":companys_phones_check,
}
return render(request, "companys.html", context)
def test_default_connection_thread_local(self):
"""
Ensure that the default connection (i.e. django.db.connection) is
different for each thread.
Refs #17258.
"""
connections_set = set()
connection.cursor()
connections_set.add(connection.connection)
def runner():
from django.db import connection
connection.cursor()
connections_set.add(connection.connection)
for x in xrange(2):
t = threading.Thread(target=runner)
t.start()
t.join()
self.assertEquals(len(connections_set), 3)
# Finish by closing the connections opened by the other threads (the
# connection opened in the main thread will automatically be closed on
# teardown).
for conn in connections_set:
if conn != connection.connection:
conn.close()
def log(self):
from django.db import connection,transaction
cursor = connection.cursor()
while True:
if stop_event.isSet():
log(u"EXITING: playlist logger thread", "main")
return
rid, on_air = self.request_handler.on_air()
if not(rid is None) and rid != self.current_rid and\
not(on_air["planning_element"].type == "jingle"):
audiofile = on_air["audiofile"]
audiosource = on_air["audiosource"]
self.current_rid = rid
log("LOGGING a file in the playlist", "playlist")
cursor.execute("""
INSERT INTO playlist_playlistelement
(audiofile_id, on_air, audiosource_id)
VALUES (%s, %s , %s)
""", [audiofile.id, datetime.now(), audiosource.id]
)
cursor = connection.cursor()
transaction.commit_unless_managed()
log("LOGGING done", "playlist")
sleep(0.5)
def do_mysql_backup(self, outfile):
args = []
if self.user:
args += ["--user='******'" % self.user]
if self.passwd:
args += ["--password='******'" % self.passwd]
if self.host:
args += ["--host='%s'" % self.host]
if self.port:
args += ["--port=%s" % self.port]
args += [self.db]
base_args = copy(args)
blacklist_tables = self.get_blacklist_tables()
if blacklist_tables:
all_tables = connection.introspection.get_table_list(connection.cursor())
tables = list(set(all_tables) - set(blacklist_tables))
args += tables
os.system('%s %s > %s' % (getattr(settings, 'BACKUP_SQLDUMP_PATH', 'mysqldump'), ' '.join(args), outfile))
#append table structures of blacklist_tables
if blacklist_tables:
all_tables = connection.introspection.get_table_list(connection.cursor())
blacklist_tables = list(set(all_tables) and set(blacklist_tables))
args = base_args + ['-d'] + blacklist_tables
cmd = '%s %s >> %s' % (getattr(settings, 'BACKUP_SQLDUMP_PATH', 'mysqldump'), ' '.join(args), outfile)
os.system(cmd)
def test_db_import(self):
"""Actual test method, ran by the test suite."""
call_command('flush', interactive=False)
for fixture in getattr(self, 'dbdiff_fixtures', []):
call_command('loaddata', fixture)
for model in self.dbdiff_models:
if connection.vendor == 'postgresql':
reset = """
SELECT
setval(
pg_get_serial_sequence('%s', 'id'),
coalesce(max(id),0) + 1,
false
)
FROM %s
""" % (model._meta.db_table, model._meta.db_table)
else:
raise NotImplemented()
connection.cursor().execute(reset)
self.dbdiff_test()
Fixture(
self.dbdiff_expected,
models=self.dbdiff_models,
).assertNoDiff(
exclude=self.dbdiff_exclude,
)
def add_push(self):
cursor = connection.cursor()
cursor.execute(
"update book_bookrank "
"set all_push=all_push+1,mon_push=mon_push+1,wek_push=wek_push+1 "
"where id=%s;", [self.pk])
def test_PG_EXTRA_SEARCH_PATHS(self):
del apps.all_models['django_tenants']
c = connection.cursor()
c.execute('DROP SCHEMA {0} CASCADE; CREATE SCHEMA {0};'.format(
get_public_schema_name()))
apps.set_installed_apps(['customers', 'django_tenants'])
def _execute(sql, params=None):
cur = conn.cursor()
cur.execute(sql, params)
return cur
def get_data(sql_str):
with connection.cursor() as cursor:
cursor.execute(str(sql_str))
row = cursor.fetchall()
return row
def cdvorwrepsubw(request, id):
if request.session.has_key('uid'):
a_id = models.Engineer.objects.all()
a_id = a_id.values('a_id').filter(emp_id=id)[0]['a_id']
currtime = datetime.now().strftime("%H:%M:%S")
emp_id = models.Engineer.objects.all()
emp_id = emp_id.values('emp_id').filter(emp_id=id)[0]['emp_id']
currdate = date.today()
cursor = connection.cursor()
ps_5v = ''
ps_12v = ''
ps_28v = ''
outside_temp = ''
sideband_frequency = ''
ps_5v = float(request.POST['PS 5V'])
ps_12v = float(request.POST['PS 12V'])
ps_28v = float(request.POST['PS 28V'])
outside_temp = float(request.POST['Outside temp'])
sideband_frequency = float(request.POST['Sideband frequency'])
sql = "INSERT INTO cdvorweekly (date,time,a_id,f_id,emp_id,status,ps_5v, ps_12v, ps_28v, outside_temp, sideband_frequency) VALUES (%s,%s,%s,%s,%s,%s, %s,%s, %s, %s, %s)"
val = (currdate, currtime, a_id, '1', id, "", ps_5v, ps_12v, ps_28v,
outside_temp, sideband_frequency)
cursor.execute(sql, val)
p_id = models.Cdvorweekly.objects.all()
p_id = p_id.values('p_id')
p_id = p_id.order_by('-p_id')
p_id = p_id.values('p_id').filter(a_id=1)[0]['p_id']
print(p_id)
f = 2
if ps_5v < 4.75 or ps_5v > 5.25:
f = 3
remarks = "PS 5V not in range"
val = (id, p_id, remarks, ps_5v, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
if ps_12v < 11.5 or ps_12v > 12.5:
f = 3
remarks = "PS 12V not in range"
val = (id, p_id, remarks, ps_12v, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
if ps_28v < 27 or ps_28v > 29:
f = 3
remarks = "PS 28V not in range"
val = (id, p_id, remarks, ps_28v, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
if outside_temp < -25 or outside_temp > 70:
f = 3
remarks = "Outside temperature not in range"
val = (id, p_id, remarks, outside_temp, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
if sideband_frequency != 10001:
f = 3
remarks = "sideband frequency not equal to 10001 Hz"
val = (id, p_id, remarks, sideband_frequency, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
if f == 2:
status = "COMPLETED"
remarks = "Parameters normal at the first submit!"
value = "All parameters NORMAL"
val = (id, p_id, remarks, value, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
cursor.execute(
"update cdvorweekly set unit_incharge_approval = %s where p_id = %s",
[None, p_id])
cursor.execute(
"update dgmreports set r_count = r_count + 1 where r_id = %s",
['19'])
else:
status = "PENDING"
cursor.execute(
"update dgmreports set r_count = r_count + 1 where r_id = %s",
['18'])
print(status)
cursor.execute("update cdvorweekly set status = %s where p_id = %s",
[status, p_id])
cdvor_w = models.Cdvorweekly.objects.all()
cdvor_w = cdvor_w.values('p_id', 'date', 'time', 'status', 'ps_5v',
'ps_12v', 'ps_28v', 'outside_temp',
'sideband_frequency', 'remarks')
cdvorw = cdvor_w.filter(emp_id=id).order_by('-p_id')
cdvor_w = cdvor_w.filter(date=currdate)
supdetails = models.Supervisor.objects.all()
supdetails = supdetails.values('name', 'contact',
'email').filter(dept='N')
cdvorwlogs = models.Cdvorwlogs.objects.all()
cdvorwlogs = cdvorwlogs.filter(date=date.today()).order_by('-log_id')
return render(
request, 'engineer/cdvor/cdvorweeklyrep.html', {
'cdvorwlogs': cdvorwlogs,
'cdvor_w': cdvor_w,
'id': id,
'cdvorw': cdvorw,
'supdetails': supdetails
})
else:
return render(request, 'login/login.html')
def upcdvorweekly(request, id):
p_id = models.Cdvorweekly.objects.all()
p_id = p_id.values('p_id')
p_id = p_id.order_by('-p_id')
p_id = p_id.values('p_id').filter(a_id=1)[0]['p_id']
currdate = date.today()
currtime = datetime.now().strftime("%H:%M:%S")
emp_id = models.Cdvorweekly.objects.all()
emp_id = emp_id.values('emp_id').filter(p_id=id)[0]['emp_id']
cursor = connection.cursor()
remarks = request.POST['remarks']
ps_5v = float(request.POST['PS 5V'])
ps_12v = float(request.POST['PS 12V'])
ps_28v = float(request.POST['PS 28V'])
outside_temp = float(request.POST['Outside temp'])
sideband_frequency = float(request.POST['Sideband frequency'])
f = 2
# Failure cases
if ps_5v < 4.75 or ps_5v > 5.25:
f = 3
cursor.execute("update cdvorweekly set PS_5V = %s where p_id = %s",
[ps_5v, id])
remarks1 = "PS 5V reading not correct (update)"
val = (emp_id, p_id, remarks1, ps_5v, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
else:
cursor.execute("update cdvorweekly set PS_5V = %s where p_id = %s",
[ps_5v, id])
if ps_12v < 11.5 or ps_12v > 12.5:
f = 3
cursor.execute("update cdvorweekly set PS_12V = %s where p_id = %s",
[ps_12v, id])
remarks1 = "PS 12V not in correct range(update)"
val = (emp_id, p_id, remarks1, ps_12v, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
else:
cursor.execute("update cdvorweekly set PS_12V = %s where p_id = %s",
[ps_12v, id])
if ps_28v < 27 or ps_28v > 29:
f = 3
cursor.execute("update cdvorweekly set PS_28V = %s where p_id = %s",
[ps_28v, id])
remarks1 = "PS 28V value not in normal range(update)"
val = (emp_id, p_id, remarks1, ps_28v, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
else:
cursor.execute("update cdvorweekly set PS_28V = %s where p_id = %s",
[ps_28v, id])
if outside_temp < -25 or outside_temp > 70:
f = 3
cursor.execute(
"update cdvorweekly set outside_temp = %s where p_id = %s",
[outside_temp, id])
remarks1 = "Temperature not in range -25 to 70(update)"
val = (emp_id, p_id, remarks1, outside_temp, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
else:
cursor.execute(
"update cdvorweekly set outside_temp = %s where p_id = %s",
[outside_temp, id])
if sideband_frequency != 10001:
f = 3
cursor.execute(
"update cdvorweekly set sideband_frequency = %s where p_id = %s",
[sideband_frequency, id])
remarks1 = "Sideband frequency not equal to 10001(update)"
val = (emp_id, p_id, remarks1, sideband_frequency, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
else:
cursor.execute(
"update cdvorweekly set sideband_frequency = %s where p_id = %s",
[sideband_frequency, id])
if ((ps_5v >= 4.75 and ps_5v <= 5.25)
and (ps_12v >= 11.5 and ps_12v <= 12.5)
and (ps_28v >= 27 and ps_28v <= 29)
and (outside_temp >= -25 and outside_temp <= 70)
and (sideband_frequency == 10001)):
cursor.execute("update cdvorweekly set PS_5V = %s where p_id = %s",
[ps_5v, id])
cursor.execute("update cdvorweekly set PS_12V = %s where p_id = %s",
[ps_12v, id])
cursor.execute("update cdvorweekly set PS_28V = %s where p_id = %s",
[ps_28v, id])
cursor.execute(
"update cdvorweekly set outside_temp = %s where p_id = %s",
[outside_temp, id])
cursor.execute(
"update cdvorweekly set sideband_frequency = %s where p_id = %s",
[sideband_frequency, id])
cursor.execute("update cdvorweekly set status = %s where p_id = %s",
["COMPLETED", id])
val = (emp_id, p_id, "All parameters NORMAL", remarks, currdate,
currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
cursor.execute(
"update cdvorweekly set unit_incharge_approval = %s where p_id = %s",
[None, id])
cursor.execute(
"update dgmreports set r_count = r_count + 1 where r_id = %s",
['19'])
cursor.execute(
"update dgmreports set r_count = r_count - 1 where r_id = %s",
['18'])
else:
val = (emp_id, p_id, "Procedure Followed", remarks, currdate, currtime)
sql = "INSERT INTO cdvorwlogs (emp_id,p_id,remarks,value,date,time) values (%s ,%s,%s,%s , %s,%s)"
cursor.execute(sql, val)
cdvor_w = models.Cdvorweekly.objects.all()
cdvor_w = cdvor_w.values('p_id', 'date', 'time', 'status', 'ps_5v',
'ps_12v', 'ps_28v', 'outside_temp',
'sideband_frequency', 'remarks')
cdvorw = cdvor_w.filter(emp_id=emp_id).order_by('-p_id')
cdvor_w = cdvor_w.filter(date=currdate)
supdetails = models.Supervisor.objects.all()
supdetails = supdetails.values('name', 'contact', 'email').filter(dept='N')
cdvorwlogs = models.Cdvorwlogs.objects.all()
cdvorwlogs = cdvorwlogs.filter(date=date.today()).order_by('-log_id')
return render(
request, 'engineer/cdvor/cdvorweeklyrep.html', {
'cdvorwlogs': cdvorwlogs,
'cdvor_w': cdvor_w,
'id': emp_id,
'cdvorw': cdvorw,
'supdetails': supdetails
})
def sub_fav(self):
cursor = connection.cursor()
cursor.execute(
"update book_bookrank "
"set all_fav=all_fav-1 "
"where id=%s;", [self.pk])
def pall(request): # popular games showing all games
with connection.cursor() as cursor:
cursor.execute('select T.link,U.name_id,U.votes from games_logo as T,games_popular as U where T.name_id=U.name_id order by U.votes desc')
l1=cursor.fetchall()
return render(request,'games/populargames.html',context={'logos':l1,'c':"all"})
from django.db import models
from django.core.exceptions import ObjectDoesNotExist
from django.db import connection, transaction
# Create your models here.
cursor = connection.cursor()
'''
select
'''
class hikarianManager(models.Manager):
def get_or_none(self, **kwargs):
'''
定义 get_or_none
该方法 当get对象不存在时返回none
:param kwargs:
:return:
'''
try:
return self.get(**kwargs)
except ObjectDoesNotExist:
return None
class userInfo(models.Model):
user_id = models.CharField(max_length=20, unique=True, primary_key=True)
passwd = models.CharField(max_length=40)
user_name = models.CharField(max_length=15)
def delete_keywords(modeladmin, request, queryset):
ids = ",".join(map(str, queryset.values_list("id", flat=True)))
cursor = connection.cursor()
cursor.execute("DELETE FROM generic_assignedkeyword "
"WHERE keyword_id IN (%s);" % ids)
cursor.execute("DELETE FROM generic_keyword WHERE id IN (%s);" % ids)
def run_route(start_node_id, end_node_id, route_type):
"""
:param start_node_id:
:param end_node_id:
:param route_type:
:param route_options: a dictionary
:return:
"""
# TODO add route options dictionary
# TODO add parameter to function route_options=None
# sample dictionary of options
# route_options = {'route_types': {
# 'standard_route': 1,
# 'barrierfree route': 2,
# 'indoor_only_prefered': 3,
# 'fastest': 4
# },
# 'route_logic': {
# 'force_route_through_location': True
# }
# }
cur = connection.cursor()
base_route_q = """SELECT id, source, target,
total_cost:: DOUBLE PRECISION AS cost,
floor, network_type
FROM geodata.networklines_3857"""
# set default query
barrierfree_q = "WHERE 1=1"
if route_type == "1":
# exclude all networklines of type stairs
barrierfree_q = "WHERE network_type not in (1,3)"
routing_query = '''
SELECT seq,
id1 AS node,
id2 AS edge,
ST_Length(geom) AS cost,
floor,
network_type,
ST_AsGeoJSON(geom) AS geoj
FROM pgr_dijkstra('
{normal} {type}', %s, %s, FALSE, FALSE
) AS dij_route
JOIN geodata.networklines_3857 AS input_network
ON dij_route.id2 = input_network.id ;
'''.format(normal=base_route_q, type=barrierfree_q)
# run our shortest path query
if start_node_id or end_node_id:
if start_node_id != end_node_id:
cur.execute(routing_query, (start_node_id, end_node_id))
else:
logger.error("start or end node is None or is the same node " +
str(start_node_id))
return HttpResponseNotFound('<h1>Sorry NO start or end node'
' found within 200m</h1>')
# get entire query results to work with
route_segments = cur.fetchall()
route_info = calc_distance_walktime(route_segments)
# empty list to hold each segment for our GeoJSON output
route_result = []
# loop over each segment in the result route segments
# create the list of our new GeoJSON
for segment in route_segments:
seg_length = segment[3] # length of segment
layer_level = segment[4] # floor number
seg_type = segment[5]
seg_node_id = segment[1]
seq_sequence = segment[0]
geojs = segment[6] # geojson coordinates
geojs_geom = loads(geojs) # load string to geom
geojs_feat = Feature(geometry=geojs_geom,
properties={
'floor': layer_level,
'length': seg_length,
'network_type': seg_type,
'seg_node_id': seg_node_id,
'sequence': seq_sequence
})
route_result.append(geojs_feat)
# using the geojson module to create our GeoJSON Feature Collection
geojs_fc = FeatureCollection(route_result)
geojs_fc.update(route_info)
return geojs_fc
def lall(request): # latest games showing all games
with connection.cursor() as cursor:
cursor.execute('select T.link,U.name from games_logo as T,games_launch as U where T.name_id=U.name order by U.year desc;')
l1=cursor.fetchall()
return render(request,'games/latestgames.html',context={'logos':l1,'c':'all'})
def test_parameter_escaping(self):
with connection.cursor() as cursor:
cursor.execute("SELECT '%%', %s" + self.bare_select_suffix, ('%d',))
self.assertEqual(cursor.fetchall()[0], ('%', '%d'))
def create_route_from_search(request, start_term, end_term, route_type=0):
"""
Generate a GeoJSON route from room number
example:
http://localhost:8000/api/v1/directions/startstr=SomeSearchTerm&endstr=SomeSearchTerm&type=0
to room number
:param request: GET or POST request
:param building_id: buiilding id as integer
:param start_term: a text string as search term
:param end_term: a text string as search term
:param route_type: an integer room type
:return: a GeoJSON linestring of the route
"""
if request.method == 'GET' or request.method == 'POST':
start_room = start_term.split("=")[1]
end_room = end_term.split("=")[1]
cur = connection.cursor()
# logger.debug('*************************start term' + str(start_room))
# logger.debug('*************************end term' + str(end_room))
start_query = """SELECT id, external_id, search_string FROM geodata.search_index_v
WHERE search_string LIKE '%{0}%'
ORDER BY length(search_string) LIMIT 1""".format(
start_room)
# logger.debug('**************print query' + str(start_query))
cur.execute(start_query)
get_start_id_list = cur.fetchone()
start_id_value = get_start_id_list[0]
end_query = """SELECT id, external_id, search_string FROM geodata.search_index_v
WHERE search_string LIKE '%{0}%'
ORDER BY length(search_string) LIMIT 1""".format(
end_room)
# logger.debug('**************print END query' + str(end_query))
cur.execute(end_query)
get_end_id_list = cur.fetchone()
end_id_value = get_end_id_list[0]
# logger.debug('*************************current start id' + str(start_id_value))
# logger.debug('*************************current end id' + str(end_id_value))
start_node_id = get_room_centroid_node(start_id_value)
end_node_id = get_room_centroid_node(end_id_value)
res = run_route(start_node_id, end_node_id, route_type)
try:
return Response(res)
except:
logger.error("error exporting to json model: " + str(res))
logger.error(traceback.format_exc())
return Response(
{'error': 'either no JSON or no key params in your JSON'})
else:
return HttpResponseNotFound('<h1>Sorry not a GET or POST request</h1>')
def execute_custom_sql(query, params):
cursor = connection.cursor()
cursor.execute(query, params)
return cursor.fetchall()
def test_duplicate_table_error(self):
""" Creating an existing table returns a DatabaseError """
query = 'CREATE TABLE %s (id INTEGER);' % Article._meta.db_table
with connection.cursor() as cursor:
with self.assertRaises(DatabaseError):
cursor.execute(query)
from fuzzywuzzy import process
import os, django
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "RFPGurus.settings")
django.setup()
from django.db import connection
from rfp.models import SubCategory
from rfp.models import GovernmentBidsProfile
cursor1 = connection.cursor()
# cursor2=connection.cursor()
cursor1.execute(
'SELECT distinct(category) FROM public.rfp_governmentbidsprofile')
# cursor2.execute('SELECT * FROM public.category_subcategory')
data1 = cursor1.fetchall()
# data2=cursor2.fetchall()
sub = SubCategory.objects.all()
# print (data2[1])
for i in data1:
try:
# print (i[2:-2])
# print (i[0])#id
# print (i[1])#name
print(i[2])
obj = process.extractOne(i[0], sub)
# print (obj[0],' ',obj[1],' ',i[0])
if (obj[1] >= 70):
def test_paramless_no_escaping(self):
with connection.cursor() as cursor:
cursor.execute("SELECT '%s'" + self.bare_select_suffix)
self.assertEqual(cursor.fetchall()[0][0], '%s')
def __init__(self):
self.cursor = connection.cursor()
def remove_eventlog_tables(apps, schema_editor):
cursor = connection.cursor()
# Drop the pinax-eventlog table because we don't use that app anymore
cursor.execute("DROP TABLE IF EXISTS eventlog_log")
def index(request):
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM accounts")
rows = dictfetchall(cursor)
return render(request, template_name="app1/index.html", context={"records":rows})
def main(image=None):
img_name = uuid.uuid1()
if not image:
img = cv2.imread('measurement/template/line_1.jpg')
else:
receive = base64.b64decode(image)
with open('measurement/images/{}.jpg'.format(img_name), 'wb') as f:
f.write(receive)
img = cv2.imread('measurement/images/{}.jpg'.format(img_name))
coordinates, reference_coordinate = line_1_image_process(img)
# print('left', left, 'reference_coordinate', reference_coordinate)
height, width, dimension = img.shape
measurements_data, data = list(), dict()
cursor = connection.cursor()
cursor.execute("select top_left, bottom_right, name from templates where shape = '4' order by name;")
horizontal = cursor.fetchall()
for h in horizontal:
top_left = (int(h[0][0] * width + reference_coordinate[0]), int(h[0][1] * height + reference_coordinate[1]))
bottom_right = (int(h[1][0] * width + reference_coordinate[0]), int(h[1][1] * height + reference_coordinate[1]))
name = h[2]
cv2.rectangle(img, top_left, bottom_right, (0, 255, 0), thickness=1)
coordinates_limit = coordinates[numpy.where(
(coordinates[:, 0] >= top_left[0]) & (coordinates[:, 0] <= bottom_right[0]) &
(coordinates[:, 1] >= top_left[1]) & (coordinates[:, 1] <= bottom_right[1]))]
coordinates_limit_sort = coordinates_limit[coordinates_limit[:, 0].argsort(), :]
measurement = horizontal_measurements(coordinates_limit_sort, img, name)
if measurement:
measurements_data.append(measurement)
data['measurements_data'] = measurements_data
result_name = uuid.uuid1()
cv2.imwrite('measurement/images/{}.jpg'.format(result_name), img)
with open('measurement/images/{}.jpg'.format(result_name), 'rb') as f:
base64_img = base64.b64encode(f.read())
data.update({'image': base64_img})
if os.path.exists('measurement/images/{}.jpg'.format(img_name)):
os.remove('measurement/images/{}.jpg'.format(img_name))
if os.path.exists('measurement/images/{}.jpg'.format(result_name)):
os.remove('measurement/images/{}.jpg'.format(result_name))
# cv2.namedWindow('img_thresh', cv2.WINDOW_NORMAL)
# cv2.imshow("img_thresh", img_thresh)
# cv2.waitKey(0)
#
# cv2.namedWindow('edges', cv2.WINDOW_NORMAL)
# cv2.imshow("edges", edges)
# cv2.waitKey(0)
# cv2.namedWindow('img', cv2.WINDOW_NORMAL)
# cv2.imshow("img", img)
# cv2.waitKey(0)
# cv2.destroyAllWindows()
return data
def handle(self, *args, **options):
do_execute = options['execute']
def fetchall(query):
with connection.cursor() as cursor:
cursor.execute(query)
rows = cursor.fetchall()
return rows
queries = [
"truncate django_migrations",
"update django_content_type set app_label = 'activities' where app_label = 'pickups'",
"update django_content_type set model = 'activity' where model = 'pickupdate'",
"update django_content_type set model = 'activityseries' where model = 'pickupdateseries'",
"update django_content_type set model = 'activityparticipant' where model = 'pickupdatecollector'",
]
def rename_notification_types(type_from, type_to):
for notification_type, in fetchall(
f"select type from notifications_notification where type like '%{type_from}%'"
):
renamed = notification_type.replace(type_from, type_to)
queries.append(
f"update notifications_notification set type = '{renamed}' where type = '{notification_type}'"
)
rename_notification_types('pickup', 'activity')
rename_notification_types('collector', 'participant')
def rename_jsonb_field(table, column, field_from, field_to):
query = """
update {}
set {} = {} - '{}' || jsonb_build_object('{}', {}->'{}')
where {} ? '{}'
""".format(table, column, column, field_from, field_to, column,
field_from, column, field_from)
return re.sub('\\s+', ' ', query).strip()
# rename inside jsonb fields
queries.append(
rename_jsonb_field('notifications_notification', 'context',
'pickup_date', 'activity'))
queries.append(
rename_jsonb_field('notifications_notification', 'context',
'pickup', 'activity'))
queries.append(
rename_jsonb_field('notifications_notification', 'context',
'pickup_collector', 'activity_participant'))
queries.append(
rename_jsonb_field('history_history', 'payload', 'pickup_date',
'activity'))
queries.append(
rename_jsonb_field('history_history', 'payload', 'collectors',
'participants'))
queries.append(
rename_jsonb_field('history_history', 'payload', 'max_collectors',
'max_participants'))
queries.append(
rename_jsonb_field('history_history', 'before', 'pickup_date',
'activity'))
queries.append(
rename_jsonb_field('history_history', 'after', 'pickup_date',
'activity'))
# foreign key columns
def rename_columns(key_from, key_to):
fkey_query = f"""
select table_name, column_name from information_schema.columns where column_name = '{key_from}'
"""
for table_name, column_name in fetchall(fkey_query):
return f"alter table {table_name} rename column {key_from} to {key_to}"
queries.append(rename_columns('pickupdate_id', 'activity_id'))
queries.append(rename_columns('pickup_id', 'activity_id'))
queries.append(rename_columns('max_collectors', 'max_participants'))
# constraints
def rename_constraints(constraint_from, constraint_to):
constraints_query = f"""
select table_name, constraint_name
from information_schema.table_constraints
where constraint_name like '%{constraint_from}%'
"""
for table_name, constraint_name in fetchall(constraints_query):
return "alter table {} rename constraint {} to {}".format(
table_name, constraint_name,
constraint_name.replace(constraint_from, constraint_to))
rename_constraints('pickup', 'activity')
# indexes
def rename_indexes(index_from, index_to):
index_query = f"""
select indexname from pg_indexes where schemaname = 'public' and indexname like '%{index_from}%'
"""
for indexname, in fetchall(index_query):
return "alter index if exists {} rename to {}".format(
indexname, indexname.replace(index_from, index_to))
queries.append(rename_indexes('pickup', 'activity'))
queries.append(rename_indexes('collector', 'participant'))
# sequences
def rename_sequences(sequence_from, sequence_to):
sequence_query = f"select relname from pg_class where relkind = 'S' and relname like '%{sequence_from}%'"
for relname, in fetchall(sequence_query):
return "alter sequence if exists {} rename to {}".format(
relname, relname.replace(sequence_from, sequence_to))
queries.append(rename_sequences('pickup', 'activity'))
queries.append(rename_sequences('collector', 'participant'))
# table renames, do it last so it doesn't invalidate queries that were created above with the old table names
queries.append(
"alter table if exists pickups_pickupdate rename to activities_activity"
)
queries.append(
"alter table if exists pickups_pickupdateseries rename to activities_activityseries"
)
queries.append(
"alter table if exists pickups_pickupdate_collectors rename to activities_activity_participants"
)
queries.append(
"alter table if exists pickups_feedback rename to activities_feedback"
)
if do_execute:
with connection.cursor() as cursor:
for query in queries:
if query:
cursor.execute(query)
print('done')
else:
for query in queries:
if query:
print(query + ';')
def delete(request, id):
with connection.cursor() as cursor:
params = (id,)
cursor.execute("{CALL usp_delete_from_account (%s)}", params)
return redirect("/")
def test_repartition_all_tables(self):
"""
Repartition using driver function
"""
with schema_context(self.schema_name):
aws_lids = AWSCostEntryLineItemDailySummary.objects.order_by(
"-usage_start")[0]
aws_lids.usage_start = aws_lids.usage_start.replace(
year=(aws_lids.usage_start.year + 11))
aws_lids.save()
ocp_lids = OCPUsageLineItemDailySummary.objects.order_by(
"-usage_start")[0]
ocp_lids.usage_start = ocp_lids.usage_start.replace(
year=(aws_lids.usage_start.year + 11))
ocp_lids.save()
with conn.cursor() as cur:
cur.execute(f"""
select (
select count(*) as a_num_recs
from {AWSCostEntryLineItemDailySummary._meta.db_table}_default
) as "num_aws_lids_default",
(
select count(*) as o_num_recs
from {OCPUsageLineItemDailySummary._meta.db_table}_default
) as "num_ocp_lids_default";
""")
res = cur.fetchone()
self.assertTrue(res[0] > 0)
self.assertTrue(res[1] > 0)
ppart.repartition_default_data(schema_name=self.schema_name)
with conn.cursor() as cur:
cur.execute(f"""
select (
select count(*) as a_num_recs
from {AWSCostEntryLineItemDailySummary._meta.db_table}_default
) as "num_aws_lids_default",
(
select count(*) as o_num_recs
from {OCPUsageLineItemDailySummary._meta.db_table}_default
) as "num_ocp_lids_default";
""")
res = cur.fetchone()
self.assertEqual(res, (0, 0))
a_newpart = f"{AWSCostEntryLineItemDailySummary._meta.db_table}_{aws_lids.usage_start.strftime('%Y_%m')}"
o_newpart = f"{OCPUsageLineItemDailySummary._meta.db_table}_{ocp_lids.usage_start.strftime('%Y_%m')}"
with conn.cursor() as cur:
cur.execute(f"""
select (
select count(*) as a_num_recs
from {a_newpart}
) as "num_aws_lids_default",
(
select count(*) as o_num_recs
from {o_newpart}
) as "num_ocp_lids_default";
""")
res = cur.fetchone()
self.assertEqual(res, (1, 1))
# test that insert with new partition bounds will work successfully
new_ocp_lids = OCPUsageLineItemDailySummary(uuid=uuid.uuid4())
for col in (x for x in new_ocp_lids._meta.fields
if x.name != "uuid"):
setattr(new_ocp_lids, col.name,
getattr(ocp_lids, col.name, None))
new_day = (new_ocp_lids.usage_start.day +
1 if new_ocp_lids.usage_start.day < 28 else
new_ocp_lids.usage_start.day - 1)
new_ocp_lids.usage_start = ocp_lids.usage_start.replace(
day=new_day)
new_ocp_lids.save()
with conn.cursor() as cur:
cur.execute(f"""
select (
select count(*) as a_num_recs
from {a_newpart}
) as "num_aws_lids_default",
(
select count(*) as o_num_recs
from {o_newpart}
) as "num_ocp_lids_default";
""")
res = cur.fetchone()
self.assertEqual(res, (1, 2))
def fetchall(query):
with connection.cursor() as cursor:
cursor.execute(query)
rows = cursor.fetchall()
return rows
def set_resources(resources, resource_type):
"""
Helper function which updates the cached resources and marks resources as deleted if no
longer present.
:param resources: iterable of dicts representing the JWPlatform resources
:type resources: iterable
:param resource_type: type of JWPlatform resource (e.g. "video")
:type resource_type: str
Iterates over all of the dicts in *resources* adding or updating corresponding
:py:class:`~.CachedResource` models as it goes. After all resources have been added, any
resources of the specified type which have not been created or updated are deleted from the
cache.
This is all run inside an atomic block. Note that these blocks can be nested so calls to
this function can themselves be within an atomic block.
[1] https://docs.djangoproject.com/en/2.0/topics/db/transactions/#django.db.transaction.atomic
"""
# OK, I need to hold my hand up to this being a horrible, brittle HACK but I couldn't
# determine a clean way to do this with the stock Django ORM. We bypass the ORM entirely
# and roll our own SQL. The general idea is to, atomically,
#
# 1. Create a temporary table to hold all the keys for the resources we inserted/updated in
# the cache.
#
# 2. Insert/update ("upsert") the resources using PostgreSQL's INSERT ... ON CONFLICT
# support. If we insert a new row, created_at and updated_at are set to the statement
# timestamp but if an existing row is updated, only the updated_at timestamp is
# modified. Along the way, we record the keys of the resources which have been
# "upserted" in the temporary table.
#
# 3. Mark all the resources of the appropriate type as "deleted" if their key is not in the
# temporary table.
#
# 4. Drop the temporary table.
#
# This approach lets us send the list of new resources to the database *once* and then lets
# the database sort out evicting/deleting resources from the cache if they weren't inserted
# in this transaction. We could do much of this with the ORM but the native
# update_or_create() method would involve a database round trip for each resource which
# quickly adds up if there are 10,000 of them.
#
# Django currently does not support upsert natively in the ORM. A "better" alternative to
# rolling our own SQL is to use the .on_conflict() support in django-postgres-extra[1] but
# that requires using an entirely different Postgres backend. Using django-postgres-extra
# also introduces another dependency as a very low-level component which it'd be hard to
# migrate from if it becomes abandoned.
#
# [1] http://django-postgres-extra.readthedocs.io/manager/#conflict-handling
with connection.cursor() as cursor:
# A table to hold the list of inserted or updated keys
cursor.execute('''
CREATE TEMPORARY TABLE inserted_or_updated_keys (key TEXT)
''')
# Using execute_batch is many times faster than executemany
# http://initd.org/psycopg/docs/extras.html#fast-exec
#
# There is an argument as to what "now" function we should use here, especially as the
# test suite runs everything within one transaction so using TRANSACTION_TIMESTAMP()
# won't actually give any different values when we run testes.
#
# We use STATEMENT_TIMESTAMP() as a compromise.
#
# The downside of STATEMENT_TIMESTAMP() is that execute_batch() will batch calls so the
# various ..._at timestamps may differ slightly for a given cache update. OTOH, I don't
# think anyone will really care about the ..._at timestamps down to that sort of
# resolution. Given that assumption, I will no doubt be proved wrong when a future data
# analyst berates us when they analyse our database dumps and find out that the
# timestamps have a little bit of non-deterministic noise.
#
# [1] https://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT # noqa: E501
execute_batch(cursor, '''
WITH
insert_result
AS (
INSERT INTO mediaplatform_jwp_cachedresource (
key, data, type, updated_at, created_at, deleted_at
) VALUES (
%(key)s, %(data)s, %(type)s,
STATEMENT_TIMESTAMP(), STATEMENT_TIMESTAMP(), NULL
)
ON CONFLICT (key) DO
UPDATE SET
data = %(data)s, type = %(type)s,
updated_at = STATEMENT_TIMESTAMP(), deleted_at = NULL
WHERE mediaplatform_jwp_cachedresource.key = %(key)s
RETURNING
mediaplatform_jwp_cachedresource.key AS key
)
INSERT INTO inserted_or_updated_keys (key) SELECT key FROM insert_result
''', (
{'key': data['key'], 'data': json.dumps(data), 'type': resource_type}
for data in iter(resources)
))
cursor.execute('''
UPDATE
mediaplatform_jwp_cachedresource
SET
deleted_at = STATEMENT_TIMESTAMP()
WHERE
key NOT IN (SELECT key from inserted_or_updated_keys)
AND type = %(type)s
''', {'type': resource_type})
cursor.execute('''DROP TABLE inserted_or_updated_keys''')
def _send_digest_scheduled(run_date: datetime) -> bool:
with connection.cursor() as cursor:
sql = '''select exists(select id from task_task
where name = %s and (kwargs->>'run_date')::timestamptz = %s)'''
return fetch_bool(cursor, SQLClause(sql, [SendDigest.name, run_date]))
def list(request):
with connection.cursor() as cursor:
cursor.execute('select * from environment')
data = dictfetchall(cursor)
return HttpResponse(format(data), content_type="application/json")
def get_context_data(self, **kwargs):
context = super(IndexView, self).get_context_data(**kwargs)
quantidade_cadastro = {}
agenda_hoje = {}
alertas = {}
data_atual = datetime.now().date()
nfes = NotaFiscal.objects.all()
nro_notas_em_digitacao = 0
nro_notas_autorizadas = 0
nro_notas_canceladas = 0
for nf in nfes:
if nf.status_nfe == '3':
nro_notas_em_digitacao += 1
if nf.status_nfe == '1':
nro_notas_autorizadas += 1
if nf.status_nfe == '8':
nro_notas_canceladas += 1
nro_notas_restantes = 200 - nro_notas_autorizadas
with connection.cursor() as cursor:
cursor.execute("SELECT pg_database_size('SGEO')")
tamanho = cursor.fetchall()[0][0]
context['armazenamento'] = round((tamanho / 1000000), 3)
data_de_hoje = datetime.now().date()
mes_atual = datetime.now().month
ano_atual = datetime.now().year
dias_do_mes = monthrange(ano_atual, mes_atual)[1]
ultimo_dia_mes = datetime(ano_atual, mes_atual, dias_do_mes)
primeiro_dia_mes = datetime(ano_atual, mes_atual, 1)
a_receber_dia = Entrada.objects.filter(
data_vencimento=datetime.now().date(), status__in=['1'])
a_receber_mes = Entrada.objects.filter(
data_vencimento__gte=data_de_hoje,
data_vencimento__lte=ultimo_dia_mes,
status__in=['1'])
recebidos_dia = Entrada.objects.filter(
data_vencimento=datetime.now().date(), status__in=['0'])
recebidos_mes = Entrada.objects.filter(
data_vencimento__lte=data_de_hoje,
data_vencimento__gte=primeiro_dia_mes,
status__in=['0'])
pagamentos_dia = Saida.objects.filter(
data_vencimento=datetime.now().date(), status__in=['0'])
pagamentos_mes = Saida.objects.filter(
data_vencimento__lte=data_de_hoje,
data_vencimento__gte=primeiro_dia_mes,
status__in=['0'])
a_pagar_dia = Saida.objects.filter(
data_vencimento=datetime.now().date(), status__in=['1'])
a_pagar_mes = Saida.objects.filter(data_vencimento__gte=data_de_hoje,
data_vencimento__lte=ultimo_dia_mes,
status__in=['1'])
context['valor_a_receber_dia'] = a_receber_dia.aggregate(
soma_total=Sum('valor_liquido'))['soma_total']
context['valor_a_receber_mes'] = a_receber_mes.aggregate(
soma_total=Sum('valor_liquido'))['soma_total']
context['valor_recebidos_dia'] = recebidos_dia.aggregate(
soma_total=Sum('valor_liquido'))['soma_total']
context['valor_recebidos_mes'] = recebidos_mes.aggregate(
soma_total=Sum('valor_liquido'))['soma_total']
context['valor_a_pagar_dia'] = a_pagar_dia.aggregate(
soma_total=Sum('valor_liquido'))['soma_total']
context['valor_a_pagar_mes'] = a_pagar_mes.aggregate(
soma_total=Sum('valor_liquido'))['soma_total']
context['valor_pago_dia'] = pagamentos_dia.aggregate(
soma_total=Sum('valor_liquido'))['soma_total']
context['valor_pago_mes'] = pagamentos_mes.aggregate(
soma_total=Sum('valor_liquido'))['soma_total']
context['todos_recebidos'] = MovimentoCaixa.objects.order_by(
'data_movimento').all()
context['all_a_receber'] = Entrada.objects.filter(
data_vencimento__gte=datetime.now().date(),
status__in=['1']).order_by('data_vencimento')[:7]
context['qtd_a_receber'] = Entrada.objects.filter(
data_vencimento__gte=datetime.now().date(),
status__in=['1']).count()
context['all_a_pagar'] = Saida.objects.filter(
data_vencimento__gte=datetime.now().date(),
status__in=['1']).order_by('data_vencimento')[:7]
context['qtd_a_pagar'] = Saida.objects.filter(
data_vencimento__gte=datetime.now().date(),
status__in=['1']).count()
context['entradas_atrasadas'] = Entrada.objects.filter(
data_vencimento__lt=datetime.now().date(),
status__in=['1', '2']).order_by('data_vencimento')[:7]
context['pagamentos_atrasados'] = Saida.objects.filter(
data_vencimento__lt=datetime.now().date(),
status__in=['1', '2']).order_by('data_vencimento')[:7]
context['contador_nfe_em_digitacao'] = nro_notas_em_digitacao
context['contador_nfe_autorizada'] = nro_notas_autorizadas
context['contador_nfe_cancelada'] = nro_notas_canceladas
context['contador_nfe_restantes'] = nro_notas_restantes
context['contador_nfe_total'] = nfes.count()
context[
'certificado_a1'] = ConfiguracaoNotaFiscal.arquivo_certificado_a1
context['data_atual'] = data_atual.strftime('%d/%m/%Y')
quantidade_cadastro['clientes'] = Cliente.objects.all().count()
quantidade_cadastro['fornecedores'] = Fornecedor.objects.all().count()
quantidade_cadastro['produtos'] = Produto.objects.all().count()
#quantidade_cadastro['servicos'] = Servico.objects.all().count()
quantidade_cadastro['empresas'] = Empresa.objects.all().count()
quantidade_cadastro['transportadoras'] = Transportadora.objects.all(
).count()
context['quantidade_cadastro'] = quantidade_cadastro
agenda_hoje['orcamento_venda_hoje'] = OrcamentoVenda.objects.filter(
data_vencimento=data_atual, status='0').count()
agenda_hoje['orcamento_compra_hoje'] = OrcamentoCompra.objects.filter(
data_vencimento=data_atual, status='0').count()
agenda_hoje['pedido_venda_hoje'] = PedidoVenda.objects.filter(
data_entrega=data_atual, status='0').count()
agenda_hoje['pedido_compra_hoje'] = PedidoCompra.objects.filter(
data_entrega=data_atual, status='0').count()
agenda_hoje['contas_receber_hoje'] = Entrada.objects.filter(
data_vencimento=data_atual, status__in=['1', '2']).count()
agenda_hoje['contas_pagar_hoje'] = Saida.objects.filter(
data_vencimento=data_atual, status__in=['1', '2']).count()
context['agenda_hoje'] = agenda_hoje
alertas['produtos_baixo_estoque'] = Produto.objects.filter(
estoque_atual__lte=F('estoque_minimo')).count()
alertas['orcamentos_venda_vencidos'] = OrcamentoVenda.objects.filter(
data_vencimento__lte=data_atual, status='0').count()
alertas['pedidos_venda_atrasados'] = PedidoVenda.objects.filter(
data_entrega__lte=data_atual, status='0').count()
alertas['orcamentos_compra_vencidos'] = OrcamentoCompra.objects.filter(
data_vencimento__lte=data_atual, status='0').count()
alertas['pedidos_compra_atrasados'] = PedidoCompra.objects.filter(
data_entrega__lte=data_atual, status='0').count()
alertas['contas_receber_atrasadas'] = Entrada.objects.filter(
data_vencimento__lte=data_atual, status__in=['1', '2']).count()
alertas['contas_pagar_atrasadas'] = Saida.objects.filter(
data_vencimento__lte=data_atual, status__in=['1', '2']).count()
context['alertas'] = alertas
try:
context['movimento_dia'] = MovimentoCaixa.objects.get(
data_movimento=data_atual)
except (MovimentoCaixa.DoesNotExist, ObjectDoesNotExist):
ultimo_mvmt = MovimentoCaixa.objects.filter(
data_movimento__lt=data_atual)
if ultimo_mvmt:
context['saldo'] = ultimo_mvmt.latest(
'data_movimento').saldo_final
else:
context['saldo'] = '0,00'
return context
