Insert data with pymysql into a mysql database error. How to resolve?

Question

seqteste.txt or List:

>gb:KX262887|Organism:Zika virus|Strain Name:103451|Segment:null|Subtype:Asian|Host:Human
GTTGTTGATCTGTGTGAATCAGACTGCGACAGTTCGAGTTTGAAGCGAAAGCTAGCAACAGTATCAACAG
GTTTTATTTTGGATTTGGAAACGAGAGTTTCTGGTCATGAAAAACCCAAAAAAGAAATCCGGAGGATTCC

>gb:KX262887|Organism:Zika virus|Strain Name:103451|Segment:null|Subtype:Asian|Host:Human
    GTTGTTGATCTGTGTGAATCAGACTGCGACAGTTCGAGTTTGAAGCGAAAGCTAGCAACAGTATCAACAG
    GTTTTATTTTGGATTTGGAAACGAGAGTTTCTGGTCATGAAAAACCCAAAAAAGAAATCCGGAGGATTCC

>gb:KX262887|Organism:Zika virus|Strain Name:103451|Segment:null|Subtype:Asian|Host:Human
    GTTGTTGATCTGTGTGAATCAGACTGCGACAGTTCGAGTTTGAAGCGAAAGCTAGCAACAGTATCAACAG
    GTTTTATTTTGGATTTGGAAACGAGAGTTTCTGGTCATGAAAAACCCAAAAAAGAAATCCGGAGGATTCC

mysqldb:

id   id_name host  organism  seq

script/ code:

import pymysql.cursors
import pymysql as MySQLdb
import pymysql
from Bio import SeqIO

try:
    conexao = MySQLdb.connect(host="localhost",user="root",passwd="",db="db_teste")
    print("conectado")
    print(conexao)
except:
    print("Não conectado")



for item in SeqIO.parse('seqteste.txt', 'fasta'):
    dados = print('>{}\t{}'.format(str(item.description).replace('|', '\t'), item.seq),)
    with conexao:
         with conexao.cursor() as cursor:
            sql =  "INSERT INTO `tabelateste` (`id`, `id_name`, `host`, `organism`, `seq`) VALUES(3, %s, %s, %s,%s)"
            print(sql)
            cursor.execute(sql,(3,dados,dados,dados,dados))
            conexao.commit()

error:

INSERT INTO `tabelateste` (`id`, `id_name`, `host`, `organism`, `seq`) VALUES(3, %s, %s, %s,%s)
Traceback (most recent call last):
  File "c:\Users\B\Desktop\codeinserir.py", line 21, in <module>
    cursor.execute(sql,(3,dados,dados,dados,dados))
  File "C:\Users\B\AppData\Local\Programs\Python\Python39\lib\site-packages\pymysql\cursors.py", line 146, in execute
    query = self.mogrify(query, args)
  File "C:\Users\B\AppData\Local\Programs\Python\Python39\lib\site-packages\pymysql\cursors.py", line 125, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not all arguments converted during string formatting

code2 :

for linha in alllines:
    seqt = linha.replace("|","\t")
    seqin =  SeqIO.parse(seqt, "fasta")
    for i in seqin:
     print(i.id,i.seq)
     with con:
         with con.cursor() as cursor:
            sql = "INSERT INTO `tabelateste` (`id_name`,`seq`) VALUES (%s, %s)"
            cursor.execute(sql,(i.id , i.seq))
            con.commit()

error:

Traceback (most recent call last):
  File "c:\Users\B\Desktop\codeinserir.py", line 25, in <module>
    DEanalysisfile = open (argumentos.file, 'r')
TypeError: expected str, bytes or os.PathLike object, not NoneType

expected exit:

mysqldb

 id  id_name            host        organism         seq
 1   gb:KX262887        Human       Zika Virus       aatgtgttt

I am trying to insert data from a seqteste.txt file into a mysql database but the result is always null. How to resolve this problem? What am I doing wrong??

Answer 1

Good evening Lara, I believe pymysql is installed so it could be the type there in the database, you can send a string and the bank waiting for a whole, worth checking and send the print here.

Answer 2

I confess I was left with some doubts:

• Why import pymysql with Mysqldb name and you could install Mysqldb directly?

• Why import pymysql twice?

• Why enter the data as in the question: cursor.execute(sql,(3,dados,dados,dados,dados))? Did that ever work?

To enter data in the database, you need to pass key and value, that is, the names of the columns according to the database and the values that will be placed there. How Python would identify which given place if the value is 'dice' ?

Your connection did not return anything, and it ever worked?

Knowing these things is a real curiosity, because it doesn’t seem that you are totally uneducated in what you are doing and I believe that you can share with us what you know.

Follows the code.

import pymysql as MySQLdb
from Bio import SeqIO


def conexao():
    try:
        conn = MySQLdb.connect(
            host="localhost",
            user="root",
            passwd="",
            db="db_teste"
        )
        print("conectado")
        return conn
    except MySQLdb.Error as e:
        print(f'Erro ao conectar no Servidor MySQL: {e}')


def desconectar(conn):
    if conn:
        conn.close()


for item in SeqIO.parse('seqteste.txt', 'fasta'):
    dado = item.description.replace('|', '\n').splitlines()
    resumo = []

    for i in dado:
        d = i.replace(':', '\n').splitlines()
        resumo.append(d[1])

    id_name = resumo[0]
    organism = resumo[1]
    host = resumo[5]
    seq = item.seq
    conn = conexao()
    try:
        cursor = conn.cursor()
        cursor.execute(f"INSERT INTO tabelateste (id_name, host, organism, seq) "
                       f"VALUES('{id_name}', '{host}', '{organism}', '{seq}');"
                       )
        conn.commit()

        print(f"INSERT INTO tabelateste (id_name, host, organism, seq) "
              f"VALUES('{id_name}', '{host}', '{organism}', '{seq}');")
    except MySQLdb.Error as e:
        print(f'Erro ao conectar no Servidor MySQL: {e}')
    
    desconectar(conn)