How to insert database mysql. Solve?

user_12440478 0
Viewed 84

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?

pymysql biopython mysql sql
2 Answers

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.

user_871890

change :

for item in SeqIO.parse('seqteste.txt', 'fasta'):
    dados = print('>{}\t{}'.format(str(item.description).replace('|', '\t'), item.seq), )

into

import re

cnt = 0
for item in SeqIO.parse('seqteste.txt', 'fasta'):
    cnt += 1
    s=(str((item.description).strip('')))
    pattern_id = "(.*?)\|Organism"
    pattern_host = "Host:(.*.)"
    pattern_org = "Organism:(.*?)\|Strain"
    ids= re.search(pattern_id, s).group(1)
    host = re.search(pattern_host, s).group(1)
    org = re.search(pattern_org, s).group(1)
    dados = [str(cnt), ids ,host , org   ,str(item.seq)]

and :

 cursor.execute(sql, (dados, dados, dados, dados, dados))

with:

cursor.execute(sql, (dados[0] , dados[1] ,dados[2] ,dados[3] ,dados[4]))

let me know how it behaves, I dont have your database and can't test my code

user_98770650
Related