Python通过ODBC访问MySQL

Python通过ODBC访问MySQL

安装MySQL

[root@train ~]# yum install mariadb-server.x86_64
Loaded plugins: fastestmirror, langpacks
base                                                                                                                      | 3.6 kB  00:00:00     
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package mariadb-server.x86_64 1:5.5.52-1.el7 will be installed
--> Processing Dependency: mariadb(x86-64) = 1:5.5.52-1.el7 for package: 1:mariadb-server-5.5.52-1.el7.x86_64
--> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.52-1.el7.x86_64
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.52-1.el7 will be installed
---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================
 Package                                Arch                           Version                                Repository                    Size
=================================================================================================================================================
Installing:
 mariadb-server                         x86_64                         1:5.5.52-1.el7                         base                          11 M
Installing for dependencies:
 mariadb                                x86_64                         1:5.5.52-1.el7                         base                         8.7 M
 perl-DBD-MySQL                         x86_64                         4.023-5.el7                            base                         140 k

Transaction Summary
=================================================================================================================================================
Install  1 Package (+2 Dependent packages)

Total download size: 20 M
Installed size: 104 M
Is this ok [y/d/N]: y
Downloading packages:
-------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                             26 MB/s |  20 MB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : 1:mariadb-5.5.52-1.el7.x86_64                                                                                                 1/3 
  Installing : perl-DBD-MySQL-4.023-5.el7.x86_64                                                                                             2/3 
  Installing : 1:mariadb-server-5.5.52-1.el7.x86_64                                                                                          3/3 
  Verifying  : 1:mariadb-server-5.5.52-1.el7.x86_64                                                                                          1/3 
  Verifying  : perl-DBD-MySQL-4.023-5.el7.x86_64                                                                                             2/3 
  Verifying  : 1:mariadb-5.5.52-1.el7.x86_64                                                                                                 3/3 

Installed:
  mariadb-server.x86_64 1:5.5.52-1.el7                                                                                                           

Dependency Installed:
  mariadb.x86_64 1:5.5.52-1.el7                                        perl-DBD-MySQL.x86_64 0:4.023-5.el7                                       

Complete!
[root@train ~]#

创建用户并设置密码和访问权限

MariaDB [(none)]> grant all privileges on *.* to 'jihui'@'%' identified by '111111';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

下载对应版本的ODBC

https://downloads.mysql.com/archives/c-odbc/

配置ODBC

测试成功

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table t_user(f_userid int, f_username varchar(20));
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t_user values(1, 'mysql');
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from t_user;
+----------+------------+
| f_userid | f_username |
+----------+------------+
|        1 | mysql      |
+----------+------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t_user values(2, 'mariadb');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from t_user;
+----------+------------+
| f_userid | f_username |
+----------+------------+
|        1 | mysql      |
|        2 | mariadb    |
+----------+------------+
2 rows in set (0.00 sec)

MariaDB [test]> 

Python

import pyodbc

conn = pyodbc.connect('DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=192.168.3.6;PORT=3306;DATABASE=test;UID=jihui;PWD=111111')
# conn = pyodbc.connect('DSN=myodbc;PWD=111111')

cursor = conn.cursor()

cursor.execute("select * from t_user")

row = cursor.fetchone()

# if row:
#     print(row)
while row:
    print(row)

    row = cursor.fetchone()

conn.close()

Result

D:\Users\86138\Anaconda3\python.exe C:/Users/86138/PycharmProjects/mydb/gbase8s.py
(1, 'mysql')
(2, 'mariadb')

Process finished with exit code 0