python中常見數(shù)據(jù)庫有哪些

來源：懂視網(wǎng) 責(zé)編：小采時間：2020-11-27 14:09:02

python中常見數(shù)據(jù)庫有哪些

python中常見數(shù)據(jù)庫有哪些:python中常見的數(shù)據(jù)庫有哪些呢？數(shù)據(jù)庫大致分為兩大類，第一類是包括關(guān)系數(shù)據(jù)庫，第二類是非關(guān)系數(shù)據(jù)庫，下面介紹一下這兩類數(shù)據(jù)庫的相關(guān)知識。包括關(guān)系數(shù)據(jù)庫：sqlite,mysql,mssql 非關(guān)系數(shù)據(jù)庫：MongoDB,Redis1. 連接Sqliteimport sqlit

推薦度：

點擊下載本文 文檔為doc格式

導(dǎo)讀python中常見數(shù)據(jù)庫有哪些:python中常見的數(shù)據(jù)庫有哪些呢？數(shù)據(jù)庫大致分為兩大類，第一類是包括關(guān)系數(shù)據(jù)庫，第二類是非關(guān)系數(shù)據(jù)庫，下面介紹一下這兩類數(shù)據(jù)庫的相關(guān)知識。包括關(guān)系數(shù)據(jù)庫：sqlite,mysql,mssql 非關(guān)系數(shù)據(jù)庫：MongoDB,Redis1. 連接Sqliteimport sqlit

python中常見的數(shù)據(jù)庫有哪些呢？數(shù)據(jù)庫大致分為兩大類，第一類是包括關(guān)系數(shù)據(jù)庫，第二類是非關(guān)系數(shù)據(jù)庫，下面介紹一下這兩類數(shù)據(jù)庫的相關(guān)知識。

包括關(guān)系數(shù)據(jù)庫：sqlite,mysql,mssql

非關(guān)系數(shù)據(jù)庫：MongoDB,Redis

1. 連接Sqlite

import sqlite3
import traceback
try:
 # 如果表不存在,就創(chuàng)建
 with sqlite3.connect('test.db') as conn:
 print("Opened database successfully")
 # 刪除表
 conn.execute("DROP TABLE IF EXISTS COMPANY")
 # 創(chuàng)建表
 sql = """
 CREATE TABLE IF NOT EXISTS COMPANY
 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
 NAME TEXT NOT NULL,
 AGE INT NOT NULL,
 ADDRESS CHAR(50),
 SALARY REAL);
 """
 conn.execute(sql)
 print("create table successfully")
 # 添加數(shù)據(jù)
 conn.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES (?, ?, ?, ? )",
 [('Paul', 32, 'California', 20000.00),
 ('Allen', 25, 'Texas', 15000.00),
 ('Teddy', 23, 'Norway', 20000.00),
 ('Mark', 25, 'Rich-Mond ', 65000.00),
 ('David', 27, 'Texas', 85000.00),
 ('Kim', 22, 'South-Hall', 45000.00),
 ('James', 24, 'Houston', 10000.00)])
 # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
 # VALUES ( 'Paul', 32, 'California', 20000.00 )")
 #
 # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
 # VALUES ('Allen', 25, 'Texas', 15000.00 )")
 #
 # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
 # VALUES ('Teddy', 23, 'Norway', 20000.00 )")
 #
 # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
 # VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 )")
 #
 # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
 # VALUES ( 'David', 27, 'Texas', 85000.00 )");
 #
 # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
 # VALUES ( 'Kim', 22, 'South-Hall', 45000.00 )")
 #
 # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
 # VALUES ( 'James', 24, 'Houston', 10000.00 )")
 # 提交,否則重新運行程序時,表中無數(shù)據(jù)
 conn.commit()
 print("insert successfully")
 # 查詢表
 sql = """
 select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
 """
 result = conn.execute(sql)
 for row in result:
 print("-" * 50) #

