DEV Community: ZtoloGame

sql - Mysql Calculate rank of teams from different rows

ZtoloGame — Wed, 07 Sep 2022 07:28:44 +0000

Question:
I'm trying to build a kind of peddy paper. For that I have the following tables:

teams

CREATE TABLE teams (
id int(11) NOT NULL AUTO_INCREMENT,
creator_id int(11) NOT NULL,
friend_id int(11) DEFAULT NULL,
team_name varchar(128) NOT NULL,
PRIMARY KEY (id)
);
team_log

CREATE TABLE IF NOT EXISTS progress_tracker (
id int(8) NOT NULL AUTO_INCREMENT,
user_id int(8) NOT NULL,
team_id int(11) NOT NULL,
date date NOT NULL,
clues_found int(11) NOT NULL,
clues_to_find int(11) NOT NULL,
PRIMARY KEY (id)
);
Each team is composed by two users;
Each user starts out with a variable number of clues found;
clues_found can either increase or decrease. No guarantee that the highest number is the latest;
I need to get a rank of the teams (in percentage) based on the average of the number of clues the user found since they joined (for both users in a team) - clues_found on the row with biggest date minus clues_found on the record with lowest date).

For instance if I have the following data for each table:

teams table data

+--------+------------+------------+---------------+
| id | creator_id | friend_id | team_name |
+--------+------------+------------+---------------+
| 1 | 25 | 28 | Test1 |
| 2 | 31 | 5 | Test2 |
+--------+------------+------------+---------------+
team_log table data

+--------+---------+---------+------------+-------------+---------------+
| id | user_id | team_id | date | clues_found | clues_to_find |
+--------+---------+---------+------------+-------------+---------------+
| 1 | 25 | 1 | 2013-01-6 | 3 | 24 |
| 2 | 25 | 1 | 2013-01-8 | 7 | 24 |
| 3 | 25 | 1 | 2013-01-10 | 10 | 24 |
| 4 | 28 | 1 | 2013-01-8 | 5 | 30 |
| 5 | 28 | 1 | 2013-01-14 | 20 | 30 |
| 6 | 31 | 2 | 2013-01-11 | 6 | 14 |
| 7 | 5 | 2 | 2013-01-9 | 2 | 20 |
| 8 | 5 | 2 | 2013-01-10 | 10 | 20 |
| 9 | 5 | 2 | 2013-01-12 | 14 | 20 |
+--------+---------+---------+------------+-------------+---------------+
Desired Result

+-------------+---------------------+
| team_id | team_percentage |
+-------------+---------------------+
| 1 | 39,58333333 |
| 2 | 30 |
+-------------+---------------------+
As a reference this is an intermediate representation which might help to understand:

+-------------+---------+---------------------+
| user_id | team_id | precentage_per_user |
+-------------+---------+---------------------+
| 25 | 1 | 29,16666667 |
| 28 | 1 | 50 |
| 31 | 2 | 0 |
| 5 | 2 | 60 |
+-------------+---------+---------------------+
So far I have the following sql:

SELECT STRAIGHT_JOIN
tl2.team_id, (tl2.weight - tl1.weight)*100/tl2.clues_to_find
from
( select
team_id,user_id,clues_found
FROM
team_log
where 1

        group by
           team_id, user_id
        order by
           `date` ) base
   join (select team_id, user_id, clues_found, clues_to_find from `team_log` where user_id = base.user_id and team_id = base.team_id group by team_id, user_id order by `date` desc) tl2

But this returns an error as I'm not allowed to use base.user_id inside the second query. I'm also not very sure I'm heading in the right direction.

Can anyone help please?

Solution 1:

Here's another query that will produce the correct result:

SELECT calc.team_id, AVG((calc.end_clues - calc.start_clues)/calc.total_clues*100) as team_percentage
FROM
(SELECT log1.user_id, log1.team_id, log1.clues_found as start_clues, log2.clues_found as end_clues, log2.clues_to_find as total_clues FROM team_log log1
JOIN
(SELECT MIN(id) as start_id, MAX(id) as end_id FROM team_log GROUP BY user_id) ids
ON ids.start_id = log1.id
JOIN team_log log2 ON ids.end_id = log2.id) calc
GROUP BY team_id
ORDER BY team_id;
And the SQL Fiddle-link...

Solution 2:

Please take a look at this and comment:

SQLFIDDLE DEMO
Team pct:

select z.team_id, avg(z.pct) as teampct
from (
select x.user_id, y.team_id, x.mndate,
y.mxdate, x.mnclues_found,
y.mxclues_found,
(((y.mxclues_found - x.mnclues_found)*100)
/y.mxclues_tofind) pct
from
(select user_id, team_id, min(date) mndate,
min(clues_found) as mnclues_found
from team_log
group by user_id, team_id) x
left join
(select user_id, team_id, max(date) mxdate,
max(clues_found) as mxclues_found,
max(clues_to_find) as mxclues_tofind
from team_log
group by user_id, team_id) y
on x.user_id = y.user_id and
x.team_id = y.team_id) z
group by z.team_id
;
Results 1:

