1. 简介
对于一些需要数据迁移的项目,我们常常需要将数据从一个数据库迁移到另一个数据库。本文将以 MSSQL 数据库和 MySQL 数据库为例,介绍如何通过连接两个数据库来进行数据迁移。
2. 安装odbc驱动
2.1 下载ODBC驱动程序
首先,我们需要在服务器上安装ODBC驱动程序。在Windows操作系统下,可以在ODBC官网上下载ODBC驱动程序。以MSSQL Server为例,我们需要下载ODBC Driver for SQL Server。而对于MySQL,我们需要下载ODBC Connector。
2.2 安装驱动程序
安装驱动程序的过程简单明了,通常只需要按照提示进行下一步即可。例如,在Windows上安装ODBC驱动程序时,我们可以依次选择“开始”菜单 -> “控制面板” -> “管理工具” -> “ODBC 数据源(32位)” -> “驱动程序” -> “添加” -> “Microsoft ODBC Driver For SQL Server”(或“MySQL ODBC 驱动程序”) -> “完成”,即可完成ODBC驱动的安装。
3. 连接数据库
在安装好ODBC驱动后,我们需要通过ODBC连接到数据库。下面,我们分别介绍连接 MSSQL 和连接 MySQL 的方法。
3.1 连接MSSQL
连接MSSQL需要提供相应的信息,包括数据库服务器地址、数据库名称、登录名和密码等。我们可以在需要连接的服务器上创建一个DSN(Dynamic Source Name),而DSN中包含了连接MSSQL所需的所有信息。
我们可以通过以下步骤来创建一个DSN:
以管理员身份登陆计算机。
打开“ODBC 数据源(32位)”。
在“系统 DSN”选项卡下,单击“添加”按钮,选择要连接的驱动程序。
在弹出的创建新数据源窗口中,设置“名称”、“描述”和“服务器”的值。
打开“网络库”选项卡,设置“协议”、 “Pipe 名称”和“端口”等参数。
打开“选项”选项卡,设置“默认数据库”和“语言”等参数。
单击“测试连接”按钮,检查设置是否正确。
单击“确定”按钮,完成DSN的创建。
当我们需要在代码中连接MSSQL时,可以使用以下代码:
import pyodbc
server = 'myserver'
database = 'mydb'
username = 'myusername'
password = 'mypassword'
cnxn = pyodbc.connect('DRIVER={ODBC Driver for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
cursor = cnxn.cursor()
3.2 连接MySQL
与连接MSSQL不同,连接MySQL需要提供的信息有些不同。我们需要提供MySQL服务器的地址、端口、数据库名称、登录名和密码等信息。
在Windows上,我们可以通过ODBC来连接MySQL。首先,需要在Windows上安装MySQL Connector/ODBC。安装完成后,按照以下步骤操作即可完成ODBC连接MySQL:
以管理员身份登陆计算机。
打开“ODBC 数据源(32位)”。
在“系统 DSN”选项卡下,单击“添加”按钮,选择“MySQL ODBC 8.0 Unicode Driver”。
在弹出的创建新数据源窗口中,设置“名称”、“描述”和“服务器”的值。
打开“详细信息”选项卡,“服务器”栏填入MySQL服务器的地址、“端口”栏填写MySQL服务器的端口、“用户名”和“密码”填写MySQL数据库的登录名和密码、“数据库”填写需要访问的数据库名称。
单击“测试连接”按钮,检查设置是否正确。
单击“确定”按钮,完成DSN的创建。
在Python代码中连接MySQL时,可以使用以下代码:
import pyodbc
dsn = 'MySQL64'
cnxn = pyodbc.connect('DSN=' + dsn)
cursor = cnxn.cursor()
4. 数据迁移
通过ODBC连接MSSQL和MySQL之后,我们可以使用Python代码来实现数据的迁移。具体来说,需要完成以下步骤:
从MSSQL中读取数据
将数据写入到MySQL中
4.1 从MSSQL中读取数据
从MSSQL中读取数据需要使用 pyodbc 模块。例如,我们需要将MSSQL中的一张表中的数据读取到一个列表中,代码如下:
import pyodbc
server = 'myserver'
database = 'mydb'
username = 'myusername'
password = 'mypassword'
cnxn = pyodbc.connect('DRIVER={ODBC Driver for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
cursor = cnxn.cursor()
result = []
cursor.execute("SELECT * FROM mytable")
row = cursor.fetchone()
while row:
result.append(row)
row = cursor.fetchone()
在这段代码中,我们首先连接数据库,然后打开一个光标,并执行了一条查询语句。通过“fetchone()”方法,我们一次获取一行记录,并将其加入到一个列表中。
4.2 将数据写入到MySQL中
将数据从MSSQL中读取出来后,接下来就是将这些数据迁移到MySQL数据库中。这个过程较为简单,只需要使用 MySQLdb 模块,将每一行数据插入到MySQL中即可。
以下代码展示了如何将一个列表中的数据迁移到MySQL中:
import MySQLdb
host = '127.0.0.1'
port = 3306
user = 'root'
password = '123456'
db = 'mydb'
conn = MySQLdb.connect(host=host,port=port,user=user,passwd=password,db=db,charset="utf8")
cursor = conn.cursor()
for row in result:
sql = "INSERT INTO mytable (col1, col2, col3, col4) VALUES ('%s', '%s', '%s', '%s')" % row
cursor.execute(sql)
cursor.close()
conn.commit()
在这段代码中,我们首先连接MySQL,然后枚举列表中的每一行数据,并将其插入到MySQL的指定表中。
5. 总结
本文介绍了如何通过ODBC连接MSSQL和MySQL,以及如何将数据从MSSQL中读取出来迁移到MySQL中。通过这些方法,我们可以轻松地完成数据迁移的工作,方便快捷。