调用两个数据库的数据，可以使用多数据库连接技术，在Python中，使用SQLAlchemy或直接通过数据库驱动分别连接两个数据库，然后执行查询并整合
现代软件开发中，经常会遇到需要同时调用两个或多个数据库的情况，这可能是因为业务需求涉及到多个数据源的整合、数据迁移、或者是为了实现更复杂的数据处理逻辑，下面将详细介绍如何调用两个数据库,包括不同的技术栈和实现方式。
使用PHP连接两个数据库
使用mysqli扩展
<?php
// 连接第一个数据库
$mysqli1 = new mysqli("localhost", "user1", "password1", "db1");
if ($mysqli1->connect_error) {
die("连接第一个数据库失败: " . $mysqli1->connect_error);
}
// 连接第二个数据库
$mysqli2 = new mysqli("localhost", "user2", "password2", "db2");
if ($mysqli2->connect_error) {
die("连接第二个数据库失败: " . $mysqli2->connect_error);
}
// 查询第一个数据库的数据
$query1 = "SELECT FROM table1";
$result1 = $mysqli1->query($query1);
// 查询第二个数据库的数据
$query2 = "SELECT FROM table2";
$result2 = $mysqli2->query($query2);
// 处理结果
while ($row1 = $result1->fetch_assoc()) {
echo "数据1: " . $row1["column1"] . "n";
}
while ($row2 = $result2->fetch_assoc()) {
echo "数据2: " . $row2["column1"] . "n";
}
// 关闭数据库连接
$mysqli1->close();
$mysqli2->close();
?>
使用PDO扩展
<?php
try {
// 连接第一个数据库
$dbh1 = new PDO('mysql:host=localhost;dbname=db1', 'user1', 'password1');
$dbh1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 连接第二个数据库
$dbh2 = new PDO('mysql:host=localhost;dbname=db2', 'user2', 'password2');
$dbh2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 查询第一个数据库的数据
$stmt1 = $dbh1->prepare("SELECT FROM table1");
$stmt1->execute();
$result1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
// 查询第二个数据库的数据
$stmt2 = $dbh2->prepare("SELECT FROM table2");
$stmt2->execute();
$result2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
// 处理结果
foreach ($result1 as $row1) {
echo "数据1: " . $row1["column1"] . "n";
}
foreach ($result2 as $row2) {
echo "数据2: " . $row2["column1"] . "n";
}
} catch (PDOException $e) {
echo '连接失败: ' . $e->getMessage();
}
// 关闭数据库连接
$dbh1 = null;
$dbh2 = null;
?>
使用Python连接两个数据库
使用ODBC连接
import pyodbc
# 连接第一个数据库
conn1 = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=db1_name;UID=user;PWD=password')
cursor1 = conn1.cursor()
# 连接第二个数据库
conn2 = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=db2_name;UID=user;PWD=password')
cursor2 = conn2.cursor()
# 查询第一个数据库的数据
cursor1.execute("SELECT FROM table1")
rows1 = cursor1.fetchall()
for row in rows1:
print("数据1: ", row)
# 查询第二个数据库的数据
cursor2.execute("SELECT FROM table2")
rows2 = cursor2.fetchall()
for row in rows2:
print("数据2: ", row)
# 关闭数据库连接
conn1.close()
conn2.close()
使用JDBC连接（Java）
import java.sql.;
public class ConnectDatabase {
public static void main(String[] args) {
try {
// 连接第一个数据库
Connection conn1 = DriverManager.getConnection("jdbc:mysql://server_name/db1_name", "user", "password");
Statement stmt1 = conn1.createStatement();
ResultSet rs1 = stmt1.executeQuery("SELECT FROM table1");
while (rs1.next()) {
System.out.println("数据1: " + rs1.getString("column1"));
}
rs1.close();
stmt1.close();
conn1.close();
// 连接第二个数据库
Connection conn2 = DriverManager.getConnection("jdbc:mysql://server_name/db2_name", "user", "password");
Statement stmt2 = conn2.createStatement();
ResultSet rs2 = stmt2.executeQuery("SELECT FROM table2");
while (rs2.next()) {
System.out.println("数据2: " + rs2.getString("column1"));
}
rs2.close();
stmt2.close();
conn2.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
使用Spring Boot配置两个数据源
在Spring Boot项目中，可以通过配置文件和注解来配置多个数据源,以下是一个示例：
修改application.yml文件
server:
port: 7101
spring:
jpa:
show-sql: true
datasource:
test1:
driver-class-name: org.postgresql.Driver
jdbc-url: jdbc:postgresql://127.0.0.1:5432/test # 测试数据库1
username: root
password: root
test2:
driver-class-name: oracle.jdbc.driver.OracleDriver
jdbc-url: jdbc:oracle:thin:@127.0.0.1:8888:orcl # 测试数据库2
username: root
password: root
创建配置类DataSource1Config.java和DataSource2Config.java
package com.test.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.test.dao.test1", sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class DataSource1Config {
@Bean(name = "test1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test1")
@Primary
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test1SqlSessionFactory")
@Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:test1/.xml"));
return bean.getObject();
}
@Bean(name = "test1TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
package com.test.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.test.dao.test2", sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class DataSource2Config {
@Bean(name = "test2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test2")
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test2SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:test2/.xml"));
return bean.getObject();
}
@Bean(name = "test2TransactionManager")
public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test2SqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
使用MyBatis同时连接两个数据库
创建两个数据源和SqlSessionFactory
SqlSessionFactory firstSessionFactory = createFirstSessionFactory(); SqlSessionFactory secondSessionFactory = createSecondSessionFactory();
使用多个SqlSession操作多个数据库
SqlSession firstSqlSession = firstSessionFactory.openSession(); SqlSession secondSqlSession = secondSessionFactory.openSession(); FirstMapper firstMapper = firstSqlSession.getMapper(
原创文章，发布者：酷盾叔，转转请注明出处：https://www.kd.cn/ask/59783.html