| USER_ID | TEAM_ID | MNDATE | MXDATE | MNCLUES_FOUND | MXCLUES_FOUND | PCT |

| 5 | 2 | 13-01-09 | 13-01-12 | 2 | 14 | 60 |
| 25 | 1 | 13-01-06 | 13-01-10 | 3 | 10 | 29.1667 |
| 28 | 1 | 13-01-08 | 13-01-14 | 5 | 20 | 50 |
| 31 | 2 | 13-01-11 | 13-01-11 | 6 | 6 | 0 |
Results final:

| TEAM_ID | TEAMPCT |

| 1 | 39.58335 |
| 2 | 30 |
Solution 3:

This is a bit ugly, but should work:

select
team_id,
AVG(percentage_per_user) as team_percentage
from (select
team_id,
user_id,
((select clues_found from progress_tracker as x
where x.user_id = m.user_id order by x.date desc limit 0, 1)
- (select clues_found from progress_tracker as y
where y.user_id = m.user_id order by y.date asc limit 0, 1))
/ MAX(clues_to_find)
as percentage_per_user
from progress_tracker as m
group by team_id, user_id
) as userScore
group by team_id
order by team_percentage desc;

sql — Select Records multiple times from table

ZtoloGame — Fri, 02 Sep 2022 09:33:53 +0000

Question:
I have a SQL statement like this:

EDIT :

SELECT
location as Location
FROM
Table1
WHERE
OnsiteOffshore = 'Offshore' AND Acc_Code = 'ABC'

UNION

SELECT
Country
FROM
Table1
WHERE
OnsiteOffshore = 'Onsite' AND Acc_Code = 'ABC'
This SQL query gives these results:

Chennai
Bangalore
USA
NewZealand
But due to some requirement I need the output like this:

Chennai
Chennai
Chennai
Chennai
Bangalore
Bangalore
Bangalore
Bangalore
USA
USA
USA
USA
NewZealand
NewZealand
NewZealand
NewZealand
Mean to say each location needs to be output 4 times.

Pls help how to get the same.

Solution 1:

SELECT Location
FROM Table1
CROSS JOIN
( VALUES (1),(2),(3),(4)
) AS four(dummy)
If the 4 is not a constant but (as @xQbert noticed/asked) is the number of rows of the table, you can use this:

SELECT a.Location
FROM Table1 AS a
CROSS JOIN
Table1 AS b
If you don't have Table1 but any (however complex) query, you could use this for 4 copies:

SELECT Location
FROM (
SELECT Location --- complex query here
... --- inside parenthesis
UNION
SELECT Country
...
) AS Table1
CROSS JOIN
( VALUES (1),(2),(3),(4)
) AS four(dummy)
or this for n copies:

WITH cte AS
( SELECT Location --- complex query here
... --- inside parenthesis
UNION
SELECT Country
...
)
SELECT a.Location
FROM cte AS a
CROSS JOIN
cte AS b
Solution 2:

Simplest and (probably) fully accepted in any RDBMS ;-):

select location from (
Select Location From Table1 union all
Select Location From Table1 union all
Select Location From Table1 union all
Select Location From Table1
) t
order by location
And better way with CTE (Common Table Expressions):

;with cte (id) as (
select 1 union all
select id + 1 from cte where id < 4
)
select location from Table1
cross join cte
Solution 3:

You can create a wrapper procedure for this... where u first create a cursor for Select Location From Table1 than you can loop through this cursor and extract the data whatever way want.

optionally you can use some front end technology to do this. This is not a tough task in front end languages i.e. Java, C++, .NET or in any other popular language.

SQL SELECT from multiple tables
Question:
How can I get all products from customers1 and customers2 include their customer names?

customer1 table
cid name1
1 john
2 joe

customer2 table
cid name2
p1 sandy
p2 linda

product table
pid cid pname
1 1 phone
2 2 pencil
3 p1 pen
4 p2 paper
Result should be like this

pid cid pname name1 name2
1 1 phone john NULL
2 2 pencil joe NULL
3 p1 pen NULL sandy
4 p2 paper NULL linda

Solution 1:

SELECT p.pid, p.cid, p.pname, c1.name1, c2.name2
FROM product p
LEFT JOIN customer1 c1 ON p.cid = c1.cid
LEFT JOIN customer2 c2 ON p.cid = c2.cid
Solution 2:

SELECT pid, cid, pname, name1, name2
FROM customer1 c1, product p
WHERE p.cid=c1.cid
UNION SELECT pid, cid, pname, name1, name2
FROM customer2 c2, product p
WHERE p.cid=c2.cid;
Solution 3:

SELECT product.*, customer1.name1, customer2.name2
FROM product
LEFT JOIN customer1 ON product.cid = customer1.cid
LEFT JOIN customer2 ON product.cid = customer2.cid

