christianmlong + query   43

postgresql - Querying non-ASCII rows from Postgres - Database Administrators Stack Exchange
t, regexp_replace(t, '([^[:ascii:]])', '[\1]', 'g') AS t_marked
t ~ '[^[:ascii:]]' ;
postgres  sql  regex  unicode  ascii  query 
november 2018 by christianmlong
PyPika - Python Query Builder — PyPika 0.15.5 documentation
PyPika is a Python API for building SQL queries. The motivation behind PyPika is to provide a simple interface for building SQL queries without limiting the flexibility of handwritten SQL. Designed with data analysis in mind, PyPika leverages the builder design pattern to construct queries to avoid messy string formatting and concatenation. It is also easily extended to take full advantage of specific features of SQL database vendors.
python  sql  orm  query  builder  pika 
september 2018 by christianmlong
Idiot's query: proper way to handle NULL in SELECT query?
Let's say you do this in a where clause

select *
from my_table
where a = %s

and then you use the python dbapi way of inserting sql parameters (to avoid sql injection)

cursor.execute(sql, params=(None,))

Looking at the actual sql with cursor.query, we see that it has translated None to NULL not translated the '= NULL' to 'is NULL'. D'oh!

I think what you have to do is check for None yourself, and decide if you want to use the '=' operator (if the parameter is not None) or the 'is' operator (if the parameter is None).
python  dbapi  psycopg2  is  null  none  query  where  annoyances  gotcha 
march 2018 by christianmlong
#25582 (Add a way to build URLs with query strings) – Django
def reverse(*args, **kwargs):
get = kwargs.pop('get', {})
url = orig_reverse(*args, **kwargs)
if get:
url += '?' + urllib.urlencode(get)
return url
django  reverse  url  query  string 
june 2017 by christianmlong
A Django template library for manipulating URLs
Spurl is not a replacement for Django's built-in {% url %} template tag. It is a general-purpose toolkit for manipulating URL components in templates. You can use it alongside {% url %} if you like (see below).

Spurl provides a single template tag, called (surprisingly enough), spurl. You call it with a set of key=value keyword arguments, which are described fully below.
django  url  query  string  reverse 
june 2017 by christianmlong
Difference between stats and chart - Question | Splunk Answers
Let's compare with two examples:

* | stats sum(x) by user, host, status will output rows that look like:

user host status sum(x)
bob host1 200 25
bob host1 404 12
bob host2 404 3
alice host1 200 17
alice host2 500 1

2) But * | chart sum(x) over user by status will output quite different rows that look like.

user 200 404 500
bob 25 15
alice 17 1

Note that the first example incorporates data about the "host" field, whereas the second one does not. We'll come back to this.

