python - 在 python 中连接“sqlite3”时遇到问题
问题描述
我将以下数据库保存为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'
我怎么解决这个问题?我需要连接到数据库才能进行计算,
解决方案
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');
sqlite3 data.db
在 cmd 或 shell 中运行,并在提示符下运行.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 查询(我也可以提供帮助)!
让我知道这对你有什么帮助。
推荐阅读
- openedge - 带有 Web 服务的 PASOE 业务类实体中带有令牌的自定义标头?
- html - 简单的电子邮件 CSS 显示与浏览器中的不同
- python - Python CGI 脚本根本不渲染
- ios - 在 iOS 10 上的 WKWebView 中打开 PDF 文件时启用捏合缩放手势
- gitlab - 我笔记本电脑上的 Gitlab CI 运行程序无法执行 ci,因为它无权访问 CI_XXX 环境变量
- javascript - jQuery:如何获取选定单元格的列和行标题
- python - 使用贝塞尔曲线的平滑 2d 布朗步走
- azure - 创建资源组并部署资源
- laravel - Laravel 计数 > 其他计数
- reactjs - 如何解决我的 react-dropzone 应用程序的“道具类型错误”