本文将详细探讨如何在Python中连接全种类数据库以及实现相应的CRUD(创建,读取,更新,删除)操作。我们将逐一解析连接MySQL,SQL Server,Oracle,PostgreSQL,MongoDB,SQLite,DB2,Redis,Cassandra,Microsoft Access,ElasticSearch,Neo4j,InfluxDB,Snowflake,Amazon DynamoDB,Microsoft Azure CosMos DB数据库的方法,并演示相应的CRUD操作。
MySQL
连接数据库
Python可以使用mysql-connector-python库连接MySQL数据库:
import mysql.connector conn = mysql.connector.connect(user='username', password='password', host='127.0.0.1', database='my_database') print("Opened MySQL database successfully") conn.close()
CRUD操作
接下来,我们将展示在MySQL中如何进行基本的CRUD操作。
创建(Create)
conn = mysql.connector.connect(user='username', password='password', host='127.0.0.1', database='my_database') cursor = conn.cursor() cursor.execute("CREATE TABLE Employees (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT, ADDRESS CHAR(50), SALARY REAL)") print("Table created successfully") conn.close()
读取(Retrieve)
conn = mysql.connector.connect(user='username', password='password', host='127.0.0.1', database='my_database') cursor = conn.cursor() cursor.execute("SELECT id, name, address, salary from Employees") rows = cursor.fetchall() for row in rows: print("ID = ", row[0]) print("NAME = ", row[1]) print("ADDRESS = ", row[2]) print("SALARY = ", row[3]) conn.close()
更新(Update)
conn = mysql.connector.connect(user='username', password='password', host='127.0.0.1', database='my_database') cursor = conn.cursor() cursor.execute("UPDATE Employees set SALARY = 25000.00 where ID = 1") conn.commit() print("Total number of rows updated :", cursor.rowcount) conn.close()
删除(Delete)
conn = mysql.connector.connect(user='username', password='password', host='127.0.0.1', database='my_database') cursor = conn.cursor() cursor.execute("DELETE from Employees where ID = 1") conn.commit() print("Total number of rows deleted :", cursor.rowcount) conn.close()
SQL Server
连接数据库
Python可以使用pyodbc库连接SQL Server数据库:
import pyodbc conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=my_database;UID=username;PWD=password') print("Opened SQL Server database successfully") conn.close()
CRUD操作
接下来,我们将展示在SQL Server中如何进行基本的CRUD操作。
创建(Create)
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=my_database;UID=username;PWD=password') cursor = conn.cursor() cursor.execute("CREATE TABLE Employees (ID INT PRIMARY KEY NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT, ADDRESS CHAR(50), SALARY REAL)") conn.commit() print("Table created successfully") conn.close()
读取(Retrieve)
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=my_database;UID=username;PWD=password') cursor = conn.cursor() cursor.execute("SELECT id, name, address, salary from Employees") rows = cursor.fetchall() for row in rows: print("ID = ", row[0]) print("NAME = ", row[1]) print("ADDRESS = ", row[2]) print("SALARY = ", row[3]) conn.close()
更新(Update)
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=my_database;UID=username;PWD=password') cursor = conn.cursor() cursor.execute("UPDATE Employees set SALARY = 25000.00 where ID = 1") conn.commit() print("Total number of rows updated :", cursor.rowcount) conn.close()
删除(Delete)
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=my_database;UID=username;PWD=password') cursor = conn.cursor() cursor.execute("DELETE from Employees where ID = 1") conn.commit() print("Total number of rows deleted :", cursor.rowcount) conn.close()
Oracle
连接数据库
Python可以使用cx_Oracle库连接Oracle数据库:
import cx_Oracle dsn_tns = cx_Oracle.makedsn('localhost', '1521', service_name='my_database') conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns) print("Opened Oracle database successfully") conn.close()
CRUD操作
接下来,我们将展示在Oracle中如何进行基本的CRUD操作。
创建(Create)
dsn_tns = cx_Oracle.makedsn('localhost', '1521', service_name='my_database') conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns) cursor = conn.cursor() cursor.execute("CREATE TABLE Employees (ID NUMBER(10) NOT NULL PRIMARY KEY, NAME VARCHAR2(20) NOT NULL, AGE NUMBER(3), ADDRESS CHAR(50), SALARY NUMBER(10, 2))") conn.commit() print("Table created successfully") conn.close()
读取(Retrieve)
dsn_tns = cx_Oracle.makedsn('localhost', '1521', service_name='my_database') conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns) cursor = conn.cursor() cursor.execute("SELECT id, name, address, salary from Employees") rows = cursor.fetchall() for row in rows: print("ID = ", row[0]) print("NAME = ", row[1]) print("ADDRESS = ", row[2]) print("SALARY = ", row[3]) conn.close()
更新(Update)
dsn_tns = cx_Oracle.makedsn('localhost', '1521', service_name='my_database') conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns) cursor = conn.cursor() cursor.execute("UPDATE Employees set SALARY = 25000.00 where ID = 1") conn.commit() print("Total number of rows updated :", cursor.rowcount) conn.close()
删除(Delete)
dsn_tns = cx_Oracle.makedsn('localhost', '1521', service_name='my_database') conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns) cursor = conn.cursor() cursor.execute("DELETE from Employees where ID = 1") conn.commit() print("Total number of rows deleted :", cursor.rowcount) conn.close()
PostgreSQL
连接数据库
Python可以使用psycopg2库连接PostgreSQL数据库:
import psycopg2 conn = psycopg2.connect(database="my_database", user="username", password="password", host="127.0.0.1", port="5432") print("Opened PostgreSQL database successfully") conn.close()
CRUD操作
接下来,我们将展示在PostgreSQL中如何进行基本的CRUD操作。
创建(Create)
conn = psycopg2.connect(database="my_database", user="username", password="password", host="127.0.0.1", port="5432") cursor = conn.cursor() cursor.execute('''CREATE TABLE Employees (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);''') conn.commit() print("Table created successfully") conn.close()
读取(Retrieve)
conn = psycopg2.connect(database="my_database", user="username", password="password", host="127.0.0.1", port="5432") cursor = conn.cursor() cursor.execute("SELECT id, name, address, salary from Employees") rows = cursor.fetchall() for row in rows: print("ID = ", row[0]) print("NAME = ", row[1]) print("ADDRESS = ", row[2]) print("SALARY = ", row[3]) conn.close()
更新(Update)
conn = psycopg2.connect(database="my_database", user="username", password="password", host="127.0.0.1", port="5432") cursor = conn.cursor() cursor.execute("UPDATE Employees set SALARY = 25000.00 where ID = 1") conn.commit() print("Total number of rows updated :", cursor.rowcount) conn.close()
删除(Delete)
conn = psycopg2.connect(database="my_database", user="username", password="password", host="127.0.0.1", port="5432") cursor = conn.cursor() cursor.execute("DELETE from Employees where ID = 1") conn.commit() print("Total number of rows deleted :", cursor.rowcount) conn.close()
MongoDB
连接数据库
Python可以使用pymongo库连接MongoDB数据库:
from pymongo import MongoClient client = MongoClient("mongodb://localhost:27017/") db = client["my_database"] print("Opened MongoDB database successfully") client.close()
CRUD操作
接下来,我们将展示在MongoDB中如何进行基本的CRUD操作。
创建(Create)
在MongoDB中,文档的创建操作通常包含在插入操作中:
client = MongoClient("mongodb://localhost:27017/") db = client["my_database"] employees = db["Employees"] employee = {"id": "1", "name": "John", "age": "30", "address": "New York", "salary": "1000.00"} employees.insert_one(employee) print("Document inserted successfully") client.close()
读取(Retrieve)
client = MongoClient("mongodb://localhost:27017/") db = client["my_database"] employees = db["Employees"] cursor = employees.find() for document in cursor: print(document) client.close()
更新(Update)
client = MongoClient("mongodb://localhost:27017/") db = client["my_database"] employees = db["Employees"] query = { "id": "1" } new_values = { "$set": { "salary": "25000.00" } } employees.update_one(query, new_values) print("Document updated successfully") client.close()
删除(Delete)
client = MongoClient("mongodb://localhost:27017/") db = client["my_database"] employees = db["Employees"] query = { "id": "1" } employees.delete_one(query) print("Document deleted successfully") client.close()
SQLite
连接数据库
Python使用sqlite3库连接SQLite数据库:
import sqlite3 conn = sqlite3.connect('my_database.db') print("Opened SQLite database successfully") conn.close()
CRUD操作
接下来,我们将展示在SQLite中如何进行基本的CRUD操作。
创建(Create)
conn = sqlite3.connect('my_database.db') cursor = conn.cursor() cursor.execute('''CREATE TABLE Employees (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);''') conn.commit() print("Table created successfully") conn.close()
读取(Retrieve)
conn = sqlite3.connect('my_database.db') cursor = conn.cursor() cursor.execute("SELECT id, name, address, salary from Employees") rows = cursor.fetchall() for row in rows: print("ID = ", row[0]) print("NAME = ", row[1]) print("ADDRESS = ", row[2]) print("SALARY = ", row[3]) conn.close()
更新(Update)
conn = sqlite3.connect('my_database.db') cursor = conn.cursor() cursor.execute("UPDATE Employees set SALARY = 25000.00 where ID = 1") conn.commit() print("Total number of rows updated :", cursor.rowcount) conn.close()
删除(Delete)
conn = sqlite3.connect('my_database.db') cursor = conn.cursor() cursor.execute("DELETE from Employees where ID = 1") conn.commit() print("Total number of rows deleted :", cursor.rowcount) conn.close()
DB2
连接数据库
Python可以使用ibm_db库连接DB2数据库:
import ibm_db dsn = ( "DRIVER={{IBM DB2 ODBC DRIVER}};" "DATABASE=my_database;" "HOSTNAME=127.0.0.1;" "PORT=50000;" "PROTOCOL=TCPIP;" "UID=username;" "PWD=password;" ) conn = ibm_db.connect(dsn, "", "") print("Opened DB2 database successfully") ibm_db.close(conn)
CRUD操作
接下来,我们将展示在DB2中如何进行基本的CRUD操作。
创建(Create)
conn = ibm_db.connect(dsn, "", "") sql = '''CREATE TABLE Employees (ID INT PRIMARY KEY NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY DECIMAL(9, 2));''' stmt = ibm_db.exec_immediate(conn, sql) print("Table created successfully") ibm_db.close(conn)
读取(Retrieve)
conn = ibm_db.connect(dsn, "", "") sql = "SELECT id, name, address, salary from Employees" stmt = ibm_db.exec_immediate(conn, sql) while ibm_db.fetch_row(stmt): print("ID = ", ibm_db.result(stmt, "ID")) print("NAME = ", ibm_db.result(stmt, "NAME")) print("ADDRESS = ", ibm_db.result(stmt, "ADDRESS")) print("SALARY = ", ibm_db.result(stmt, "SALARY")) ibm_db.close(conn)
更新(Update)
conn = ibm_db.connect(dsn, "", "") sql = "UPDATE Employees set SALARY = 25000.00 where ID = 1" stmt = ibm_db.exec_immediate(conn, sql) ibm_db.commit(conn) print("Total number of rows updated :", ibm_db.num_rows(stmt)) ibm_db.close(conn)
删除(Delete)
conn = ibm_db.connect(dsn, "", "") sql = "DELETE from Employees where ID = 1" stmt = ibm_db.exec_immediate(conn, sql) ibm_db.commit(conn) print("Total number of rows deleted :", ibm_db.num_rows(stmt)) ibm_db.close(conn)
Microsoft Access
连接数据库
Python可以使用pyodbc库连接Microsoft Access数据库:
import pyodbc conn_str = ( r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' r'DBQ=path_to_your_access_file.accdb;' ) conn = pyodbc.connect(conn_str) print("Opened Access database successfully") conn.close()
CRUD操作
接下来,我们将展示在Access中如何进行基本的CRUD操作。
创建(Create)
conn = pyodbc.connect(conn_str) cursor = conn.cursor() cursor.execute('''CREATE TABLE Employees (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY DECIMAL(9, 2));''') conn.commit() print("Table created successfully") conn.close()
读取(Retrieve)
conn = pyodbc.connect(conn_str) cursor = conn.cursor() cursor.execute("SELECT id, name, address, salary from Employees") rows = cursor.fetchall() for row in rows: print("ID = ", row[0]) print("NAME = ", row[1]) print("ADDRESS = ", row[2]) print("SALARY = ", row[3]) conn.close()
更新(Update)
conn = pyodbc.connect(conn_str) cursor = conn.cursor() cursor.execute("UPDATE Employees set SALARY = 25000.00 where ID = 1") conn.commit() print("Total number of rows updated :", cursor.rowcount) conn.close()
删除(Delete)
conn = pyodbc.connect(conn_str) cursor = conn.cursor() cursor.execute("DELETE from Employees where ID = 1") conn.commit() print("Total number of rows deleted :", cursor.rowcount) conn.close()