python - How to Count bugs in an image?

ZtoloGame — Fri, 02 Sep 2022 09:25:15 +0000

Question:
I have a picture like the below and I would like to count the number of bugs (continuous blobs of color/grey) that show up on it with Python. How could I do this best?

Bugs on a noisy background

I've so far looked at ImageChops, SciPy and PIL but I'm unsure what I can/should use...

I think I can use ndimage.gaussian_filter() and then scipy.ndimage.measurements.label() am just not sure yet how I use latter to count my blue dots in the gaussian-ized image...... it looks something likeenter image description here

Okay,

With above image I now got this code:

! /usr/bin/python

import numpy as np
import scipy
import pylab
import pymorph
import mahotas
from PIL import Image
import PIL.ImageOps
from scipy import ndimage

image = Image.open('bugs.jpg')

inverted_image = PIL.ImageOps.invert(image)

inverted_image.save('in_bugs.jpg')
dna = mahotas.imread('in_bugs.jpg')

pylab.imshow(dna)

pylab.gray()

pylab.show()

T = mahotas.thresholding.otsu(dna)
pylab.imshow(dna > T)

pylab.show()

dnaf = ndimage.gaussian_filter(dna, 8)
T = mahotas.thresholding.otsu(dnaf)
pylab.imshow(dnaf > T)

pylab.show()

labeled,nr_objects = ndimage.label(dnaf > T)
print nr_objects
pylab.imshow(labeled)
pylab.jet()
pylab.show()
the problem is, this returns me a number of 5 which isn'rt that bad but I need to have it more accurate, I want to see two. How can I do this? Will it help to blur the image before applying the gaussian filter?

Thanks for help!

Ron

Solution 1:

Your gaussian filtering is mostly fine already, but you are considering a radius way bigger than needed for the task. For instance, let us consider a kernel of radius 15 as an example. Here is a representation of what we get:

enter image description here

There are two clear valleys (yes, shown as peaks instead) and the histogram of the filtered image shows that most of the data available is now close to the maximum value possible.

enter image description here

Considering only a part of the histogram we can better see the part of the data we are interested: the darker spots.

enter image description here

So, with a simple threshold at 0.5 this is the result (which matches where the bugs are):

enter image description here

Depending on how you implement (or the libraries you use implement) the related functions, this threshold value will vary. But by looking at the histogram you will be able to spot a good threshold. Now, if you don't want to guess this threshold by looking at the histogram then you need to preprocess your image beyond the gaussian filtering. By doing this step in a good manner, your image gets simple enough that methods like the one given by Otsu can automatically find the threshold you are after. Performing a morphological closing followed by a morphological opening, and then binarizing by Otsu, this is the result we get:

enter image description here

The shapes are closer to the initial ones, since we didn't rely on a linear low-pass filter which at best will blur contours.

EDIT:

Since the question now includes some code, I felt the need to explain why it is wrong to use Otsu as the code there is doing. The method given by Otsu for thresholding actually expects the data to be bimodal but, as the histogram plots above shows, this is not the case here. Otsu will provide a threshold that is too close to the huge peak at the right, while the good point at 0.5 is very far from there. To replicate the first result shown in this answer, here is some basic code:

import sys
import numpy
from PIL import Image
from scipy.ndimage import gaussian_filter, label

img = Image.open(sys.argv[1]).convert('L')
im = numpy.array(img)

im_g = gaussian_filter(im, 3)
im_norm = (im_g - im_g.min()) / (float(im_g.max()) - im_g.min())
im_norm[im_norm < 0.5] = 0
im_norm[im_norm >= 0.5] = 1

result = 255 - (im_norm * 255).astype(numpy.uint8)
print u"Objects: %d" % label(result)[1]

Image.fromarray(result).save(sys.argv[2])
Note that this code is using sigma = 3 (while originally 7.5 was used) for the gaussian kernel, from which scipy internally builds a window with a radius 4 times larger than it. For this particular image, a great range of sigma works just as well, starting from 2 up to 10 should give the same results -- 2 objects detected.

Solution 2:

This is a task of computer vision, which you could solve with the popular OpenCV.

You might want to consider processing the image with some morphological operations (e.g. opening) to remove the noise. Perhaps then you could count the number of contiguous blobs with area above some threshold.

Some resources to look into:

Object recognition (Wikipedia)
Computer vision (Wikipedia)
OpenCV Cookbook

git — Fatal: Not possible to fast-forward, aborting

ZtoloGame — Fri, 02 Sep 2022 09:13:18 +0000

Top 5 Answer for git — Fatal: Not possible to fast-forward, aborting

git pull --rebase. Unlike the other solution, you don't need to know the name of your destination branch.

If your upstream branch is not set, try git pull origin --rebase (credit to @rick in the comments)

Your branch is no longer directly based off of the branch you’re trying to merge it into — e.g. another commit was added to the destination branch that isn’t in your branch. Thus, you can’t fast-forward into it (because fast-forward requires your branch to completely contain the destination branch).

