为了支持多种类型的数据库进行SQL语法错误检测，需要使用能够连接这些数据库的SQLAlchemy方言以及数据库驱动。可以通过配置多个数据库连接，并根据用户选择的数据库类型进行相应的语法检查。
以下是扩展后的解决方案，支持SQLServer、MySQL、SQLite和Oracle：
1. 安装依赖
在requirements.txt文件中添加所需的数据库驱动：
Flask
SQLAlchemy
sqlparse
pymysql # MySQL
pyodbc # SQLServer
cx_Oracle # Oracle
然后运行：
pip install -r requirements.txt
2. 更新Flask应用 (app.py)
from flask import Flask, render_template, request, send_file, jsonify
import sqlparse
from io import BytesIO
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
app = Flask(__name__)
# 数据库连接字符串，根据需要修改
DATABASES = {
'sqlite': 'sqlite:///:memory:',
'mysql': 'mysql+pymysql://user:password@localhost/testdb',
'sqlserver': 'mssql+pyodbc://user:password@localhost/testdb?driver=SQL+Server',
'oracle': 'oracle+cx_oracle://user:password@localhost:1521/testdb'
}
def get_engine(db_type):
return create_engine(DATABASES[db_type])
@app.route('/')
def index():
return render_template('index.html')
@app.route('/format', methods=['POST'])
def format_sql():
sql = request.form['sql']
db_type = request.form['db_type']
engine = get_engine(db_type)
try:
# SQL语法错误检测
with engine.connect() as connection:
connection.execute(text(sql))
# 格式化SQL
formatted_sql = sqlparse.format(sql, reindent=True, keyword_case='upper')
return jsonify({'formatted_sql': formatted_sql})
except SQLAlchemyError as e:
return jsonify({'error': str(e)})
@app.route('/download', methods=['POST'])
def download_sql():
formatted_sql = request.form['formatted_sql']
buffer = BytesIO()
buffer.write(formatted_sql.encode('utf-8'))
buffer.seek(0)
return send_file(buffer, as_attachment=True, download_name='formatted_sql.sql', mimetype='text/sql')
if __name__ == '__main__':
app.run(debug=True)
3. 更新前端页面 (templates/index.html)
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>SQL Formatter</title>
<link rel="stylesheet" href="{{ url_for('static', filename='main.css') }}">
</head>
<body>
<h1>SQL Formatter</h1>
<form id="sqlForm">
<textarea id="sqlInput" name="sql" rows="10" cols="80" placeholder="Paste your SQL here..."></textarea>
<br>
<label for="dbType">Select Database Type:</label>
<select id="dbType" name="db_type">
<option value="sqlite">SQLite</option>
<option value="mysql">MySQL</option>
<option value="sqlserver">SQLServer</option>
<option value="oracle">Oracle</option>
</select>
<br>
<button type="button" onclick="formatSQL()">Format SQL</button>
<br><br>
<textarea id="formattedSqlOutput" rows="10" cols="80" readonly></textarea>
<br>
<button type="button" onclick="copyToClipboard()">Copy to Clipboard</button>
<button type="button" onclick="downloadSQL()">Download SQL</button>
</form>
<script>
async function formatSQL() {
const sql = document.getElementById('sqlInput').value;
const dbType = document.getElementById('dbType').value;
const response = await fetch('/format', {
method: 'POST',
headers: {
'Content-Type': 'application/x-www-form-urlencoded',
},
body: new URLSearchParams({
'sql': sql,
'db_type': dbType
})
});
const result = await response.json();
if (result.error) {
alert('Error: ' + result.error);
} else {
document.getElementById('formattedSqlOutput').value = result.formatted_sql;
}
}
function copyToClipboard() {
const formattedSql = document.getElementById('formattedSqlOutput');
formattedSql.select();
document.execCommand('copy');
alert('SQL copied to clipboard');
}
async function downloadSQL() {
const formattedSql = document.getElementById('formattedSqlOutput').value;
const response = await fetch('/download', {
method: 'POST',
headers: {
'Content-Type': 'application/x-www-form-urlencoded',
},
body: new URLSearchParams({
'formatted_sql': formattedSql
})
});
const blob = await response.blob();
const url = window.URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = 'formatted_sql.sql';
document.body.appendChild(a);
a.click();
window.URL.revokeObjectURL(url);
}
</script>
</body>
</html>
4. 配置数据库连接
确保在DATABASES字典中配置正确的数据库连接字符串。需要根据实际环境修改其中的用户名、密码、主机和数据库名。
5. 运行应用
在项目根目录下运行：
python app.py
打开浏览器，访问http://127.0.0.1:5000/，将看到一个支持选择不同数据库类型并进行SQL语法错误检测、格式化、复制和下载的界面。
当前文章价值4.21元，扫一扫支付后添加微信提供帮助！（如不能解决您的问题，可以申请退款）
评论已关闭！