輸出50個-,作為分界線 print("%-10s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對齊 print("%-10s %s" % ("name", row[1])) print("%-10s %s" % ("age", row[2])) print("%-10s %s" % ("address", row[3])) print("%-10s %.2f" % ("salary", row[4])) # or # print('{:10s} {:.2f}'.format("salary", row[4])) except sqlite3.Error as e: print("sqlite3 Error:", e) traceback.print_exc()

2.連接mysql

相關(guān)推薦：《python視頻教程》

2.2 使用MySQLdb

2.1使用mysqldb庫中的_mysql

import MySQLdb
from contextlib import closing
import traceback
try:
 # 獲取一個數(shù)據(jù)庫連接
 with closing(MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', port=3306,charset='utf8')) as conn:
 print("connect database successfully")
 with closing(conn.cursor()) as cur:
 # 刪除表
 cur.execute("DROP TABLE IF EXISTS COMPANY")
 # 創(chuàng)建表
 sql = """
 CREATE TABLE IF NOT EXISTS COMPANY
 (ID INTEGER PRIMARY KEY NOT NULL auto_increment,
 NAME TEXT NOT NULL,
 AGE INT NOT NULL,
 ADDRESS CHAR(50),
 SALARY REAL);
 """
 cur.execute(sql)
 print("create table successfully")
 # 添加數(shù)據(jù)
 # 在一個conn.execute里面里面執(zhí)行多個sql語句是非法的
 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )",
 [('Paul', 32, 'California', 20000.00),
 ('Allen', 25, 'Texas', 15000.00),
 ('Teddy', 23, 'Norway', 20000.00),
 ('Mark', 25, 'Rich-Mond ', 65000.00),
 ('David', 27, 'Texas', 85000.00),
 ('Kim', 22, 'South-Hall', 45000.00),
 ('James', 24, 'Houston', 10000.00)])
 # 提交,否則重新運行程序時,表中無數(shù)據(jù)
 conn.commit()
 print("insert successfully")
 # 查詢表
 sql = """
 select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
 """
 cur.execute(sql)
 for row in cur.fetchall():
 print("-" * 50) #

輸出50個-,作為分界線 print("%-10s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對齊 print("%-10s %s" % ("name", row[1])) print("%-10s %s" % ("age", row[2])) print("%-10s %s" % ("address", row[3])) print("%-10s %s" % ("salary", row[4])) except MySQLdb.Error as e: print("Mysql Error:", e) traceback.print_exc() # 打印錯誤棧信息

2.2 使用MySQLdb

import MySQLdb
from contextlib import closing
import traceback
try:
 # 獲取一個數(shù)據(jù)庫連接
 with closing(MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', port=3306,charset='utf8')) as conn:
 print("connect database successfully")
 with closing(conn.cursor()) as cur:
 # 刪除表
 cur.execute("DROP TABLE IF EXISTS COMPANY")
 # 創(chuàng)建表
 sql = """
 CREATE TABLE IF NOT EXISTS COMPANY
 (ID INTEGER PRIMARY KEY NOT NULL auto_increment,
 NAME TEXT NOT NULL,
 AGE INT NOT NULL,
 ADDRESS CHAR(50),
 SALARY REAL);
 """
 cur.execute(sql)
 print("create table successfully")
 # 添加數(shù)據(jù)
 # 在一個conn.execute里面里面執(zhí)行多個sql語句是非法的
 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )",
 [('Paul', 32, 'California', 20000.00),
 ('Allen', 25, 'Texas', 15000.00),
 ('Teddy', 23, 'Norway', 20000.00),
 ('Mark', 25, 'Rich-Mond ', 65000.00),
 ('David', 27, 'Texas', 85000.00),
 ('Kim', 22, 'South-Hall', 45000.00),
 ('James', 24, 'Houston', 10000.00)])
 # 提交,否則重新運行程序時,表中無數(shù)據(jù)
 conn.commit()
 print("insert successfully")
 # 查詢表
 sql = """
 select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
 """
 cur.execute(sql)
 for row in cur.fetchall():
 print("-" * 50) #