You can rebase your branch on top of the destination branch (git rebase ) to rework the commits such that they will fast forward into it, or you can do a regular merge.

If
git pull
does not do the trick and if you want to merge both the current changes and the changes that’d come from the pull of the branch from origin then do this:-

git merge origin/BRANCH_NAME
After that, resolve the merge conflicts if any and done for the day.
3

This is because you have enabled the fast-forward only option. The thing here is your pull from the branch will create a merge commit in your local git and the fast-forward only option doesn’t allow creating a merge commit at the time of pull.

In the case of a big team, You will end up rebasing and resolving conflicts lots of the time and for each and every commit coming from the pull.

I suggest you remove ff = only line from git local config file.

$ cd to-my-project-root-dir

$ nano .git/config

[pull] ff = only // remove this line rebase = false
5

git pull --no-ff -> make fast-forwarding to be off by --no-ff

android - Get root view from current activity

ZtoloGame — Thu, 18 Aug 2022 10:19:20 +0000

Question from androidandrey:
I know how to get the root view with View.getRootView(). I am also able to get the view from a button's onClick event where the argument is a View. But how can I get the view in an activity?

Solution 1:

If you need root view of your activity (so you can add your contents there) use

findViewById(android.R.id.content).getRootView()
Also it was reported that on some devices you have to use

getWindow().getDecorView().findViewById(android.R.id.content)
instead.

Please note that as Booger reported, this may be behind navigation bar (with back button etc.) on some devices (but it seems on most devices it is not).

If you need to get view that you added to your activity using setContentView() method then as pottedmeat wrote you can use

final ViewGroup viewGroup = (ViewGroup) ((ViewGroup) this
.findViewById(android.R.id.content)).getChildAt(0);
But better just set id to this view in your xml layout and use this id instead.

Solution 2:

This is what I use to get the root view as found in the XML file assigned with setContentView:

final ViewGroup viewGroup = (ViewGroup) ((ViewGroup) this
.findViewById(android.R.id.content)).getChildAt(0);
Solution 3:

I tested this in android 4.0.3, only:

getWindow().getDecorView().getRootView()
give the same view what we get from

anyview.getRootView();

com.android.internal.policy.impl.PhoneWindow$DecorView@#########
and

getWindow().getDecorView().findViewById(android.R.id.content)
giving child of its

android.widget.FrameLayout@#######
Please confirm here.

mysql — How to get the count of each distinct value in a column?

ZtoloGame — Wed, 17 Aug 2022 09:56:06 +0000

Question:
This question already has answers here : https://sqlerrors.com/howto/392/mysql---How-to-get-the-count-of-each-distinct-value-in-a-column?#mysql---How-to-get-the-count-of-each-distinct-value-in-a-column?
Count the occurrences of DISTINCT values (3 answers)
Closed last year at : https://sqlerrors.com/.
I have an SQL table called "posts" that looks like this:

As an example, such a query might return a symbolic array such as this: (1:3, 2:2, 3:1, 4:1)

My current method is to use queries for each possible category, such as: SELECT COUNT(*) AS num FROM posts WHERE category=#, and then combine the return values into a final array. However, I'm looking for a solution that uses only one query.

Solution 1:

SELECT
category,
COUNT(*) AS num
FROM
posts
GROUP BY
category
mysql - How to count the number of instances of each foreign-key ID in a table?
Question:
Here's my simple SQL question...

I have two tables:

Books

| book_id | author | genre | price | publication_date |

Orders

| order_id | customer_id | book_id |

I'd like to create a query that returns:

| book_id | author | genre | price | publication_date | number_of_orders |

