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