輸出50個-,作為分界線 print("%-10s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對齊 print("%-10s %s" % ("name", row[1])) print("%-10s %s" % ("age", row[2])) print("%-10s %s" % ("address", row[3])) print("%-10s %s" % ("salary", row[4])) except MySQLdb.Error as e: print("Mysql Error:", e) traceback.print_exc() # 打印錯誤棧信息

2.3使用pymysql

2.1和2.2節(jié)使用MySQLdb，不支持Python3.x
pymysql對Python2.x和Python3.x的支持都比較好

import pymysql
from contextlib import closing
import traceback
try:
 # 獲取一個數(shù)據(jù)庫連接,with關(guān)鍵字 表示退出時,conn自動關(guān)閉
 # with 嵌套上一層的with 要使用closing()
 with closing(pymysql.connect(host='localhost', user='root', passwd='root', db='test', port=3306,
 charset='utf8')) as conn:
 print("connect database successfully")
 # 獲取游標(biāo),with關(guān)鍵字 表示退出時,cur自動關(guān)閉
 with conn.cursor() as cur:
 # 刪除表
 cur.execute("DROP TABLE IF EXISTS COMPANY")
 # 創(chuàng)建表
 sql = """
 CREATE TABLE IF NOT EXISTS COMPANY
 (ID INTEGER PRIMARY KEY NOT NULL auto_increment,
 NAME TEXT NOT NULL,
 AGE INT NOT NULL,
 ADDRESS CHAR(50),
 SALARY REAL);
 """
 cur.execute(sql)
 print("create table successfully")
 # 添加數(shù)據(jù)
 # 在一個conn.execute里面里面執(zhí)行多個sql語句是非法的
 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )",
 [('Paul', 32, 'California', 20000.00),
 ('Allen', 25, 'Texas', 15000.00),
 ('Teddy', 23, 'Norway', 20000.00),
 ('Mark', 25, 'Rich-Mond ', 65000.00),
 ('David', 27, 'Texas', 85000.00),
 ('Kim', 22, 'South-Hall', 45000.00),
 ('James', 24, 'Houston', 10000.00)])
 # 提交,否則重新運行程序時,表中無數(shù)據(jù)
 conn.commit()
 print("insert successfully")
 # 查詢表
 sql = """
 select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
 """
 cur.execute(sql)
 for row in cur.fetchall():
 print("-" * 50) #

輸出50個-,作為分界線 print("%-10s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對齊 print("%-10s %s" % ("name", row[1])) print("%-10s %s" % ("age", row[2])) print("%-10s %s" % ("address", row[3])) print("%-10s %s" % ("salary", row[4])) except pymysql.Error as e: print("Mysql Error:", e) traceback.print_exc()

3.連接mssql

import pymssql
from contextlib import closing
try:
 # 先要保證數(shù)據(jù)庫中有test數(shù)據(jù)庫
 # 獲取一個數(shù)據(jù)庫連接,with關(guān)鍵字 表示退出時,conn自動關(guān)閉
 # with 嵌套上一層的with 要使用closing()
 with closing(pymssql.connect(host='192.168.100.114', user='sa', password='sa12345', database='test', port=1433,
 charset='utf8')) as conn:
 print("connect database successfully")
 # 獲取游標(biāo),with關(guān)鍵字 表示退出時,cur自動關(guān)閉
 with conn.cursor() as cur:
 # 刪除表
 cur.execute(
 '''if exists (select 1 from sys.objects where name='COMPANY' and type='U') drop table COMPANY''')
 # 創(chuàng)建表
 sql = """
 CREATE TABLE COMPANY
 (ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,
 NAME TEXT NOT NULL,
 AGE INT NOT NULL,
 ADDRESS CHAR(50),
 SALARY REAL);
 """
 cur.execute(sql)
 print("create table successfully")
 # 添加數(shù)據(jù)
 # 在一個conn.execute里面里面執(zhí)行多個sql語句是非法的
 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )",
 [('Paul', 32, 'California', 20000.00),
 ('Allen', 25, 'Texas', 15000.00),
 ('Teddy', 23, 'Norway', 20000.00),
 ('Mark', 25, 'Rich-Mond', 65000.00),
 ('David', 27, 'Texas', 85000.00),
 ('Kim', 22, 'South-Hall', 45000.00),
 ('James', 24, 'Houston', 10000.00)])
 # 提交,否則重新運行程序時,表中無數(shù)據(jù)
 conn.commit()
 print("insert successfully")
 # 查詢表
 sql = """
 select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
 """
 cur.execute(sql)
 for row in cur.fetchall():
 print("-" * 50) #

