Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
List of helpful script and sql
select * from
(select callTime,callType,employee,phoneNumber,count(*) as n from table2 group by callTime,callType,employee,phoneNumber) x
where x.n > 1;
delete a
from leads a
LEFT JOIN
(
SELECT MIN(ID) ID,phone
FROM leads
GROUP BY phone
) b ON a.ID = b.ID AND
a.phone = b.phone
WHERE b.ID IS NULL;
DELETE a
FROM table1 as a, table1 as b
WHERE
(a.phone = b.phone OR a.phone IS NULL AND b.phone IS NULL)
AND (a.comments = b.comments OR a.comments IS NULL AND b.comments IS NULL)
AND (a.rm = b.rm OR a.rm IS NULL AND b.rm IS NULL)
AND (a.voice_record = b.voice_record OR a.voice_record IS NULL AND b.voice_record IS NULL)
AND (a.call_duration = b.call_duration OR a.call_duration IS NULL AND b.call_duration IS NULL)
AND (a.disposition = b.disposition OR a.disposition IS NULL AND b.disposition IS NULL)
AND (a.call_duration = b.call_duration OR a.call_duration IS NULL AND b.call_duration IS NULL)
AND a.submission_id < b.submission_id;
delete a
from table1 a
LEFT JOIN
(
SELECT MIN(submission_id) submission_id,phone
FROM table1
GROUP BY form_id,phone,rm,comments,disposition,call_duration,voice_record
) b ON a.submission_id = b.submission_id AND
(a.phone = b.phone OR a.phone IS NULL AND b.phone IS NULL)
AND (a.comments = b.comments OR a.comments IS NULL AND b.comments IS NULL)
AND (a.rm = b.rm OR a.rm IS NULL AND b.rm IS NULL)
AND (a.voice_record = b.voice_record OR a.voice_record IS NULL AND b.voice_record IS NULL)
AND (a.call_duration = b.call_duration OR a.call_duration IS NULL AND b.call_duration IS NULL)
AND (a.disposition = b.disposition OR a.disposition IS NULL AND b.disposition IS NULL)
AND (a.call_duration = b.call_duration OR a.call_duration IS NULL AND b.call_duration IS NULL)
WHERE b.submission_id IS NULL;
import boto3
import os
aws_session = boto3.Session(profile_name='prod')
s3_client = aws_session.client('s3')
def download_dir(prefix, local, bucket, client=s3_client):
"""
params:
- prefix: pattern to match in s3
- local: local path to folder in which to place files
- bucket: s3 bucket with target contents
- client: initialized s3 client object
"""
keys = []
dirs = []
next_token = ''
base_kwargs = {
'Bucket':bucket,
'Prefix':prefix,
}
while next_token is not None:
kwargs = base_kwargs.copy()
if next_token != '':
kwargs.update({'ContinuationToken': next_token})
results = client.list_objects_v2(**kwargs)
contents = results.get('Contents')
for i in contents:
k = i.get('Key')
if k[-1] != '/':
keys.append(k)
else:
dirs.append(k)
next_token = results.get('NextContinuationToken')
for d in dirs:
dest_pathname = os.path.join(local, d)
if not os.path.exists(os.path.dirname(dest_pathname)):
os.makedirs(os.path.dirname(dest_pathname))
for k in keys:
dest_pathname = os.path.join(local, k)
if not os.path.exists(os.path.dirname(dest_pathname)):
os.makedirs(os.path.dirname(dest_pathname))
client.download_file(bucket, k, dest_pathname)
download_dir('sample_format/','/home/avinash/Templates','bucketname')
<?php
print_r(array(
'sql' => $dq->getSQL(),
'parameters' => $dq->getParameters(),
));
var_dump($dq->getParams());
exit();
$sql=$db->getSQL();
?>
SELECT COLUMN_NAME AS 'ColumnName'
,TABLE_NAME AS 'TableName'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%MyName%'
ORDER BY TableName
,ColumnName;
UPDATE table
SET
columnname = REPLACE(columnname,
'value2',
'value1')
WHERE
columnname like '%value1%';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment