How to insert database mysql. Solve?

How can I insert the file data into the table mysql?

code:

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

try:
    conexao = MySQLdb.connect(host="localhost",user="root",passwd="xxx",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(1, %s, %s, %s,%s, %s)"
            cursor.execute(sql, (dados, dados, dados, dados, dados,))
            conexao.commit()

Erros:

pymysql.err.OperationalError: (1136, "Column count doesn't match value count at row 1")

our:

sql =  "INSERT INTO `tabelateste` (`id`, `id_name`, `host`, `organism`, `seq`) VALUES(%s, %s, %s, %s,%s, %s)"
            cursor.execute(sql, (dados, dados, dados, dados, dados))
            conexao.commit()

error:

    line 125, in mogrify
        query = query % self._escape_args(args, conn)
    TypeError: not enough arguments for format string

How to solve this error and be able to insert the data in the mysqldb table?

myseq:

>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

expected exit Output:

mysqldb

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

Solve?

Answer

You must carefully match the columns, the placeholders, and the values so that there is an identical number of each:

sql =  "INSERT INTO `tabelateste` (`id`, `id_name`, `host`, `organism`, `seq`) VALUES(%s, %s, %s, %s, %s)"
            cursor.execute(sql, (dados, dados, dados, dados, dados))
            conexao.commit()

Note how there’s 5 columns specified, 5 placeholders, and 5 binds in the tuple now. You had an extra %s.

Conceptually what you want to do is:

(column_name, ...) <-- Columns specified
     |
     v
(   %s      , ... ) <-- Placeholders specified
     |
     v
(bind_value , ... ) <-- Binding on execute()

Note that these must correlate 1:1:1 exactly. Any mismatches will result in errors like you’ve seen.

Advertisement

Answer