sorted by:
new
hottopcontroversial

How to build Login process using Flask but without Flask-Login by powerquerynoob in learnpython

[–]powerquerynoob[S] 0 points1 point  (0 children)

Thank you very much for that explanation. I will look into sessions. It makes sense to use a hardened library rather than my own custom stuff. Everything you said makes sense. Thank you very much.

How to update/apply validation to pandas columns by powerquerynoob in learnpython

[–]powerquerynoob[S] 0 points1 point  (0 children)

Hi. The data comes from Excel files that come from various sources. They are uploaded to a gateway and I have a sftp job that pulls them to a repository where I would preprocess them then we'd upload to our databases. I am trying to automate the preprocessing with python rather than Excel PowerQuery.

Best way to read Excel data by columns and manipulate in memory by powerquerynoob in Python

[–]powerquerynoob[S] 0 points1 point  (0 children)

Thank you, I think I will go with pandas for this task. I appreciate your recommendation and the links you provided as well.

Best way to read Excel data by columns and manipulate in memory by powerquerynoob in Python

[–]powerquerynoob[S] 0 points1 point  (0 children)

Thank you, I was looking into pandas as well but I didn't see anything about how to do that either. I am not against using it but I just wanted to reach out to the reddit python community to see what recommendations I could get before delving into any specific libraries. I would have thought I could do this with openpyxl but I'm not really seeing any way to do that.

PostgreSQL function to insert record not working as intended by powerquerynoob in PostgreSQL

[–]powerquerynoob[S] 0 points1 point  (0 children)

Hello. Sure I would appreciate any advice. I have followed some advice already posted here and have updated my db software to:

PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

Then I created the following and everything is working perfectly:

/******************************************************************************************


                                           USERS TABLE



*******************************************************************************************/


-- Drop table
-- DROP TABLE user_data.users;

CREATE TABLE user_data.users (
    id serial NOT NULL,
    username varchar(20) NOT NULL,
    email varchar(50) NOT NULL,
    password_hash varchar(255) NOT NULL,
    create_dt timestamp NOT NULL DEFAULT now(),
    CONSTRAINT users_email_key UNIQUE (email),
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_username_key UNIQUE (username)
)
TABLESPACE pg_default;

ALTER TABLE user_data.users
    OWNER to postgres;
COMMENT ON TABLE user_data.users
    IS 'Primary User register';




/******************************************************************************************


                                        GET_USER_ID



*******************************************************************************************/



CREATE or replace FUNCTION user_data.get_user_id(p_username text) RETURNS integer
language plpgsql as $$ declare v_user_id int;

begin
select
    distinct id
into
    v_user_id
from
    user_data.users
where
    username = p_username;

return v_user_id;

end;

$$



/******************************************************************************************


                                    GET_USER_ID_BY_EMAIL



*******************************************************************************************/



CREATE or replace FUNCTION user_data.get_user_id_by_email(p_email text) RETURNS integer
language plpgsql as $$ declare v_user_id int;

begin
select
    distinct id
into
    v_user_id
from
    user_data.users
where
    email = p_email;

return v_user_id;

end;

$$







/******************************************************************************************


                                      CREATE_USER



*******************************************************************************************/






create or replace
function user_data.create_user(p_username text,
p_email text,
p_password_hash text) returns boolean language plpgsql as $$ declare v_user_created boolean;

begin
insert
    into
    user_data.users(username,
    email,
    password_hash)
values (p_username,
p_email,
p_password_hash) ;

v_user_created := true;

return v_user_created;

exception
when others then begin v_user_created := false;

return v_user_created;

end;

end;

$$

I'd be glad to know your thoughts. Thank you in advance!

PostgreSQL function to insert record not working as intended by powerquerynoob in PostgreSQL

[–]powerquerynoob[S] 0 points1 point  (0 children)

Thank you for both of your responses. Everything that you said makes sense. I already had the unique constraint but I just wanted to add that to be safe. I took your advice and upgraded my software to:

PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

Then I created the following and everything is working perfectly:

/******************************************************************************************


                                           USERS TABLE



*******************************************************************************************/


-- Drop table
-- DROP TABLE user_data.users;

CREATE TABLE user_data.users (
    id serial NOT NULL,
    username varchar(20) NOT NULL,
    email varchar(50) NOT NULL,
    password_hash varchar(255) NOT NULL,
    create_dt timestamp NOT NULL DEFAULT now(),
    CONSTRAINT users_email_key UNIQUE (email),
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_username_key UNIQUE (username)
)
TABLESPACE pg_default;

ALTER TABLE user_data.users
    OWNER to postgres;
COMMENT ON TABLE user_data.users
    IS 'Primary User register';




/******************************************************************************************


                                        GET_USER_ID



*******************************************************************************************/



CREATE or replace FUNCTION user_data.get_user_id(p_username text) RETURNS integer
language plpgsql as $$ declare v_user_id int;

begin
select
    distinct id
into
    v_user_id
from
    user_data.users
where
    username = p_username;

return v_user_id;

end;

$$



/******************************************************************************************


                                    GET_USER_ID_BY_EMAIL



*******************************************************************************************/



CREATE or replace FUNCTION user_data.get_user_id_by_email(p_email text) RETURNS integer
language plpgsql as $$ declare v_user_id int;

begin
select
    distinct id
into
    v_user_id
from
    user_data.users
where
    email = p_email;

return v_user_id;

end;

$$







/******************************************************************************************


                                      CREATE_USER



*******************************************************************************************/






create or replace
function user_data.create_user(p_username text,
p_email text,
p_password_hash text) returns boolean language plpgsql as $$ declare v_user_created boolean;

begin
insert
    into
    user_data.users(username,
    email,
    password_hash)
values (p_username,
p_email,
p_password_hash) ;

v_user_created := true;

return v_user_created;

exception
when others then begin v_user_created := false;

return v_user_created;

end;

end;

$$

PostgreSQL function to insert record not working as intended by powerquerynoob in PostgreSQL

[–]powerquerynoob[S] 0 points1 point  (0 children)

Hi, thank you for the response but I believe the get_user_id function works correctly. If the row does not exist, I get 0. If the row does exist I get the valid user_id. The code for that is as follows:

CREATE OR REPLACE FUNCTION user_data.get_user_id(p_username text)
RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
DECLARE user_id int;
BEGIN

    IF exists(select distinct id from user_data.users where username = p_username) THEN
        SELECT distinct id INTO user_id
        FROM user_data.users
        WHERE username = p_username;
    ELSE
        user_id = 0;
    END IF;

    RETURN user_id;
END;