In other words, return every column for ALL rows in the Books table, along with a calculated column named 'number_of_orders' that counts the number of times each book appears in the Orders table. (If a book does not occur in the orders table, the book should be listed in the result set, but "number_of_orders" should be zero.

So far, I've come up with this:

SELECT
books.book_id,
books.author,
books.genre,
books.price,
books.publication_date,
count(*) as number_of_orders
from books
left join orders
on (books.book_id = orders.book_id)
group by
books.book_id,
books.author,
books.genre,
books.price,
books.publication_date
That's almost right, but not quite, because "number_of_orders" will be 1 even if a book is never listed in the Orders table. Moreover, given my lack of knowledge of SQL, I'm sure this query is very inefficient.

What's the right way to write this query? (For what it's worth, this needs to work on MySQL, so I can't use any other vendor-specific features).

Thanks in advance!

Solution 1:

Your query is almost right and it's the right way to do that (and the most efficient)

SELECT books., count(orders.book_id) as number_of_orders

from books
left join orders
on (books.book_id = orders.book_id)
group by
books.book_id
COUNT() could include NULL values in the count because it counts all the rows, while COUNT(orders.book_id) does not because it ignores NULL values in the given field.

Solution 2:

SELECT b.book_id,
b.author,
b.genre,
b.price,
b.publication_date,
coalesce(oc.Count, 0) as number_of_orders
from books b
left join (
select book_id, count(*) as Count
from Order
group by book_id
) oc on (b.book_id = oc.book_id)
Solution 3:

Change count(*) to count(orders.book_id)

python - How to display a message box on PyQT4?

ZtoloGame — Wed, 17 Aug 2022 09:41:09 +0000

Question:
I'd like a MessageBox to display when I click a button on my simple PyQT application. How can I declare two textboxes and have a MessageBox display with the text from both textboxes?

Here's my code far:

import sys
from PyQt4 import QtGui, QtCore

class myWindow(QtGui.QWidget):
def init(self, parent=None):
QtGui.QWidget.init(self, parent)

    #The setGeometry method is used to position the control.
    #Order: X, Y position - Width, Height of control.
    self.setGeometry(300, 300, 500, 350)
    self.setWindowTitle("Sergio's QT Application.")
    self.setWindowIcon(QtGui.QIcon('menuScreenFolderShadow.png'))

    self.setToolTip('<i>Welcome</i> to the <b>first</b> app ever!')
    QtGui.QToolTip.setFont(QtGui.QFont('Helvetica', 12))

    txtFirstName = QtGui.?
    txtLastName = QtGui.?

    btnQuit = QtGui.QPushButton('Exit Application', self)
    btnQuit.setGeometry(340, 300, 150, 35)

    self.connect(btnQuit, QtCore.SIGNAL('clicked()'),
                QtGui.qApp, QtCore.SLOT('quit()'))

app = QtGui.QApplication(sys.argv)
mainForm = myWindow()
mainForm.show()
sys.exit(app.exec_())

Solution 1:

Since such simple code is a common request, I decided to hack something basic together, here you go:

from PyQt4.QtCore import *
from PyQt4.QtGui import *

class AppForm(QMainWindow):
def init(self, parent=None):
QMainWindow.init(self, parent)
self.create_main_frame()

def create_main_frame(self):        
    page = QWidget()        

    self.button = QPushButton('joy', page)
    self.edit1 = QLineEdit()
    self.edit2 = QLineEdit()

    vbox1 = QVBoxLayout()
    vbox1.addWidget(self.edit1)
    vbox1.addWidget(self.edit2)
    vbox1.addWidget(self.button)
    page.setLayout(vbox1)
    self.setCentralWidget(page)

    self.connect(self.button, SIGNAL("clicked()"), self.clicked)

def clicked(self):
    QMessageBox.about(self, "My message box", "Text1 = %s, Text2 = %s" % (
        self.edit1.text(), self.edit2.text()))

if name == "main":
import sys
app = QApplication(sys.argv)
form = AppForm()
form.show()
app.exec_()
Write something into the line edits (text boxes), click the button. Profit! :-)

Note: it can be done with less code, but this is a good PyQt coding practice - create a widget to serve as a central widget of a window, populate it with a layout, etc.

Solution 2:

PyQt comes with examples when you install it. These examples contain a lot of very useful code and you can learn from them, as well as take whole code chunks and use them.

Check out, for example, the "Address book" example which pops message boxes along other things (search its sources for "messagebox").

Solution 3:

enter image description here

from PyQt4 import QtGui, QtCore

class Window( QtGui.QWidget ):

def __init__( self ):
    QtGui.QWidget.__init__( self )

    msgBox = QtGui.QMessageBox( self )
    msgBox.setIcon( QtGui.QMessageBox.Information )
    msgBox.setText( "Do not stare into laser with remaining eye" )

    msgBox.setInformativeText( "Do you really want to disable safety enforcement?" )
    msgBox.addButton( QtGui.QMessageBox.Yes )
    msgBox.addButton( QtGui.QMessageBox.No )

    msgBox.setDefaultButton( QtGui.QMessageBox.No ) 
    ret = msgBox.exec_()

    if ret == QtGui.QMessageBox.Yes:
        print( "Yes" )
        return
    else:
        print( "No" )
        return

if name == 'main':

import sys
app = QtGui.QApplication( sys.argv )
window = Window()
# window.show()
sys.exit( app.exec_() )

Source:

https://pythonhowto.org/howto/22358/python---How-to-display-a-message-box-on-PyQT4?
pythonhowto

sql — Add primary key to PostgreSQL table only if it does not exist

ZtoloGame — Mon, 15 Aug 2022 10:49:38 +0000

Question:

I have simple table creating script in Postgres 9.1. I need it to create the table with 2-attributes PK only if it does not exist.

CREATE TABLE IF NOT EXISTS "mail_app_recipients"
(
"id_draft" Integer NOT NULL,
"id_person" Integer NOT NULL
) WITH (OIDS=FALSE); -- this is OK
ALTER TABLE "mail_app_recipients" ADD PRIMARY KEY IF NOT EXISTS ("id_draft","id_person");
-- this is problem since "IF NOT EXISTS" is not allowed.
Any solution how to solve this problem? Thanks in advance.

