python 連接各類主流數(shù)據(jù)庫的實例代碼
更新時間：2018年01月30日 10:16:03 作者：sanxiaochengyu
下面小編就為大家分享一篇python 連接各類主流數(shù)據(jù)庫的實例代碼，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
本篇博文主要介紹Python連接各種數(shù)據(jù)庫的方法及簡單使用
包括關(guān)系數(shù)據(jù)庫：sqlite,mysql,mssql
非關(guān)系數(shù)據(jù)庫：MongoDB,Redis
代碼寫的比較清楚，直接上代碼
1.連接sqlite
# coding=utf-8
# http://www.runoob.com/sqlite/sqlite-python.html
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
2.1使用mysqldb庫中的_mysql
#! /usr/bin/env python2.7
# coding=utf-8
# Created by xiaosanyu at 16/5/30
# mysqldb 只支持python2.7
# http://mysql-python.sourceforge.net/
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
#! /usr/bin/env python2.7
# coding=utf-8
# Created by xiaosanyu at 16/5/30
# mysqldb 只支持python2.7
# http://mysql-python.sourceforge.net/
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的支持都比較好
# Created by xiaosanyu at 16/5/30
# coding=utf-8
# https://github.com/PyMySQL/PyMySQL/
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
# Created by xiaosanyu at 16/5/30
# http://www.pymssql.org/en/latest/
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
# Created by xiaosanyu at 16/5/30
# https://docs.mongodb.com/ecosystem/drivers/python/
# https://pypi.python.org/pypi/pymongo/
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('\n\n\n')
# 使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
# coding=utf-8
# Created by xiaosanyu at 16/5/31
# https://pypi.python.org/pypi/redis/2.10.5
# http://redis-py.readthedocs.io/en/latest/#
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
# Created by xiaosanyu at 16/5/30
# http://pyredis.readthedocs.io/en/latest/
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()
以上這篇python 連接各類主流數(shù)據(jù)庫的實例代碼就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要介紹了python實現(xiàn)的簡單RPG游戲流程,實例分析了Python實現(xiàn)RPG游戲流程的常用判定技巧,具有一定參考借鑒價值,需要的朋友可以參考下2015-06-06
本文主要介紹了python進(jìn)程間通信的項目實踐，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
今天小編就為大家分享一篇解決Pycharm 導(dǎo)入其他文件夾源碼的2種方法，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-02-02
這篇文章主要為大家詳細(xì)介紹了python小程序之4名牌手洗牌發(fā)牌問題，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2020-05-05
今天小編就為大家分享一篇pytorch 彩色圖像轉(zhuǎn)灰度圖像實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-01-01
這篇文章主要為大家詳細(xì)介紹了如何使用Python調(diào)用快遞查詢API接口,并提取出我們需要的快遞信息,感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2024-04-04
這篇文章主要介紹了Python實現(xiàn)快速計算詞頻功能,結(jié)合實例形式總結(jié)分析了Python使用nltk庫進(jìn)行詞頻計算功能的相關(guān)操作技巧,需要的朋友可以參考下2018-06-06
這篇文章主要介紹了Python中的null是什么,Python中其實沒有null這個詞，取而代之的是None對象，即特殊類型NoneType，代表空、沒有，本文通過實例代碼給大家介紹的非常詳細(xì)，需要的朋友可以參考下2022-09-09
Python通過Tesseract庫實現(xiàn)文字識別
這篇文章主要介紹了Python通過Tesseract庫實現(xiàn)文字識別,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-03-03
這篇文章主要介紹了Python氣泡提示與標(biāo)簽的實現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-04-04