前言
提到数据库,首先会想到的就是MySql,作为最流行的关系型数据库,凭借其体积小、速度快、开源免费等优点,得到了开发者们的青睐,普遍应用于中小型网站和小规模公司的数据管理业务中。
而有些场景下,开发者们会需要一种更加轻便的数据库,用于非中心化地存储一些配置信息、使用信息等,例如一些客户端软件的用户配置信息需要存储到用户本地,而用户系统中一般是不会搭建数据库服务的。
因此,SQLite应运而生。
SQLite介绍
SQLite是一个无服务器的、零配置的、事务性的 SQL 数据库引擎。
无服务器和零配置意味着与其他数据库不同,其他数据库在使用前都需要先在服务器搭建数据库服务,并且需要保持服务或者进程在后台持续运行。
SQLite不是一个独立的服务或者进程,而是一个数据库存储文件,其他的程序或者编程语言可以通过特定的方式直接访问这个存储文件使用数据库。
如果实在没有可以访问数据库文件的程序或者编程语言,可以从SQLite下载页面下载安装SQLite软件使用。
SQLite的优点如下
-
不需要一个单独的服务器进程或操作的系统(无服务器)
-
SQLite 不需要配置,不需要安装或管理(零配置)
-
一个完整的 SQLite 数据库是存储在单一的磁盘文件中
- 支持数据库体积最大到2TB
-
SQLite 是轻量级的,完全配置时小于 400KB,省略可选功能配置时小于250KB
-
SQLite 是自给自足的,不需要任何外部的依赖
-
SQLite 事务是完全兼容 ACID 的,允许从多个进程或线程安全访问
-
SQLite 支持 SQL92(SQL2)标准的大多数查询语言的功能
-
SQLite 使用 ANSI-C 编写,并提供了简单和易于使用的 API
-
支持多种开发语言,C, C++, PHP, Perl, Java, C#,Python,Ruby等
-
SQLite 可在 UNIX(Linux, Mac OS-X, Android, iOS)和 Windows(Win32, WinCE, WinRT)中运行
SQLite的基础命令
SQLite的命令和MySql的非常相似,常用的例如
- CREATE 创建表、视图等对象
- ALTER 修改数据库中某个已有的对象
- DROP 删除表、视图等对象
- INSERT 创建记录
- UPDATE 修改记录
- DELETE 删除记录
- SELECT 从一个表或者多个表中检索记录
- PRAGMA 控制SQLite环境中的各种环境变量和状态标志
- COPY 复制对象或者记录
SQLite可视化工具
首先推荐的可视化工具就是sqliteviz,无需安装,直接打开网页,上传db文件就可以使用,也支持csv文件的可视化,简单方便。
其次是sqlitebrowser,虽然需要安装,但胜在功能更强大,如果需要经常查看数据库的话推荐使用。
最后还有绕不过的Navicat家的Navicat for SQLite,优点是界面美观,功能强大,缺点则是需要付费或者和谐了。
Python操作SQLite
Python 在2.5版本以上就已经内置了 sqlite3库 ,因此在Python中可以直接使用SQLite。
导入sqlite3库
导入方式很简单,直接import即可。
import sqlite3
建立数据库连接
数据库建立连接方法如下
con=sqlite3.connect("yumefx.db")
如果db文件不存在的话会自动创建,建立连接成功后会返回一个connection对象。
可以使用 :memory:
来在 RAM 中打开一个到 database 的数据库连接,而不是在磁盘上打开。
当一个数据库被多个连接访问,且其中一个修改了数据库,此时 SQLite 数据库被锁定,直到事务提交。
创建游标对象
cur=con.cursor()
执行SQL命令
使用游标对象的execute方法可以执行SQL命令
cur.execute("create table if not exists book(id primary key,name,tel)")
也可以通过executemany方法批量执行多条SQL命令
cur.executemany("insert into book(id,name,tel) values(?,?,?)",[("book1","KON",18), ("book2","SOX",24)])
还可以通过executescript
方法执行一个SQL脚本,脚本中每个SQL命令之间记得用;
隔开。
数据库提交和回滚
con.commit() con.rollback()
注意提交事务和回滚操作是对数据库的操作,只有数据库的connection对象才具有提交commit和回滚rollback的两个方法,不要与游标对象混淆。
获取游标对象的查询结果
cur.execute("select id,name,tel from book") for row in cur: print(row)
还可以通过fetchone()、fetchmany(size)、fetchall()方式,根据游标现在的位置进行查询结果的遍历。
cur.execute("select id,name,tel from book") print(cur.fetchone()) print(cur.fetchmany(3)) print(cur.fetchall())
使用cursor对象执行select语句时,使用fetchcall方法返回的是查询结果集。结果集是一个list,每个元素都是一个tuple,对应一行记录,按建表的字段顺序排列。
fetchone只返回一条结果,是一个tuple,每个元素是一个字段值。
需要注意的是,SQLite游标是有状态的,只能遍历结果集一次,不能在结果集中返回移动,遍历结束会返回空值。
关闭游标对象和数据库连接
cur.close() #关闭游标对象 con.close() #关闭connection对象
世上的人大半不会用自己的脑袋思考,
而且越是不思考的人,越不愿倾听别人说话。
《IQ84》
——村上春树
评论
nice content!nice history!! boba 😀
https://karuniamedicalclinic.com/storage/team/?plans=linetogel
nice content!nice history!! boba 😀
https://mayomhospital.com/dana/?judul=nanastoto
hello
https://pa-kualapembuang.go.id/-/?miss=YOWESTOGEL
एशियाई अश्लील qqyyooppxx.DtPjTEOVAHK
मिशनरी स्टाइल के अश्लीलता के बारे में बतावल गइल बा hjkvbasdfzxzz.RLFH904XkkA
wow, amazing
http://115.178.54.92/konten_web/lpse/foto/yoktogel/
मिश्रित अश्लीलता txechdyzxca.wX4VPxPcj0i
wow, amazing
http://103.160.152.144/uploads/user/2024-03-29-104435.800489yowestogel.html/
My partner and I absolutely love your blog and find almost all of your post’s to be exactly I’m looking for. Do you offer guest writers to write content for you? I wouldn’t mind creating a post or elaborating on most of the subjects you write in relation to here. Again, awesome weblog!
https://discentra.es/patinetes-electricos/270222-niu-k1p342w1e11-kqi1-pro-25-kmh-negro-blanco.html
nice content!nice history!! boba 😀
https://dms.ft-umt.ac.id/togelup
Incredible! I just finished reading your blog post and I’m blown away. Your insight on this subject is spot-on. I’ve learned so much and can’t wait to see what you write next. Your work is inspiring!
https://moniquemdigitalmarketingcompany.org/indrabet-login-04
Wow! I just finished reading your post and I’m blown away. Your perspective on this topic is spot-on. I’ve gained a new perspective and am eager to read more. Keep up the great work!
https://fkip.unimen.ac.id/?fkip=togelon
Good site! I really love how it is simple on my eyes and the data are well written. I am wondering how I could be notified when a new post has been made. I have subscribed to your feed which must do the trick! Have a great day!
https://www.xixona.net/810-televisores/327526-cecotec-02628-vqu20043-109-2-cm-43-4k-ultra-hd-smart-tv-negro-8435484026284.html
nice content!nice history!! boba 😀
https://siakad.unik-cipasung.ac.id/date/04/-/?mhs=PROTOGEL
nice content!nice history!! boba 😀
https://siakad.unik-cipasung.ac.id/date/04/-/?mhs=INDRABET
nice content!nice history!! boba 😀
https://resbandara.bali.polri.go.id/asset/?bantuloh=lunatogel
सह शॉट अश्लील hkyonet.plR1udFM0ed
Fantastic! I recently read your blog post and I’m thoroughly impressed. Your analysis on this topic is spot-on. I’ve learned so much and can’t wait to read more. Keep up the great work!
https://siakad.unik-cipasung.ac.id/date/04/-/?mhs=DANATOTO
Fantastic! I just finished reading your article and I’m blown away. Your analysis on this subject is incredibly insightful. It really made me think and can’t wait to read more. Keep up the great work!
https://lsp-p1smkn3kendal.co.id/bahasa/?id=indratogel
Wow! I recently read your article and I’m blown away. Your analysis on this topic is spot-on. It really made me think and can’t wait to see what you write next. Keep up the great work!
https://smart2.unulampung.ac.id/
phising
https://bpkad.kuburayakab.go.id/WEB/linetogel/
lost money
https://apps.perteta.or.id/pages/?lebarancuan=goltogel+868
lost money
https://urcues.org/pulitoto-com/
scam
https://mtsmacikaliung.sch.id/zeuz/index.php?judul=nanastoto-com
funny ਸੈਕਸ ਪੋਰਨ madisonivysex.2CPphvgKsjb
Fantastic! I recently read your post and I’m blown away. Your perspective on the topic is extremely valuable. It really made me think and am eager to read more. Your work is inspiring!
https://dp3ap2kb.surakarta.go.id/player/zeus-slot/
Fantastic! I just read your post and I’m absolutely amazed. Your perspective on this topic is incredibly insightful. I’ve learned so much and can’t wait to see your next post. Your work is inspiring!
https://apps.perteta.or.id/pages/?lebarancuan=nanastoto
Fantastic! I recently read your blog post and I’m thoroughly impressed. Your analysis on the topic is extremely valuable. I’ve learned so much and can’t wait to see what you write next. Your work is inspiring!
https://www.sirusuk.pkp.sukoharjokab.go.id/login/fonts/rumah/?daftarkan=ziatogel
scam
https://elearning.smanpa-kotabengkulu.sch.id/sale/?idanti=oppatoto
phising
https://siakad.unik-cipasung.ac.id/date/04/-/?mhs=ANGKABET
Amazing! I recently read your article and I’m blown away. Your insight on the topic is incredibly insightful. It really made me think and am eager to see what you write next. Your work is inspiring!
http://sibisa.batubarakab.go.id/udara/?pidi=partaitogel20wap
Wow! I just finished reading your blog post and I’m absolutely amazed. Your insight on this topic is incredibly insightful. I’ve gained a new perspective and can’t wait to see what you write next. Your work is inspiring!
https://pgsd.unimen.ac.id/wp-content/plugins/photo-gallery/files/gengtoto/
Incredible! I just read your post and I’m blown away. Your analysis on this subject is extremely valuable. I’ve learned so much and can’t wait to read more. Keep up the great work!
https://linetogelofficial.com/
ladesbet ਕਿਸ਼ੋਰ ਪੋਰਨੋਗ੍ਰਾਫੀ ladesinemi.3j1QnfBdwal
ladesbet 大きなお尻ポルノ ladestinemi.mYdISGm3NCS
phising
https://tss.tabalongkab.go.id/-/?seoghost=indrabet
scam
https://resbandara.bali.polri.go.id/asset/?bantuloh=LUNATOGEL
Fantastic! I just finished reading your post and I’m absolutely amazed. Your insight on this subject is extremely valuable. I’ve gained a new perspective and am eager to see what you write next. Thanks for sharing!
https://linetogelofficial.com/
phising
https://indoku.cdn.ars.ac.id/?apalu=watitoto
lost money
https://sikatineung.cianjurkab.go.id/sller-resmi/?link=udintogel
lost money
http://ce.fti.unand.ac.id/new/apk/?carigoogle=jpslot
phising
https://mtsnukertek01.sch.id/produk/?id=lunatogel
phising
https://babat-terus.smartcity.kotaprabumulih.go.id/sikatterus/?webid=yowestogel
Incredible! I just finished reading your post and I’m blown away. Your analysis on this topic is extremely valuable. I’ve learned so much and can’t wait to see what you write next. Thanks for sharing!
https://lhp.balikpapan.go.id/dist/?nama=togelon
Amazing! I just read your post and I’m thoroughly impressed. Your insight on this subject is extremely valuable. It really made me think and can’t wait to read more. Keep up the great work!
https://tribratanews.rembang.jateng.polri.go.id/shop/?luelan=gengtoto
Amazing! I recently read your post and I’m thoroughly impressed. Your insight on this topic is spot-on. I’ve learned so much and can’t wait to read more. Your work is inspiring!
https://fs.uinsa.ac.id/?gid=patihtoto
Amazing! I recently read your blog post and I’m thoroughly impressed. Your perspective on this topic is spot-on. It really made me think and can’t wait to see what you write next. Thanks for sharing!
http://103.169.26.186:89/meranti/login/css/gengtoto
Incredible! I recently read your blog post and I’m thoroughly impressed. Your analysis on this topic is extremely valuable. It really made me think and am eager to see your next post. Thanks for sharing!
https://heylink.me/Depobos/
I urge you stay away from this site. My own encounter with it was purely frustration as well as doubts about deceptive behavior. Exercise extreme caution, or even better, find an honest service to meet your needs.
https://dpkplh.banjarnegarakab.go.id/css/?traffic=TOGELON
I strongly recommend stay away from this platform. My personal experience with it was nothing but disappointment along with concerns regarding scamming practices. Be extremely cautious, or better yet, look for a trustworthy service to fulfill your requirements.
https://sister.sttberitahidup.ac.id/sgacor/?siswa=yoktogel
I urge you steer clear of this platform. My own encounter with it was purely dismay along with doubts about fraudulent activities. Be extremely cautious, or even better, seek out a trustworthy site to fulfill your requirements.
http://ami.ubhara.ac.id/public/?perompak=wdbos
Incredible! I just finished reading your post and I’m blown away. Your insight on this subject is incredibly insightful. It really made me think and am eager to see your next post. Thanks for sharing!
https://siprowid.unanda.ac.id/assets11/prodi.unanda/dev/-/products/?daftar=linetogel+slot
I strongly recommend to avoid this platform. My own encounter with it has been only disappointment as well as doubts about scamming practices. Proceed with extreme caution, or even better, find an honest site for your needs.
https://siumpeg.ummi.ac.id/webroot/catatan/cariuang/?thr=jutawanbet
I strongly recommend to avoid this platform. The experience I had with it has been only disappointment along with doubts about deceptive behavior. Exercise extreme caution, or alternatively, look for an honest site to fulfill your requirements.
https://tik.ft.unm.ac.id/gorlden/?products=situstoto
I strongly recommend to avoid this site. The experience I had with it was only disappointment as well as doubts about fraudulent activities. Be extremely cautious, or better yet, find a more reputable service for your needs.
https://game.ibunda.id/konsultasi/?jiwa=depobos
I simply desired to say thanks all over again. I am not sure the things I would’ve taken care of without the type of hints shared by you directly on that problem. It has been a very difficult situation for me personally, nevertheless being able to view a new well-written fashion you solved the issue made me to jump with happiness. I’m just grateful for this guidance and then sincerely hope you find out what a great job you are always undertaking instructing the rest through the use of your blog post. I know that you’ve never encountered all of us.
https://www.bigzh.com/hire-a-hacker-for-snapchat/
Emergency Tree Services: Storm damage? Fallen tree? We’re here for you 24/7! Call us for emergency tree services and we’ll be there in no time.
https://sites.google.com/treeserviceusa.site/tree-removal-west-perrine-f/home
I strongly recommend to avoid this site. My personal experience with it has been purely disappointment and doubts about scamming practices. Exercise extreme caution, or better yet, find a trustworthy service to meet your needs.
https://silajara.kepulauanselayarkab.go.id/administrator/files/?upload=pwvip4d20wap
I strongly recommend steer clear of this platform. The experience I had with it was nothing but dismay and doubts about deceptive behavior. Proceed with extreme caution, or alternatively, seek out a more reputable site for your needs.
http://222.124.4.74/simkeu/less/js/lunatogel/
I urge you steer clear of this site. The experience I had with it has been nothing but frustration as well as doubts about deceptive behavior. Proceed with extreme caution, or alternatively, look for a trustworthy platform to meet your needs.
https://prp2.taputkab.go.id/marketplace/?link=danatoto
I highly advise stay away from this site. My own encounter with it was nothing but dismay along with concerns regarding scamming practices. Be extremely cautious, or alternatively, find a trustworthy platform to fulfill your requirements.
https://apps.perteta.or.id/pages/?lebarancuan=indrabet
There is visibly a bunch to realize about this. I consider you made certain nice points in features also.
https://youtu.be/CDPkB9NIJGg
I got what you intend,bookmarked, very decent site.
https://youtu.be/u-1NI679V24
I urge you steer clear of this site. My own encounter with it has been purely frustration as well as doubts about fraudulent activities. Exercise extreme caution, or alternatively, seek out a more reputable service to fulfill your requirements.
https://desaklampok.gresikkab.go.id/assets/apk/?carigoogle=linetogel
I highly advise steer clear of this site. My own encounter with it was only disappointment along with suspicion of scamming practices. Exercise extreme caution, or better yet, find an honest service to meet your needs.
https://aset.gorontalokota.go.id/display/?showid=fiatogel
I urge you stay away from this site. My personal experience with it has been purely dismay and concerns regarding scamming practices. Be extremely cautious, or better yet, seek out a more reputable site for your needs.
https://pkt-bpsdm.kalbarprov.go.id/app/zeus-slot.html
Howdy very nice website!! Man .. Excellent .. Wonderful .. I’ll bookmark your site and take the feeds alsoKI am satisfied to seek out so many useful info right here within the put up, we need develop extra strategies on this regard, thanks for sharing. . . . . .
https://youtu.be/l3-eyPbi5js
I urge you to avoid this site. My personal experience with it has been nothing but frustration and doubts about deceptive behavior. Proceed with extreme caution, or even better, find a more reputable service to meet your needs.
http://203.142.77.181:99/img/jpslot/
I strongly recommend to avoid this site. My personal experience with it has been only disappointment as well as suspicion of scamming practices. Exercise extreme caution, or better yet, look for an honest platform to meet your needs.
https://jdih1.sambas.go.id/assets/?site=danatoto
I urge you steer clear of this platform. My personal experience with it was nothing but dismay as well as concerns regarding scamming practices. Exercise extreme caution, or alternatively, find an honest site for your needs.
https://jdih1.sambas.go.id/assets/?site=indratogel
I strongly recommend stay away from this site. My own encounter with it was only dismay along with suspicion of scamming practices. Proceed with extreme caution, or better yet, seek out a trustworthy site for your needs.
https://ikm.dinsos.pringsewukab.go.id/css/?traffic=fiatogel+slot
I urge you to avoid this platform. The experience I had with it has been nothing but frustration along with concerns regarding fraudulent activities. Exercise extreme caution, or alternatively, find an honest site for your needs.
https://acitya.mayasaribakti.ac.id/files/?siauhe=TOGELON
I urge you stay away from this site. My own encounter with it has been purely dismay as well as doubts about fraudulent activities. Be extremely cautious, or even better, seek out a trustworthy site for your needs.
https://www.apply.candler.emory.edu/?tunnel=yowestogel
I highly advise stay away from this site. The experience I had with it has been nothing but disappointment along with suspicion of deceptive behavior. Exercise extreme caution, or alternatively, seek out a trustworthy service for your needs.
https://jdih1.sambas.go.id/assets/?site=lunatogel