Solution 1:

You could do something like the following, however it is better to include it in the create table as a_horse_with_no_name suggests.

if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'table_name' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE table_name
ADD PRIMARY KEY (id);
end if;
Solution 2:

Why not include the PK definition inside the CREATE TABLE:

CREATE TABLE IF NOT EXISTS mail_app_recipients
(
id_draft Integer NOT NULL,
id_person Integer NOT NULL,
constraint pk_mail_app_recipients primary key (id_draft, id_person)
)
Solution 3:

You can try to DROP it before creating it (DROP has the IF EXISTS clause):

ALTER TABLE mail_app_recipients DROP CONSTRAINT IF EXISTS mail_app_recipients_pkey;
ALTER TABLE mail_app_recipients ADD CONSTRAINT mail_app_recipients_pkey PRIMARY KEY ("id_draft","id_person");
Note that this require that you give a name to the primary key constraint — in this example mail_app_recipients_pkey.

sql — Postgresql: how to create table only if it does not already exist?
Question:

In Postgresql, how can I do a condition to create a table only if it does not already exist?

Code example appreciated.

Solution 1:

I’m not sure when it was added, but for the sake of completeness I’d like to point out that in version 9.1 (maybe before) IF NOT EXISTS can be used. IF NOT EXISTS will only create the table if it doesn't exist already.

Example:

CREATE TABLE IF NOT EXISTS users.vip
(
id integer
)
This will create a table named vip in the schema users if the table doesn't exist.

Source

Solution 2:

create or replace function update_the_db() returns void as
$$
begin
if not exists(select * from information_schema.tables
where
table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA
and table_name = 'your_table_name_here') then
create table your_table_name_here
(
the_id int not null,
name text
);
end if;
end;
$$
language 'plpgsql';
select update_the_db();
drop function update_the_db();
Solution 3:

Just create the table and don’t worry about whether it exists. If it doesn’t exist it will be created; if it does exist the table won’t be modified. You can always check the return value of your SQL query to see whether the table existed or not when you executed the create statement.

sql — Postgresql tables exists, but getting “relation does not exist” when querying
Question:

I have a postgresql db with a number of tables. If I query:

SELECT column_name
FROM information_schema.columns
WHERE table_name="my_table";
I will get a list of the columns returned properly.

However, when I query:

SELECT *
FROM "my_table";
I get the error:

(ProgrammingError) relation "my_table" does not exist
'SELECT *\n FROM "my_table"\n' {}
Any thoughts on why I can get the columns, but can’t query the table? Goal is to be able to query the table.

Solution 1:

You have to include the schema if isnt a public one

SELECT *
FROM ."my_table"
Or you can change your default schema

SHOW search_path;
SET search_path TO my_schema;
Check your table schema here

SELECT *
FROM information_schema.columns

For example if a table is on the default schema public both this will works ok

SELECT * FROM parroquias_region
SELECT * FROM public.parroquias_region
But sectors need specify the schema

SELECT * FROM map_update.sectores_point
Solution 2:

I had to include double quotes with the table name.

Double quotes:

db=> select ID from COMMONDATA_NWCG_AGENCIES limit 1;
ERROR: relation "commondata_nwcg_agencies" does not exist
LINE 1: select ID from COMMONDATA_NWCG_AGENCIES limit 1;
^
db=> select ID from "COMMONDATA_NWCG_AGENCIES" limit 1;
ERROR: column "id" does not exist
LINE 1: select ID from "COMMONDATA_NWCG_AGENCIES" limit 1;
^
db=> select "ID" from "COMMONDATA_NWCG_AGENCIES" limit 1;

ID

1
(1 row)
This is postgres 11. The CREATE TABLE statements from this dump had double quotes as well:

