關于Python的最小資料庫sqlite3的調用,存儲,讀取詳解
Python的資料庫子產品有很多種。本人學習python的資料庫也僅僅是看着網上的資料自學然後加工了一下,中間加了一些自己實驗使用的小技巧,本人不是計算機專業的,但是希望能将自己做過的東西和大家分享。
這個是本人自己寫的一個項目用到的資料庫代碼,親測有效。我用的python版本為3.6.5,環境是windows。
首先概括下面會有的常用關系型資料庫:SQLite、MySQL、PoesgreSQL、Oracle、SQL Server、 excel
SQLite:sqlite3。(python2.5+内置)
MySQL: MySQLdb
PoesgreSQL:postgresql_psycopg2()
Oracle: cx_Oracle
SQL Server: pymssql、pyodbc、adodbapi
excel: pyExcelertor
(引用自)> https://www.cnblogs.com/scofieldyu/p/5436104.html
這裡我隻講下sqlite3的使用和技巧,畢竟sqlite3的使用已經夠一般的常用功能,存儲,建立,讀取,查詢。
進階的技巧在很高端的項目中運用的比較多,這裡就不講解了。
首先是sqlite3的簡介。
簡介:
SQLite3 可使用 sqlite3 子產品與 Python 進行內建。sqlite3 子產品是由 Gerhard Haring 編寫的。它提供了一個與 PEP 249 描述的 DB-API 2.0 規範相容的 SQL 接口。您不需要單獨安裝該子產品,因為 Python 2.5.x 以上版本預設自帶了該子產品。
自我對sqlite3的了解:
sqlite3這個子產品也叫做最小資料庫,在一般的Python的資料存儲,讀取,查詢中比較常用,也被大多數程式員所青睐。使用簡單,友善,語句短小,不需要像C++寫資料庫一樣先要下載下傳SQL Sever,不需要買本資料庫的書去學習,隻用在CMD的界面裡下載下傳sqlite3的子產品,就是直接使用。
資料庫簡介:
資料庫主要用來資料存儲,讀取,查詢。要建立一個資料庫,就要先建立一個資料的表,在表内寫需要存的資料是什麼。如:需要存入一個班級的人的資訊,name,class,grade,score,等等。然後再規定存入的每個資訊的資料類型,盡量存入的資料類型一緻,不然容易出錯。存儲操作和查詢操作類似,都是對表内的資料進行操作。
sqlite3的下載下傳:
關于sqlite3的下載下傳,我推薦用cmd的界面下載下傳。
在windows的界面打開這個“運作”界面,然後輸入cmd,點選确定。進入cmd的界面。
然後輸入pip install sqlite3,回車,就開始自動下載下傳了。如果出現識别不了的情況,就是電腦的python環境沒有安裝好,請參考其他的文章安裝python 的系統環境變量。
當安裝好sqlite3後,就可以開始使用了。下面我以一個自己做的項目的資料庫做詳解。
sqlite3的使用:
這裡按照資料庫的操作步驟講解如何使用資料庫。請按照1,2,3的順序寫資料庫,當有3.1,3.2等序号時,為目前步驟可以進行各種其他的操作。如:3中可以建立或者存儲或者查詢,如果都需要操作可以單獨寫一個函數,也可以接着下面繼續寫需要的操作,但是連着寫兩個exeute的操作沒有實驗過,不能保證正确性,需要讀者自己測試。
1、sqlite3資料庫的建立:
在調用connect函數的時候,指定庫名稱,如果指定的資料庫存在就直接打開這個資料庫,如果不存在就新建立一個再打開。
import sqlite3
import pandas as pd
conn = sqlite3.connect('temp_wet_data.db')#這裡命名一個conn的變量指向temp_wet_data.db的資料庫檔案,.db為資料庫字尾。
2、建立資料庫遊标:
我們需要使用遊标對象SQL語句建立,存儲,查詢資料庫,獲得對象。 通過以下方法來定義一個遊标。
cursor = conn.cursor()#建立遊标
遊标用來對資料庫中的資料進行操作,是最主要的一步,不論是建立資料庫的表還是要讀取資料庫的資料,都要用遊标操作。
遊标的附錄操作:
execute()–執行sql語句
executemany–執行多條sql語句
close()–關閉遊标
fetchone()–從結果中取一條記錄,并将遊标指向下一條記錄
fetchmany()–從結果中取多條記錄
fetchall()–從結果中取出所有記錄
scroll()–遊标滾動
3、利用遊标對資料庫進行資料存儲或者建立表或者查詢資料操作。
3.1、建立表:
當建立好遊标以後,就是要利用遊标對資料庫的表進行建立,沒有表是不能查詢到任何資料也存不了任何資料的,是以第一步就是要建立一個資料庫内的表。
#執行一條SQL語句:建立user表
cursor.execute('create table user(date varchar(20) ,time varchar(20),chuanganqi varchar(20),bieming varchar(20),wendu varchar(20),shidu varchar(20))')
#這裡建立了一個名字叫user的表,
#括号内date,time,chuangganqi,bieming,wendu,shidu都是要存的變量名。varchar(20)為變量類型。
關于括号内的語句,最後我再把我參考的資料貼上,可以翻到最後參考。
cursor.exeute就是執行一條sql語句,我基本用這條語句可以搞定所有的事情。
當建立好這個表後,要對事務進行送出,相當于進行一次儲存。
儲存代碼:
conn.commit()
3.2、存儲資料:
當資料庫内有表的時候,就可以按照表的形式進行資料存儲了。
cursor.execute("insert into user (date, time,chuanganqi,bieming,wendu,shidu) values('%s','%s','%s','%s','%s','%s')"% (shijian, time,chuanganqi,bieming,wendu,shidu))
print('完成')
conn.commit()
這裡我全部存的%s的類型存進表格,因為表格建立的時候就是字元串的形式建立,是以,存儲也要盡量相同。
3.3、查詢:
資料庫的資料查詢有很多種,可以在一個範圍内查詢,可以根據某個名字查詢,也可以查詢所有的資料。
3.3.1、按某個變量的某個值查詢:
當這個值和這個表中這個變量的位置存的資料值一樣的時候,都會被查找出來。
cursor.execute("select * from user WHERE chuanganqi LIKE '%s'"%(chuanganqi))
#第一個chuanganqi為表中的變量名,第2個為需要查找的值。
這裡我查的是chuanganqi的值,這裡需要有一個變量傳入,如:當要查詢chuanganqi這列中的所有寫的’a’的值的列時,
cursor.execute("select * from user WHERE chuanganqi LIKE '%s'"%(’a‘))
同時也可以進行模糊查詢,
_x:找到以x結尾,并且x前面隻有一個字元的資料,有幾個_代表有幾個資料
x_:找到以x開頭,後面隻有一個字元的資料
x%:找到所有以x結束的資料
%x:找到所有以x開頭的資料
%x%:找到所有包含x的資料
具體來操作一下:找到所有name以C開頭的資料:
cursor.execute('select * from user WHERE chuanganqi LIKE "C_"')
result=cursor.fetchall()
print(result)
3.3.2、按一定範圍查詢:
當給出一定的範圍的時候,可以在一定的範圍内查找sql表内某個列中存的資料。
cursor.execute("select * from user where date>='%s' and date<='%s' and chuanganqi='%s'"%(date_s,date_e,shebei))
#這裡的select *的意思是得到所有的關于後面條件的所有資料。
#date>='%s' and date<='%s'為我的條件,date為建立表時定義的某個變量,這裡是一個日期的列。%s為要代入的字元串。
#(date_s,date_e,shebei)為我要帶入的字元串的名字。這裡的
date_s=‘2019-04-03’
date_e=‘2019-04-04’
#shebei為我要查詢的在這段時間内同時chuanganqi等于某個值時的條件,如果隻要查某個範圍内的話,這句可以不要。
shebei=‘001’
3.3.3、還有一種查詢某個值的查詢的語句:
cursor.execute('select * from user where wendu=?', (-256,))
#這裡查詢的是wendu為某個值的時候所有的資料。-256為我的wendu的資料。
3.3.4、查詢後的讀取出來:
當查詢後,就要把資料讀取出來,讀取操作:
values = cursor.fetchall()
print(values)
這樣就讀取到values裡了。
注意:
讀取出來的值為nonetype,如果是讀取操作是寫在一個函數内,傳回的一定什麼都傳回不了。如果要把nonetype類型的資料轉換成有用的資料,就要在return()前寫轉換語句,如:
return(str(values))
3.4、關閉資料庫。
關閉資料庫前要先關閉遊标,在關閉資料庫。
cursor.close()
conn.close()
4、番外:
如果要讀取所有的内容:
cursor.execute("select * from user ")
values = cursor.fetchall()
return(values)
下面是我的所有的完整代碼:
import sqlite3
import pandas as pd
import datetime
def sql_to_list(s):#将sql的資料轉成list型的函數
r=[]
a=s.replace(' " ', '').replace('[', '').replace(')', '').replace('(', '').replace('"', '').replace(']', '').strip().split(', ')
#print((a))
for i in range(len(a)):
a[i]=a[i][1:len(a[i])-1]
#print(a)
for i in range(int(len(a)/6)):
q=[]
q.append(a[i*6])
q.append(a[i*6+1])
q.append(a[i*6+2])
q.append(a[i*6+3])
q.append(a[i*6+4])
q.append(a[i*6+5])
#print([q])
r.extend([q])
return(r)
def sql_create():
#連接配接到SQlite資料庫
#資料庫檔案是temp_wet_data.db,不存在,則自動建立
conn = sqlite3.connect('temp_wet_data.db')
#建立一個cursor:
cursor = conn.cursor()
#執行一條SQL語句:建立user表
cursor.execute('create table user(date varchar(20) ,time varchar(20),chuanganqi varchar(20),bieming varchar(20),wendu varchar(20),shidu varchar(20))')
#插入一條記錄:
#cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
#cursor.execute('insert into user (id, name) values (\'2\', \'sims\')')
#cursor.execute('insert into user (id, name) values (\'3\', \'sims\')')
#通過rowcount獲得插入的行數:
#print(cursor.rowcount) #reusult 1
#關閉Cursor:
cursor.close()
#送出事務:
conn.commit()
#關閉connection:
conn.close()
def sql_save(shijian,time,chuanganqi,bieming,wendu,shidu):#插入一個資料
print('開始')
#資料庫檔案是test.db,不存在,則自動建立
conn = sqlite3.connect('temp_wet_data.db')
#建立一個cursor:
try:
cursor = conn.cursor()
print('準備')
#"insert into cname(id,name,age)values(%d,'%s',%d)" % (sline['id'],sline['name'],sline['age'])
cursor.execute("insert into user (date, time,chuanganqi,bieming,wendu,shidu) values('%s','%s','%s','%s','%s','%s')"% (shijian, time,chuanganqi,bieming,wendu,shidu))
print('完成')
conn.commit()
print("存儲成功。")
#執行查詢語句:
#cursor.execute('select *from user where name=?', ('sims',))
#使用featchall獲得結果集(list)
#values = cursor.fetchall()
#print(len(values))
#print(values) #result:[('1', 'Michael')]
#關閉cursor
#關閉conn
cursor.close()
conn.close()
except:
cursor = conn.cursor()
cursor.execute('create table user(date varchar(20) ,time varchar(20),chuanganqi varchar(20),bieming varchar(20),wendu varchar(20),shidu varchar(20))')
cursor.execute("insert into user (date, time,chuanganqi,bieming,wendu,shidu) values('%s','%s','%s','%s','%s','%s')"% (shijian, time,chuanganqi,bieming,wendu,shidu))
cursor.close()
#送出事務:
conn.commit()
#關閉connection:
conn.close()
print('已建立,存儲')
def sql_search_shebei(chuanganqi):
#資料庫檔案是test.db,不存在,則自動建立
conn = sqlite3.connect('temp_wet_data.db')
try:
try:
#建立一個cursor:
cursor = conn.cursor()
#執行查詢語句:
try:
cursor.execute("select * from user WHERE bieming LIKE '%s'"%(chuanganqi))
except:
pass
try:
cursor.execute("select * from user WHERE chuanganqi LIKE '%s'"%(chuanganqi))
except:
pass
#cursor.execute('select *from user where chuanganqi=?', ('001',))
#cursor.execute("select name from user where score>=? and score<=? order by score ASC", (low,high))
#使用featchall獲得結果集(list)
values = cursor.fetchall()
#print(len(values))
#print(values) #result:[('1', 'Michael')]
cursor.close()
conn.close()
x=sql_to_list(str(values))
return(x)
except:
cursor.execute('create table user(date varchar(20),time varchar(20),chuanganqi varchar(20),bieming varchar(20),wendu varchar(20),shidu varchar(20))')
conn.commit()
#關閉cursor
#關閉conn
cursor.close()
conn.close()
except:
print("建立失敗")
def sql_search_date_shebei(date_s,date_e,shebei):
#資料庫檔案是test.db,不存在,則自動建立
conn = sqlite3.connect('temp_wet_data.db')
try:
try:
#建立一個cursor:
cursor = conn.cursor()
#執行查詢語句:
#cursor.execute("select * from user WHERE chuanganqi LIKE '%s'"%(chuanganqi))
#cursor.execute('select *from user where chuanganqi=?', ('001',))
cursor.execute("select * from user where date>='%s' and date<='%s' and chuanganqi='%s'"%(date_s,date_e,shebei))
#cursor.execute("select wendu from user where date>=? and date<=? order by date ASC", (date_s,date_e))
#使用featchall獲得結果集(list)
values = cursor.fetchall()
#print(len(values))
#print(values) #result:[('1', 'Michael')]
#關閉cursor
#關閉conn
cursor.close()
conn.close()
x=sql_to_list(str(values))
return(x)
except:
cursor.execute('create table user(date varchar(20),time varchar(20),chuanganqi varchar(20),bieming varchar(20),wendu varchar(20),shidu varchar(20))')
conn.commit()
#關閉cursor
#關閉conn
cursor.close()
conn.close()
except:
print("建立失敗")
def sql_search_date_shebei_one(date,shebei):
st=datetime.datetime.strptime(date, "%Y-%m-%d")
et=(st)
st=st-datetime.timedelta(days=1)
st=st.strftime("%Y-%m-%d %H:%M:%S")
et=et.strftime("%Y-%m-%d %H:%M:%S")
#print(st,et)
s=sql_search_date_shebei(st,et,shebei)
return(s)
def sql_search_date(date_s,date_e):
#資料庫檔案是test.db,不存在,則自動建立
conn = sqlite3.connect('temp_wet_data.db')
try:
try:
#建立一個cursor:
cursor = conn.cursor()
#執行查詢語句:
#cursor.execute("select * from user WHERE chuanganqi LIKE '%s'"%(chuanganqi))
#cursor.execute('select *from user where chuanganqi=?', ('001',))
cursor.execute("select * from user where date>='%s' and date<='%s' "%(date_s,date_e))
#cursor.execute("select wendu from user where date>=? and date<=? order by date ASC", (date_s,date_e))
#使用featchall獲得結果集(list)
values = cursor.fetchall()
#print(len(values))
#print(values) #result:[('1', 'Michael')]
#關閉cursor
#關閉conn
cursor.close()
conn.close()
#print(values)
x=sql_to_list(str(values))
return(x)
except:
cursor.execute('create table user(date varchar(20),time varchar(20),chuanganqi varchar(20),bieming varchar(20),wendu varchar(20),shidu varchar(20))')
conn.commit()
#關閉cursor
#關閉conn
cursor.close()
conn.close()
except:
print("建立失敗")
def sql_search_all():#查詢所有的檔案
conn = sqlite3.connect('temp_wet_data.db')
try:
try:
cursor = conn.cursor()
cursor.execute("select * from user ")
values = cursor.fetchall()
#print(len(values))
#print(values)
cursor.close()
conn.close()
x=sql_to_list(str(values))
return(x)
except:
cursor.execute('create table user(date varchar(20) ,time varchar(20),chuanganqi varchar(20),bieming varchar(20),wendu varchar(20),shidu varchar(20))')
conn.commit()
print('不存在資料庫,已建立')
#關閉cursor
#關閉conn
cursor.close()
conn.close()
except:
print("建立失敗")
關于資料庫的資料格式:
引用自:https://blog.csdn.net/qq_38880380/article/details/80115439
下表列出了當建立 SQLite3 表時可使用的各種資料類型名稱,同時也顯示了相應的親和類型:
INTEGER |
---|
INT |
INTEGER |
TINYINT |
SMALLINT |
MEDIUMINT |
BIGINT |
UNSIGNED BIG INT |
INT2 |
INT8 |
TEXT | 文本 |
---|---|
CHARACTER(20) | |
VARCHAR(255) | |
VARYING CHARACTER(255) | |
NCHAR(55) | |
NATIVE CHARACTER(70) | |
NVARCHAR(100) | |
TEXT | |
CLOB |
NONE | None類型 |
---|---|
BLOB | |
no datatype specified |
REAL | 實數 |
---|---|
REAL | 實數 |
DOUBLE | 雙精度實數 |
DOUBLE PRECISION | |
FLOAT | 單精度實數 |
NUMERI | |
---|---|
NUMERIC | |
DECIMAL(10,5) | |
BOOLEAN | |
DATE | 日期類型。如:"%Y-%m-%d" |
DATETIME | 時間類型,如:"%Y-%m-%d %H:%M:%S" |