Masi Masi - 2 months ago 8
Python Question

Select/Fetch signed integer in PostgreSQL/Python

I would like to Select and Fetch integer from the column measurement_id of the table measurements, since otherwise, I have somehow convert one-item-size tuple to signed integer by some postprocess of Python.
I know this Selecting/casting output as integer in SQL in MySQL where my pseudocode for PostgreSQL 9.4

CREATE TABLE measurements (
measurement_id SERIAL PRIMARY KEY NOT NULL,
measurement_size_in_bytes INTEGER NOT NULL,
time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- sample data
INSERT INTO measurements (measurement_size_in_bytes) VALUES (77777), (5065), (888);

SELECT CAST(measurement_id AS SIGNED) FROM measurements ORDER BY time desc limit 1;


where casting is not working, not even by
measurement_id::int
.
Probably,
format
is the right tool here.
Doing
print "measurement_id ", _measurement_id;
gives tuple (
(21,)
), while it should just give
21
.

Initiating and Running in Python



sudo -u postgres psql detector -f 12.7.2015_creates.sql;
DROP TABLE
CREATE TABLE
$ sudo -u postgres psql detector -c "INSERT INTO measurements (measurement_size_in_bytes) VALUES (77777), (5065), (888);"
INSERT 0 3
$ sudo -u postgres psql detector -c "INSERT INTO measurements (measurement_size_in_bytes) VALUES (77777), (5065), (888);"
INSERT 0 3
$ sudo -u postgres psql detector -c "SELECT measurement_id FROM measurements ORDER BY time desc limit 1";
measurement_id
----------------
7
(1 row)


but doing the same in Python where I need to use the data

import sys
import os
import struct
import psycopg2
conn_string = "dbname=detector user=postgres password=1234 host=localhost port=5432"
print "Connecting to database\n ->%s" % (conn_string)
conn = psycopg2.connect(conn_string)
cursor = connection.cursor()
cursor.execute(
'SELECT measurement_id FROM measurements ORDER BY time desc limit 1;'
);
_measurement_id = cursor.fetchone();
print "measurement_id ", _measurement_id, " javist!";


returning

Connecting to database
->dbname=detector user=postgres password=1234 host=localhost port=5432
measurement_id (11,) javist!


I would like to have from print
measurement_id 11 javist!
.

How can you select/fetch signed int from tuple in PostgreSQL/Python?

OS: Debian 8.5

Hardware: Lenovo

Answer

fetchone returns a tuple. Just get its first element:

_measurement_id = cursor.fetchone()[0];
Comments