DROP TABLE IF EXISTS "COMMONDATA_NWCG_AGENCIES";
CREATE TABLE "COMMONDATA_NWCG_AGENCIES" (
...
sql — drop primary key constraint in postgresql by knowing schema and table name only
Question:

As far I know the only way of dropping primary key in postgresql is:

ALTER TABLE schema.tableName DROP CONSTRAINT constraint_name;
the constraint name by default is tableName_pkey. However sometimes if table is already renamed I can’t get the original table name to construct right constraint name.

For example, for a table created as A then renamed to B the constraint remains A_pkey but I only have the table name B.

Do you know right way to drop the pkey constraint by knowing only the schema name and table name ?

I am writing program for doing this so I need to use only SQL queries. Solutions like “open pgAdmin and see the constraint name” will not work.

Solution 1:

You can use information from the catalog tables like so:

Create a table with id as the primary key

create table test1 (id int primary key, name text);
Create the SQL to drop the key

select concat('alter table public.test1 drop constraint ', constraint_name) as my_query
from information_schema.table_constraints
where table_schema = 'public'
and table_name = 'test1'
and constraint_type = 'PRIMARY KEY';
The result will be:

alter table public.test1 drop constraint test1_pkey
You can create a stored function to extract this query and then execute it.

Solution 2:

Then type:

\d
for example:

If you want to do this programmatically and you are using Java or another language that uses the JDBC interface, you can use the class DatabaseMetaData, method getPrimaryKeys.

Otherwise, the other answer, selecting from the system catalogs, is the way to go.

sql — Adding column with primary key in existing table
Question:

I am trying to add primary key to newly added column in existing table name Product_Details.

New Column added: Product_Detail_ID (int and not null)

I am trying add primary key to Product_Detail_ID (please note: there are no other primary or foreign key assigned to this table)

I am trying with this query but getting error.

ALTER TABLE Product_Details
ADD CONSTRAINT pk_Product_Detils_Product_Detail_ID PRIMARY KEY(Product_Detail_ID)
GO
Error:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Product_Details' and the index name 'pk_Product_Detils'. The duplicate key value is (0).

Am I missing something here? I am using SQL Server 2008 R2. I would appreciate any help.

Solution 1:

If you want SQL Server to automatically provide values for the new column, make it an identity.

ALTER TABLE Product_Details DROP COLUMN Product_Detail_ID
GO
ALTER TABLE Product_Details ADD Product_Detail_ID int identity(1,1) not null
GO
ALTER TABLE Product_Details
add CONSTRAINT pk_Product_Detils_Product_Detail_ID primary key(Product_Detail_ID)
GO
Solution 2:

In mysql, I was able to achieve with following query

ALTER TABLE table_name ADD new_column int NOT NULL AUTO_INCREMENT primary key

Solution 3:

You are getting the error because you have existing data that does not fullfill the constraint.

There are 2 ways to fix it:

clean up the existing data before adding the constraint
add the constraint with the “WITH NOCHECK” option, this will stop sql server checking existing data, only new data will be checked

python — How do I remove a trailing newline?

ZtoloGame — Mon, 15 Aug 2022 10:37:18 +0000

Question:

How do I remove the last character of a string if it is a newline?

"abc\n" --> "abc"
Solution 1:

Try the method rstrip() (see doc Python 2 and Python 3)

'test string\n'.rstrip()
'test string'
Python’s rstrip() method strips all kinds of trailing whitespace by default, not just one newline as Perl does with chomp.

'test string \n \r\n\n\r \n\n'.rstrip()
'test string'
To strip only newlines:

'test string \n \r\n\n\r \n\n'.rstrip('\n')
'test string \n \r\n\n\r '
In addition to rstrip(), there are also the methods strip() and lstrip(). Here is an example with the three of them:

s = " \n\r\n \n abc def \n\r\n \n "
s.strip()
'abc def'
s.lstrip()
'abc def \n\r\n \n '
s.rstrip()
' \n\r\n \n abc def'
Solution 2:

And I would say the “pythonic” way to get lines without trailing newline characters is splitlines().

text = "line 1\nline 2\r\nline 3\nline 4"
text.splitlines()
['line 1', 'line 2', 'line 3', 'line 4']
Solution 3:

The canonical way to strip end-of-line (EOL) characters is to use the string rstrip() method removing any trailing \r or \n. Here are examples for Mac, Windows, and Unix EOL characters.

'Mac EOL\r'.rstrip('\r\n')
'Mac EOL'
'Windows EOL\r\n'.rstrip('\r\n')
'Windows EOL'
'Unix EOL\n'.rstrip('\r\n')
'Unix EOL'
Using ‘\r\n’ as the parameter to rstrip means that it will strip out any trailing combination of ‘\r’ or ‘\n’. That’s why it works in all three cases above.

This nuance matters in rare cases. For example, I once had to process a text file which contained an HL7 message. The HL7 standard requires a trailing ‘\r’ as its EOL character. The Windows machine on which I was using this message had appended its own ‘\r\n’ EOL character. Therefore, the end of each line looked like ‘\r\r\n’. Using rstrip(‘\r\n’) would have taken off the entire ‘\r\r\n’ which is not what I wanted. In that case, I simply sliced off the last two characters instead.

Note that unlike Perl’s chomp function, this will strip all specified characters at the end of the string, not just one:

"Hello\n\n\n".rstrip("\n")
"Hello"
thanks to : pythonhowto

How can I disable landscape mode in Android?

ZtoloGame — Fri, 12 Aug 2022 10:31:38 +0000

Question - androidandrey:
How can I disable landscape mode for some of the views in my Android app?

Solution 1:

Add android:screenOrientation="portrait" to the activity in the AndroidManifest.xml. For example:

android:label="@string/app_name"
android:screenOrientation="portrait" />
Since this has become a super-popular answer, I feel very guilty as forcing portrait is rarely the right solution to the problems it's frequently applied to.
The major caveats with forced portrait:

This does not absolve you of having to think about activity lifecycle events or properly saving/restoring state. There are plenty of things besides app rotation that can trigger an activity destruction/recreation, including unavoidable things like multitasking. There are no shortcuts; learn to use bundles and retainInstance fragments.
Keep in mind that unlike the fairly uniform iPhone experience, there are some devices where portrait is not the clearly popular orientation. When users are on devices with hardware keyboards or game pads a la the Nvidia Shield, on Chromebooks, on foldables, or on Samsung DeX, forcing portrait can make your app experience either limiting or a giant usability hassle. If your app doesn't have a strong UX argument that would lead to a negative experience for supporting other orientations, you should probably not force landscape. I'm talking about things like "this is a cash register app for one specific model of tablet always used in a fixed hardware dock."
So most apps should just let the phone sensors, software, and physical configuration make their own decision about how the user wants to interact with your app. A few cases you may still want to think about, though, if you're not happy with the default behavior of sensor orientation in your use case:

If your main concern is accidental orientation changes mid-activity that you think the device's sensors and software won't cope with well (for example, in a tilt-based game) consider supporting landscape and portrait, but using nosensor for the orientation. This forces landscape on most tablets and portrait on most phones, but I still wouldn't recommend this for most "normal" apps (some users just like to type in the landscape softkeyboard on their phones, and many tablet users read in portrait - and you should let them).
If you still need to force portrait for some reason, sensorPortrait may be better than portrait for Android 2.3 (Gingerbread) and later; this allows for upside-down portrait, which is quite common in tablet usage.
Solution 2:

I was not aware of the AndroidManifest.xml file switch until reading this post, so in my apps I have used this instead:

setRequestedOrientation(ActivityInfo.SCREEN_ORIENTATION_PORTRAIT); // Fixed portrait orientation
Solution 3:

Add android:screenOrientation="portrait" in your manifest file where you declare your activity. Like this:

android:name=".yourActivity"
....
android:screenOrientation="portrait" />
If you want to do it using Java code, try:

setRequestedOrientation (ActivityInfo.SCREEN_ORIENTATION_PORTRAIT);
before you call setContentView method for your activity in onCreate().

How to display Oracle schema size with SQL query?

ZtoloGame — Thu, 11 Aug 2022 12:06:04 +0000

Question:

I have a Oracle schema with 70+ tables. I want to create simple page which can display the HDD space occupied by the tables. How I can get this value with SQL query?

P.S And how I can get the Oracle architecture version?

Solution 1:

You probably want

SELECT sum(bytes)
FROM dba_segments
WHERE owner = <>
If you are logged in as the schema owner, you can also

SELECT SUM(bytes)
FROM user_segments
That will give you the space allocated to the objects owned by the user in whatever tablespaces they are in. There may be empty space allocated to the tables that is counted as allocated by these queries.

Solution 2:

If you just want to calculate the schema size without tablespace free space and indexes :

select
sum(bytes)/1024/1024 as size_in_mega,
segment_type
from
dba_segments
where
owner=''
group by
segment_type;
For all schemas

select
sum(bytes)/1024/1024 as size_in_mega, owner
from
dba_segments
group by
owner;
Solution 3:

select T.TABLE_NAME, T.TABLESPACE_NAME, t.avg_row_len*t.num_rows from dba_tables t
order by T.TABLE_NAME asc
See e.g. sqlerrors for more options

Disable Rails SQL logging in console

ZtoloGame — Sun, 07 Aug 2022 17:36:38 +0000

To turn it off:

old_logger = ActiveRecord::Base.logger
ActiveRecord::Base.logger = nil
To turn it back on:

ActiveRecord::Base.logger = old_logger
Solution 2:

Here's a variation I consider somewhat cleaner, that still allows potential other logging from AR. In config/environments/development.rb :

config.after_initialize do
ActiveRecord::Base.logger = Rails.logger.clone
ActiveRecord::Base.logger.level = Logger::INFO
end
Solution 3:

This might not be a suitable solution for the console, but Rails has a method for this problem: Logger#silence

ActiveRecord::Base.logger.silence do
# the stuff you want to be silenced
end

DEV Community: ZtoloGame

sql - Mysql Calculate rank of teams from different rows

| USER_ID | TEAM_ID | MNDATE | MXDATE | MNCLUES_FOUND | MXCLUES_FOUND | PCT |

| TEAM_ID | TEAMPCT |

sql — Select Records multiple times from table

python - How to Count bugs in an image?

! /usr/bin/python

pylab.imshow(dna)

pylab.show()

pylab.show()

pylab.show()

git — Fatal: Not possible to fast-forward, aborting

android - Get root view from current activity

mysql — How to get the count of each distinct value in a column?

id | category

| book_id | author | genre | price | publication_date |

| order_id | customer_id | book_id |

| book_id | author | genre | price | publication_date | number_of_orders |

python - How to display a message box on PyQT4?

sql — Add primary key to PostgreSQL table only if it does not exist

ID

python — How do I remove a trailing newline?

How can I disable landscape mode in Android?

How to display Oracle schema size with SQL query?

Disable Rails SQL logging in console