python - Error loading data to postgres with if statement

问题描述

I am trying to save in a database a serie of tweets from a local newspaper on a postgres database using python. My SQL query worked perfectly from PgAdmin when I tried to load data manually. My problem comes when I try to automate the process with Python. Can anyone review my code, I tried everything I could think of.

def obtiene_tweets(usuario, auth = auth_titter(), cantidad = 100):

fuente = obtiene_id(usuario)


##Conección a twitter

##API
api = tweepy.API(auth)

## Aertura de conección a base
conn = psycopg2.connect(dbname = config.DB_NAME, 
                            user = config.DB_USER,
                            password = config.DB_PASSWORD,
                            host = config.DB_HOST)
##Tweets 
### Este loop accede a los tweets del usuario y obtiene la información de interés para que despúes
### Se la pongamos a la base de datos
cur = conn.cursor()
for tweet in tweepy.Cursor(api.user_timeline, screen_name = fuente.loc['usuario',].values[0], tweet_mode= 'extended').items(cantidad):
    #print(json.dumps(tweet._json, indent=2))
    
    full_text = tweet._json['full_text']
    id = fuente.loc['id',].values[0]
    fecha = tweet._json['created_at']
    try: 
        hashtags = str(tweet._json['entities']['hashtags']).replace('[','')
        hashtags = hashtags.replace(']','')
        if hashtags == '':
            hashtags = 'Null'
    except:
        pass
    try:
     url = tweet._json['entities']['urls'][0]['expanded_url']
    except:
        url = 'Null'
    try:
     likes = tweet._json['favorite_count']
     rts = tweet._json['retweet_count']
    except:
        pass
    cur.execute(sql_statements.CARGA_TWEETS, (full_text.replace("'",'"'),id,full_text.replace("'",'"'), fecha, hashtags, url, likes, rts))
    

cur.close()
conn.close()

Here is the sql query:

CARGA_TWEETS = """
                DO $$
                BEGIN
                IF EXISTS(SELECT tweet FROM tweets WHERE tweet = %s ) THEN

                raise notice 'Se encuentra el tweet';

                ELSE
                insert into tweets(id_usuario, tweet, fecha, hashtags, urls, likes, rts)
                values(%s, %s, %s, %s, %s, %s, %s );
                

                END IF;

                END $$





            """

My error looks like this:

(resumen_noticias) mariano@mariano-HP-Laptop-15-da2xxx:/media/mariano/Nuevo vol/CarpetaProtegida/Provincia/Propuestas/Proyectos/resumen-noticias (main)$ /home/mariano/anaconda3/envs/resumen_noticias/bin/python "/media/mariano/Nuevo vol/CarpetaProtegida/Provincia/Propuestas/Proyectos/resumen-noticias/funciomes.py"
Traceback (most recent call last):
  File "/media/mariano/Nuevo vol/CarpetaProtegida/Provincia/Propuestas/Proyectos/resumen-noticias/funciomes.py", line 121, in <module>
    obtiene_tweets('clarineconomico')
  File "/media/mariano/Nuevo vol/CarpetaProtegida/Provincia/Propuestas/Proyectos/resumen-noticias/funciomes.py", line 111, in obtiene_tweets
    cur.execute(sql_statements.CARGA_TWEETS, (full_text.replace("'",''),id,full_text.replace("'",''), fecha, hashtags, url, likes, rts))
psycopg2.errors.StringDataRightTruncation: value too long for type character varying(200)
CONTEXT:  SQL statement "insert into tweets(id_usuario, tweet, fecha, hashtags, urls, likes, rts)
                    values(9, 'José Mujica: Me he puesto viejo sintiendo de crisis en Argentina y sin embargo, no sé cómo, pero siempre salen adelante url', 'Fri Aug 27 13:26:22 +0000 2021', 'Null', 'extended_url', 7, 1 )"
PL/pgSQL function inline_code_block line 8 at SQL statement

Thank you everyone who takes time to answer.

标签： pythonsqlpostgresql

python - Error loading data to postgres with if statement

问题描述

解决方案

推荐阅读