Modify sql exit with python

3

When doing a query of a certain line of a database (MySQl) with python, the output it offers is of the type:

Db_Host = '****'     
Db_Urs = '****' 
Db_Pass = '****' 
Db_Name = '****' 

def run_query(query=''): 
    datos = [Db_Host, Db_Urs, Db_Pass, Db_Name] 

    conexion = MySQLdb.connect(*datos)  
    cursor = conn.cursor()          
    cursor.execute(query)           

    if query.upper().startswith('SELECT'): 
        data = cursor.fetchall()   
    else: 
        conexion.commit()              
        data = None 

    cursor.close()                  
    connexion.close()                    
    return data


x = run_query(query='SELECT dni FROM empleados limit 1')
print x

With this output:

((' 75849332D ',),) 

I would need to know if that output (probably tuple), can become a string to be able to modify it later, that is, that it looks like a normal string:

75849332D

Thank you very much

    
asked by Cav 18.06.2016 в 10:04
source

2 answers

2

If you know that this query will always return that (tuple within tuple) you can access the internal string using indexing:

inside the function, in the last line, you can do:

return data[0][0]

Or outside the function, when you call it, you can store in x , directly the string doing:

x = run_query(query='SELECT dni FROM empleados limit 1')[0][0]

or, in the print, you can do:

print(x[0][0])

His would be doing it in the function so that the same function could handle possible errors. One possibility could be:

def run_query(query=''): 
    try:
        datos = [Db_Host, Db_Urs, Db_Pass, Db_Name] 

        conexion = MySQLdb.connect(*datos)  
        cursor = conn.cursor()          
        cursor.execute(query)           

        if query.upper().startswith('SELECT'): 
            data = cursor.fetchall()   
        else: 
            conexion.commit()              
            data = None 

        cursor.close()                  
        connexion.close()                    
        return data[0][0]
    except:
        return ("No ha sido posible realizar la conexión o ha habido "
                "un problema con la extracción y/o formateo de los datos")
    
answered by 22.06.2016 / 09:38
source
2

The output that you sample is a monotuple within another monotuple. In short, the chain you are looking for is obtained through a double indirection:

s = x[0][0]

But it is better to visualize for a moment what was the expected result of the query. It must be a sequence of records, each record being a list of the fields it has. When doing the query only for one field, dni , there is a monotuple per record returned, and only one record because you will not have anything else stored in the table.

But I could have returned more than one record or, perhaps, none. It is convenient that your program does not assume a priori how many records, so it is very common to access the results with a loop:

res = run_query(query='SELECT dni FROM empleados limit 1')

for row in res:
    print row[0]

Or also

for (dni,) in res:
    print dni
    
answered by 22.06.2016 в 09:34