Python與SQL的交互通常是通過(guò)數(shù)據(jù)庫(kù)連接庫(kù)來(lái)實(shí)現(xiàn)的。以下是一些常見數(shù)據(jù)庫(kù)及其對(duì)應(yīng)的Python庫(kù),以及如何使用這些庫(kù)與SQL進(jìn)行交互的基本步驟:
import sqlite3
# 連接到SQLite數(shù)據(jù)庫(kù)(如果不存在,則創(chuàng)建)
conn = sqlite3.connect('example.db')
# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行SQL命令
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)')
cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
cursor.execute("INSERT INTO users (name) VALUES ('Bob')")
# 提交更改
conn.commit()
# 查詢數(shù)據(jù)
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
print(row)
# 關(guān)閉連接
conn.close()
mysql-connector-python
庫(kù))首先,安裝庫(kù):
pip install mysql-connector-python
然后,使用以下代碼連接和交互:
import mysql.connector
# 連接到MySQL數(shù)據(jù)庫(kù)
conn = mysql.connector.connect(
host='localhost',
user='your_username',
password='your_password',
database='your_database'
)
# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行SQL命令
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))')
cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
cursor.execute("INSERT INTO users (name) VALUES ('Bob')")
# 提交更改
conn.commit()
# 查詢數(shù)據(jù)
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
print(row)
# 關(guān)閉連接
conn.close()
psycopg2
庫(kù))首先,安裝庫(kù):
pip install psycopg2
然后,使用以下代碼連接和交互:
import psycopg2
# 連接到PostgreSQL數(shù)據(jù)庫(kù)
conn = psycopg2.connect(
host='localhost',
dbname='your_database',
user='your_username',
password='your_password'
)
# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cursor = conn.cursor()
# 執(zhí)行SQL命令
cursor.execute('CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(255))')
cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
cursor.execute("INSERT INTO users (name) VALUES ('Bob')")
# 提交更改
conn.commit()
# 查詢數(shù)據(jù)
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
print(row)
# 關(guān)閉連接
conn.close()
注意:在實(shí)際應(yīng)用中,請(qǐng)確保不要將敏感信息(如數(shù)據(jù)庫(kù)憑據(jù))硬編碼到腳本中??梢允褂铆h(huán)境變量、配置文件或密鑰管理服務(wù)來(lái)安全地存儲(chǔ)這些信息。