輸出50個-,作為分界線 print("%-10s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對齊 print("%-10s %s" % ("name", row[1])) print("%-10s %s" % ("age", row[2])) print("%-10s %s" % ("address", row[3])) print("%-10s %s" % ("salary", row[4])) except pymssql.Error as e: print("mssql Error:", e) # traceback.print_exc()

4.連接MongoDB

import pymongo
from pymongo.mongo_client import MongoClient
import pymongo.errors
import traceback
try:
 # 連接到 mongodb 服務(wù)
 mongoClient = MongoClient('localhost', 27017)
 # 連接到數(shù)據(jù)庫
 mongoDatabase = mongoClient.test
 print("connect database successfully")
 # 獲取集合
 mongoCollection = mongoDatabase.COMPANY
 # 移除所有數(shù)據(jù)
 mongoCollection.remove()
 # 添加數(shù)據(jù)
 mongoCollection.insert_many([{"Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"},
 {"Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"},
 {"Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"},
 {"Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"},
 {"Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"},
 {"Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"},
 {"Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}, ])
 #獲取集合中的值
 for row in mongoCollection.find():
 print("-" * 50) #

輸出50個-,作為分界線 print("%-10s %s" % ("_id", row['_id'])) # 字段名固定10位寬度,并且左對齊 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) print(' ') # 使id自增 mongoCollection.remove() # 創(chuàng)建計數(shù)表 mongoDatabase.counters.save({"_id": "people_id", "sequence_value": 0}) # 創(chuàng)建存儲過程 mongoDatabase.system_js.getSequenceValue = '''function getSequenceValue(sequenceName){ var sequenceDocument = db.counters.findAndModify({ query: {_id: sequenceName}, update: {$inc:{sequence_value: 1}}, new:true }); return sequenceDocument.sequence_value; }''' mongoCollection.insert_many( [{"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}, ]) for row in mongoCollection.find(): print("-" * 50) # 輸出50個-,作為分界線 print("%-10s %s" % ("_id", int(row['_id']))) # 字段名固定10位寬度,并且左對齊 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) except pymongo.errors.PyMongoError as e: print("mongo Error:", e) traceback.print_exc()

5.連接Redis

5.1使用redis

import redis
r = redis.Redis(host='localhost', port=6379, db=0, password="12345")
print("connect", r.ping())
# 看信息
info = r.info()
# or 查看部分信息
# info = r.info("Server")
#

輸出信息 items = info.items() for i, (key, value) in enumerate(items): print("item %s----%s:%s" % (i, key, value)) # 刪除鍵和對應(yīng)的值 r.delete("company") # 可以一次性push一條或多條數(shù)據(jù) r.rpush("company", {"id": 1, "Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}, {"id": 2, "Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}, {"id": 3, "Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}) r.rpush("company", {"id": 4, "Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}) r.rpush("company", {"id": 5, "Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}) r.rpush("company", {"id": 6, "Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}) r.rpush("company", {"id": 7, "Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}) # eval用來將dict格式的字符串轉(zhuǎn)換成dict for row in map(lambda x: eval(x), r.lrange("company", 0, r.llen("company"))): print("-" * 50) # 輸出50個-,作為分界線 print("%-10s %s" % ("_id", row['id'])) # 字段名固定10位寬度,并且左對齊 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) # 關(guān)閉當(dāng)前連接 # r.shutdown() #這個是關(guān)閉redis服務(wù)端

5.2使用pyredis

import pyredis
r = pyredis.Client(host='localhost', port=6379, database=0, password="12345")
print("connect", r.ping().decode("utf-8"))
# 看信息
# info = r.execute("info").decode()
# or 查看部分信息
info = r.execute("info", "Server").decode()
#

輸出信息 print(info) # 刪除鍵和對應(yīng)的值 r.delete("company") # 可以一次性push一條或多條數(shù)據(jù) r.rpush("company", '''{"id": 1, "Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}''', '''{"id": 2, "Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}''', '''{"id": 3, "Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}''') r.rpush("company", '''{"id": 4, "Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}''') r.rpush("company", '''{"id": 5, "Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}''') r.rpush("company", '''{"id": 6, "Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}''') r.rpush("company", '''{"id": 7, "Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}''') # eval用來將dict格式的字符串轉(zhuǎn)換成dict for row in map(lambda x: eval(x), r.lrange("company", 0, r.llen("company"))): print("-" * 50) # 輸出50個-,作為分界線 print("%-10s %s" % ("_id", row['id'])) # 字段名固定10位寬度,并且左對齊 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) # 關(guān)閉當(dāng)前連接 r.close()

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

python中常見數(shù)據(jù)庫有哪些

推薦度：

點擊下載本文 文檔為doc格式

標(biāo)簽：數(shù)據(jù) 數(shù)據(jù)庫有那些

熱門焦點

python中常見數(shù)據(jù)庫有哪些

python中常見數(shù)據(jù)庫有哪些

python中常見數(shù)據(jù)庫有哪些

最新推薦

猜你喜歡

熱門推薦