In more formal terms, stats sum(x) by user, host, status will create one row for each combination of user, host and status that are present in the data. Then for each of those rows it will also compute whatever statistic(s) or function(s) you tell it (here it's just sum(x)).

On the other hand, the chart command, will create rows that are each of the values of the single "group by" field, and COLUMNS that are each of the values of the "split by" field. (btw the timechart command you can sort of think of chart that is locked into using _time as the "group-by" field, although the reality is a little more complex)
splunk  chart  stats  xyseries  table  query  explanation  data  untable 
may 2016 by christianmlong
PostgreSQL: Documentation: 9.1: WITH Queries (Common Table Expressions)
Recursive queries are typically used to deal with hierarchical or tree-structured data. A useful example is this query to find all the direct and indirect sub-parts of a product, given only a table that shows immediate inclusions:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
SELECT p.sub_part, p.part, p.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part
postgres  sql  tree  hierarchy  parent  child  query  recursive 
september 2015 by christianmlong
sql server 2005 - SQL Query for Parent Child Relationship - Stack Overflow
with [CTE] as (
select * from [TheTable] c where c.[ParentId] = 1
union all
select * from [CTE] p, [TheTable] c where c.[ParentId] = p.[NodeId]
select * from [CTE]
sql  tree  parent  child  hierarchy  query 
september 2015 by christianmlong
Using DbLink to access other PostgreSQL Databases and Servers - Postgres OnLine Journal
Joining with Local Data

This is the major area where DbLink falls short. While you can join remote dblinked tables with local tables, for large tables, this tends to be really slow as indexes are not used and DBLinked function call needs to return the full dataset before they can be joined. Below is a simple example of a join:

SELECT realestate.address, realestate.parcel, s.sale_year, s.sale_amount,
FROM realestate INNER JOIN
dblink('dbname=somedb port=5432 host=someserver
user=someuser password=somepwd',
'SELECT parcel_id, sale_year,
sale_amount FROM parcel_sales')
AS s(parcel_id char(10),sale_year int, sale_amount int)
ON realestate.parcel_id = s.parcel_id;

If sales table is relatively huge, then you may be better forcing a nested join by creating a function that queries dblink and putting that in the SELECT clause and having a dynamic dblink query of the form.

payment_id integer,
customer_id smallint,
staff_id smallint,
rental_id integer,
amount numeric(5,2),
payment_date timestamp);

CREATE OR REPLACE FUNCTION fn_remote_payments(cust int)

SELECT * FROM dblink('dbname=pagila host=localhost user=pagila',
'SELECT payment_id, customer_id, staff_id,
rental_id, amount, payment_date
FROM payment WHERE customer_id = ' || $1) As
p(payment_id int,customer_id smallint,
staff_id smallint,
rental_id integer, amount numeric(5,2),
payment_date timestamp )


SELECT rs.customer_id, realestate.address,
realestate.parcel, (fn_remote_payments(rs.customer_id)).*
FROM realestate rs
WHERE rs.last_name = 'SMITH';
postgres  dblink  remote  query  sql  linked  server 
august 2015 by christianmlong
Yahoo! Query Language (YQL) Guide - YDN

This guide describes the Yahoo! Query Language (YQL) Web Service, which enables you to access Internet data with SQL-like commands. This guide is for software developers who are famliar with Web applications that call Web services to retrieve data in XML or JSON format. Experience with SQL, MySQL, or Yahoo! Pipes is helpful, but not required.
yahoo  yql  web  services  data  query  docs 
february 2010 by christianmlong
YQL - Community Open Data Tables
What's YQL? select * from internet
The Yahoo! Query Language (YQL) platform enables developers to query, filter, and combine data across the web. YQL exposes a SQL-like syntax that is both familiar to developers and expressive enough for getting the right data. YQL supports three SQL-like verbs:

* SELECT for fetching, combining, filtering and projecting data.
* DESC for describing the input fields for a table and other meta information.
* SHOW for getting a list of the tables/data sources supported by the language/platform.

The SELECT statement is the primary verb for YQL:

SELECT what FROM table WHERE filter

In addition, YQL also supports several post-query functions like sort and unique.
yahoo  yql  web  services  data  query 
february 2010 by christianmlong
Yahoo! Query Language - YDN
The Yahoo! Query Language is an expressive SQL-like language that lets you query, filter, and join data across Web services
yahoo  yql  web  services  data  query 
february 2010 by christianmlong
Benthic Software - Products
Golden is an ad-hoc query tool with many powerful features. It features close compatibility with SQLPLus' scripting conventions including variable prompting and script parameter passing.
oracle  database  query  tool  manager  analyzer  sql  SQLPlus 
august 2006 by christianmlong
Free tool from the makers of TOAD
oracle  database  query  tool  manager  analyzer  sql  plsql  programming 
august 2006 by christianmlong
JMatrix - DataBrowser
The DataBrowser is a Java based cross-database, cross-platform data access tool. It is stable, feature rich, and mature (initially released in 1999). As of June 2004 it has over 16,000 users worldwide.
oracle  database  query  tool  manager  analyzer  sql  SQLPlus 
july 2005 by christianmlong

related tags

analyzer  annoyances  ascii  boolean  bug  builder  case  chart  child  config  cte  data  database  dbapi  dblink  demorgans_law  desktop  developer  development  django  django_rest_framework  docs  equal  eval  excel  explain  explanation  export  filter  foaf  gist  gotcha  hierarchy  hint  ibm  index  indexing  insensitive  in_memory  is  join_collapse_limit  linked  log  logic  lower  manager  metadata  mutual_friends  ne  negation  nevra  none  not  null  object  optimizer  oracle  orm  pandas  parent  parser  performance  pika  plan  planner  plsql  postgres  predicate  prefetch  profiling  programming  psycopg2  python  py_spy  q  query  queryset  recursive  regex  remote  reverse  rpm  scan  search  select_related  sensitive  server  service  services  slow  sort  splunk  sql  sqlite  SQLPlus  stats  string  syntax  table  tool  tree  tuning  twisted  unicode  untable  url  version  web  where  windows  xyseries  yahoo  yql  yum 

Copy this bookmark: