在python中连接“sqlite3”时出现问题

Question

我将以下数据库另存为data_db.sql：

CREATE TABLE protein (
    accession   varchar(20) PRIMARY KEY,
    species_id  int,  -- NCBI taxonomic code
    mass        float,
    description varchar(100),
    seq         varchar(5000) NOT NULL,
    FOREIGN KEY (species_id) REFERENCES species (species_id)
);

CREATE TABLE protein_keywords (
    accession varchar(20),
    keyword   varchar(20),
    PRIMARY KEY (accession, keyword),
    FOREIGN KEY (accession) REFERENCES protein(accession)
);

INSERT INTO 'protein' VALUES('7UP2_DROME',7227,76814,'Steroid receptor seven-up, isoform A (Nuclear receptor subfamily 2 group F member 3, isoform A)','MCASPSTAPGFFNPRPQSGAELSAFDIGLSRSMGLGVPPHSAWHEPPASLGGHLHAASAGPGTTTGSVATGGGGTTPSSVASQQSAVIKQDLSCPSLNQAGSGHHPGIKEDLSSSLPSANGGSAGGHHSGSGSGSGSGVNPGHGSDMLPLIKGHGQDMLTSIKGQPTGCGSTTPSSQANSSHSQSSNSGSQIDSKQNIECVVCGDKSSGKHYGQFTCEGCKSFFKRSVRRNLTYSCRGSRNCPIDQHHRNQCQYCRLKKCLKMGMRREAVQRGRVPPTQPGLAGMHGQYQIANGDPMGIAGFNGHSYLSSYISLLLRAEPYPTSRYGQCMQPNNIMGIDNICELAARLLFSAVEWAKNIPFFPELQVTDQVALLRLVWSELFVLNASQCSMPLHVAPLLAAAGLHASPMAADRVVAFMDHIRIFQEQVEKLKALHVDSAEYSCLKAIVLFTTGKLLDILYKDVPALLTKVSALLGKGSTASNDDVLAVVRDHLDELNRQEQESQAQQQAPLHLAAFMNCVAGVEAAVQQAEQAQVPTSSASASVSAPLVPSAGSAFSSCQAKSAGSEMDLLASLYAQAQATPPSSGGGDASGHNNSSGLGASLPTQSQSGSSSRNLTASPLSTSLATAPAPASASAPAPVPTSSVAQVPVPAPVPVTSSASSSSLGGGAYQTPSAAAAAAAMFHYQTPPRAAFGSAFDMFHHSTPFGVGVGHAHALAHSSGSGSASFGSPSYRYSPYSLAGSRWQL');

INSERT INTO 'protein' VALUES('E75BB_DROME',7227,151292,'Ecdysone-induced protein 75B, isoform B (E75-C) (Nuclear receptor subfamily 1 group D member 3, isoform B)','MEAVQAAAAATSSGGSSGSVPGSGSGSASKLIKTEPIDFEMLHLEENERQQDIEREPSSSNSNSNSNSLTPQRYTHVQVQTVPPRQPTGLTTPGGTQKVILTPRVEYVQQRATSSTGGGMKHVYSQQQGTAASRSAPPETTALLTTTSGTPQIIITRTLPSNQHLSRRHSASPSALHHYQQQQPQRQQSPPPLHHQQQQQQQHVRVIRDGRLYDEATVVVAARRHSVSPPPLHHHSRSAPVSPVIARRGGAAAYMDQQYQQRQTPPLAPPPPPPPPPPPPPPPQQQQQQYISTGVPPPTAAARKFVVSTSTRHVNVIASNHFQQQQQQHQAQQHQQQHQQHQQHQQHVIASVSSSSSSSAIGSGGSSSSHIFRTPVVSSSSSSNMHHQQQQQQQQSSLGNSVMRPPPPPPPPKVKHASSSSSGNSSSSNTNNSSSSSNGEEPSSSIPDLEFDGTTVLCRVCGDKASGFHYGVHSCEGCKGFFRRSIQQKIQYRPCTKNQQCSILRINRNRCQYCRLKKCIAVGMSRDAVRFGRVPKREKARILAAMQQSTQNRGQQRALATELDDQPRLLAAVLRAHLETCEFTKEKVSAMRQRARDCPSYSMPTLLACPLNPAPELQSEQEFSQRFAHVIRGVIDFAGMIPGFQLLTQDDKFTLLKAGLFDALFVRLICMFDSSINSIICLNGQVMRRDAIQNGANARFLVDSTFNFAERMNSMNLTDAEIGLFCAIVLITPDRPGLRNLELIEKMYSRLKGCLQYIVAQNRPDQPEFLAKLLETMPDLRTLSTLHTEKLVVFRTEHKELLRQQMWSMEDGNNSDGQQNKSPSGSWADAMDVEAAKSPLGSVSSTESADLDYGSPSSSQPQGVSLPSPPQQQPSALASSAPLLAATLSGGCPLRNRANSGSSGDSGAAEMDIVGSHAHLTQNGLTITPIVRHQQQQQQQQQIGILNNAHSRNLNGGHAMCQQQQQHPQLHHHLTAGAARYRKLDSPTDSGIESGNEKNECKAVSSGGSSSCSSPRSSVDDALDCSDAAANHNQVVQHPQLSVVSVSPVRSPQPSTSSHLKRQIVEDMPVLKRVLQAPPLYDTNSLMDEAYKPHKKFRALRHREFETAEADASSSTSGSNSLSAGSPRQSPVPNSVATPPPSAASAAAGNPAQSQLHMHLTRSSPKASMASSHSVLAKSLMAEPRMTPEQMKRSDIIQNYLKRENSTAASSTTNGVGNRSPSSSSTPPPSAVQNQQRWGSSSVITTTCQQRQQSVSPHSNGSSSSSSSSSSSSSSSSSTSSNCSSSSASSCQYFQSPHSTSNGTSAPASSSSGSNSATPLLELQVDIADSAQPLNLSKKSPTPPPSKLHALVAAANAVQRYPTLSADVTVTASNGGPPSAAASPAPSSSPPASVGSPNPGLSAAVHKVMLEA');

INSERT INTO 'protein' VALUES('COT2_MOUSE',10090,45571,'COUP transcription factor 2 (COUP-TF2) (Apolipoprotein AI regulatory protein 1) (ARP-1) (COUP transcription factor II) (COUP-TF II) (Nuclear receptor subfamily 2 group F member 2)','MAMVVSTWRDPQDEVPGSQGSQASQAPPVPGPPPGAPHTPQTPGQGGPASTPAQTAAGGQGGPGGPGSDKQQQQQHIECVVCGDKSSGKHYGQFTCEGCKSFFKRSVRRNLSYTCRANRNCPIDQHHRNQCQYCRLKKCLKVGMRREAVQRGRMPPTQPTHGQFALTNGDPLNCHSYLSGYISLLLRAEPYPTSRFGSQCMQPNNIMGIENICELAARMLFSAVEWARNIPFFPDLQITDQVALLRLTWSELFVLNAAQCSMPLHVAPLLAAAGLHASPMSADRVVAFMDHIRIFQEQVEKLKALHVDSAEYSCLKAIVLFTSDACGLSDVAHVESLQEKSQCALEEYVRSQYPNQPTRFGKLLLRLPSLRTVSSSVIEQLFFVRLVGKTPIETLIRDMLLSGSSFNWPYMAIQ');

然后，我尝试用python连接到该数据库，如下所示：

import sqlite3

db_file = 'data_db.sql'


def get_connection(db_file):    
    connection = sqlite3.connect(db_file)
    cursor = connection.cursor()
    return connection, cursor

def get_protein(cursor):
    cursor.execute('protein' VALUES('7UP2_DROME',722))
    return cursor.fetchall()

def main(db_file):
    con, cur = get_connection(db_file)
    protein = get_protein(cur)
    for row in protein:
        print(row)
    con.close()

但是，我收到以下错误消息：

Traceback (most recent call last):
  File "<pyshell#5>", line 1, in <module>
    main('data_db.sql')
  File "C:\Users\John\AppData\Local\Programs\Python\Python38\sqlite-tools-win32-x86-3340000\sqlite-tools-win32-x86-3340000\file.py", line 16, in main
    con, cur = get_connection(db_file)
  File "C:\Users\John\AppData\Local\Programs\Python\Python38\sqlite-tools-win32-x86-3340000\sqlite-tools-win32-x86-3340000\file.py", line 7, in get_connection
    connection = sqlite3.connect(db_file)
AttributeError: module 'sqlite3' has no attribute 'connect'

我怎样才能解决这个问题？我需要连接到数据库以便进行计算

Answer 1

一,/ 1/data_db.sql文件（已更新）

CREATE TABLE protein (
    accession   varchar(20) PRIMARY KEY,
    species_id  int,    NCBI taxonomic code
    mass        float,
    description varchar(100),
    seq         varchar(5000) NOT NULL,
    FOREIGN KEY (species_id) REFERENCES species (species_id)
);

CREATE TABLE protein_keywords (
    accession varchar(20),
    keyword   varchar(20),
    PRIMARY KEY (accession, keyword),
    FOREIGN KEY (accession) REFERENCES protein(accession)
);

INSERT INTO protein VALUES('7UP2_DROME',7227,76814,'Steroid receptor seven-up, isoform A (Nuclear receptor subfamily 2 group F member 3, isoform A)','MCASPSTAPGFFNPRPQSGAELSAFDIGLSRSMGLGVPPHSAWHEPPASLGGHLHAASAGPGTTTGSVATGGGGTTPSSVASQQSAVIKQDLSCPSLNQAGSGHHPGIKEDLSSSLPSANGGSAGGHHSGSGSGSGSGVNPGHGSDMLPLIKGHGQDMLTSIKGQPTGCGSTTPSSQANSSHSQSSNSGSQIDSKQNIECVVCGDKSSGKHYGQFTCEGCKSFFKRSVRRNLTYSCRGSRNCPIDQHHRNQCQYCRLKKCLKMGMRREAVQRGRVPPTQPGLAGMHGQYQIANGDPMGIAGFNGHSYLSSYISLLLRAEPYPTSRYGQCMQPNNIMGIDNICELAARLLFSAVEWAKNIPFFPELQVTDQVALLRLVWSELFVLNASQCSMPLHVAPLLAAAGLHASPMAADRVVAFMDHIRIFQEQVEKLKALHVDSAEYSCLKAIVLFTTGKLLDILYKDVPALLTKVSALLGKGSTASNDDVLAVVRDHLDELNRQEQESQAQQQAPLHLAAFMNCVAGVEAAVQQAEQAQVPTSSASASVSAPLVPSAGSAFSSCQAKSAGSEMDLLASLYAQAQATPPSSGGGDASGHNNSSGLGASLPTQSQSGSSSRNLTASPLSTSLATAPAPASASAPAPVPTSSVAQVPVPAPVPVTSSASSSSLGGGAYQTPSAAAAAAAMFHYQTPPRAAFGSAFDMFHHSTPFGVGVGHAHALAHSSGSGSASFGSPSYRYSPYSLAGSRWQL');

在cmd或shell中运行sqlite3 data.db，或者在提示符下运行.read data_db.sql 这将在磁盘上创建数据库

2/Python部分：与数据库交互（请参见文档）https://docs.python.org/3/library/sqlite3.html

import sqlite3


with sqlite3.connect('data.db') as conn:
    c = conn.cursor()
    c.execute('SELECT * FROM protein')
    print(c.fetchall())

然后，您必须学习如何制定SQL查询（我也可以提供帮助）

让我知道这对你有什么帮助

Answer 2

with sqlite3.connect('data_db.sql') as conn:
    with conn.cursor() as cur:
        cur.execute('SELECT * from protein')
return cur.rowcount

尝试以上方法，而不是使用get_连接和get_蛋白质方法