包括關系數據庫：sqlite,mysql,mssql
非關系數據庫：MongoDB,Redis
1. 連接Sqlite
import sqlite3
import traceback
try:
# 如果表不存在,就創建
with sqlite3.connect('test.db') as conn:
print("Opened database successfully")
# 刪除表
conn.execute("DROP TABLE IF EXISTS COMPANY")
# 創建表
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")
# 添加數據
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 )")
# 提交,否則重新運行程序時,表中無數據
conn.commit()
print("insert successfully")
# 查詢表
sql = """
select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
"""
result = conn.execute(sql)
for row in result:
print("-" * 50) # 2.連接mysql
相關推薦：《python視頻教程》
2.2 使用MySQLdb
2.1使用mysqldb庫中的_mysql
import MySQLdb
from contextlib import closing
import traceback
try:
# 獲取一個數據庫連接
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")
# 創建表
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")
# 添加數據
# 在一個conn.execute里面里面執行多個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)])
# 提交,否則重新運行程序時,表中無數據
conn.commit()
print("insert successfully")
# 查詢表
sql = """
select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
"""
cur.execute(sql)
for row in cur.fetchall():
print("-" * 50) # 2.2 使用MySQLdb
import MySQLdb
from contextlib import closing
import traceback
try:
# 獲取一個數據庫連接
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")
# 創建表
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")
# 添加數據
# 在一個conn.execute里面里面執行多個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)])
# 提交,否則重新運行程序時,表中無數據
conn.commit()
print("insert successfully")
# 查詢表
sql = """
select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
"""
cur.execute(sql)
for row in cur.fetchall():
print("-" * 50) # 2.3使用pymysql
2.1和2.2節使用MySQLdb，不支持Python3.x
pymysql對Python2.x和Python3.x的支持都比較好
import pymysql
from contextlib import closing
import traceback
try:
# 獲取一個數據庫連接,with關鍵字 表示退出時,conn自動關閉
# 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")
# 獲取游標,with關鍵字 表示退出時,cur自動關閉
with conn.cursor() as cur:
# 刪除表
cur.execute("DROP TABLE IF EXISTS COMPANY")
# 創建表
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")
# 添加數據
# 在一個conn.execute里面里面執行多個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)])
# 提交,否則重新運行程序時,表中無數據
conn.commit()
print("insert successfully")
# 查詢表
sql = """
select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
"""
cur.execute(sql)
for row in cur.fetchall():
print("-" * 50) # 3.連接mssql
import pymssql
from contextlib import closing
try:
# 先要保證數據庫中有test數據庫
# 獲取一個數據庫連接,with關鍵字 表示退出時,conn自動關閉
# 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")
# 獲取游標,with關鍵字 表示退出時,cur自動關閉
with conn.cursor() as cur:
# 刪除表
cur.execute(
'''if exists (select 1 from sys.objects where name='COMPANY' and type='U') drop table COMPANY''')
# 創建表
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")
# 添加數據
# 在一個conn.execute里面里面執行多個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)])
# 提交,否則重新運行程序時,表中無數據
conn.commit()
print("insert successfully")
# 查詢表
sql = """
select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY
"""
cur.execute(sql)
for row in cur.fetchall():
print("-" * 50) # 4.連接MongoDB
import pymongo
from pymongo.mongo_client import MongoClient
import pymongo.errors
import traceback
try:
# 連接到 mongodb 服務
mongoClient = MongoClient('localhost', 27017)
# 連接到數據庫
mongoDatabase = mongoClient.test
print("connect database successfully")
# 獲取集合
mongoCollection = mongoDatabase.COMPANY
# 移除所有數據
mongoCollection.remove()
# 添加數據
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) # 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")
# 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()
# 聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2025 51dongshi.com 版權所有
違法及侵權請聯系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬商天勤律師事務所王興未律師提供法律服務
