How to insert sequence fasta data into a mysql database?

Question

[USER]

I would like to insert the data of the fasta sequence in a mysql database.

But my code doesn't work and I don't know what I might be doing wrong

I can insert the data by pasting the data for each line. but when I put the for it doesn't work

[Bastien Hervé]

If you failed to connect in your try you will fall into the except where conexao does not exist.

Try to put a sys.exit(1) after print("Not conect") in your except statement, the script will stop if you are unable to connect.

[USER]

I can make the connection the error is here

line 16, in <module>
        with conexao:
NameError: name 'conexao' is not defined

here:

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(%s, %s, %s, %s,%s)"
            cursor.execute(sql)
            conexao.commit()

[Bastien Hervé]

All right, do you have these prints results in your terminal ?

print("conect")
print(conexao)

[USER]

error:

pymysql.err.IntegrityError: (1048, "Column 'id' cannot be null")

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s, %s, %s,%s)' at line 1")

[Bastien Hervé]

Can you try running this :

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

conexao=""

try:
    conexao = MySQLdb.connect(host="localhost",user="root",passwd="xxx",db="db_teste")
    print("conect")
    print(conexao)
except:
    print("Not conect")

print(conexao)

for item in SeqIO.parse('seqteste.txt', 'fasta'):
    print(item)
    with conexao:
         with conexao.cursor() as cursor:
            sql = "INSERT INTO tabelateste(id,id_name, host,organism, seq) values(%s, %s, %s, %s,%s)"
            cursor.execute(sql)
            conexao.commit()