Python 连接 MYSQL

前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到网站：https://www.captainai.net/dongkelun

前言

总结记录Python 连接 MYSQL

代码

from sqlalchemy import create_engine
import pandas as pd


def get_db(url):
    return create_engine(url, connect_args={'charset': 'utf8'}, encoding='utf8')


def get_df(sql, url="mysql://root:Root-123456@192.168.44.128:3306/test"):
    db = get_db(url)
    df = pd.read_sql_query(sql, db)
    return df


if __name__ == '__main__':
    db_url = "mysql://root:Root-123456@192.168.44.128:3306/test"
    data1 = get_df("select * from test", db_url)
    data2 = get_df("select * from test where id BETWEEN '%s' and '%s'" % (6, 10), db_url)

    print(data1.head(10))
    print(data2)

结果 :

   id  isdeal
0   1       0
1   2       0
2   3       0
3   4       0
4   5       0
5   6       0
6   7       0
7   8       0
8   9       0
9  10       0
   id  isdeal
0   6       0
1   7       0
2   8       0
3   9       0
4  10       0

异常

执行上面代码时可能会抛出下面异常

ModuleNotFoundError: No module named 'MySQLdb'

解决

$ pip install mysqlclient
Collecting mysqlclient
  Downloading https://files.pythonhosted.org/packages/5d/b3/a753b836eab49c865651eb2bc7203d070c58e5f22b33015b48fa6112bd7a/mysqlclient-1.4.6-cp37-cp37m-win_amd64.whl (262kB)
Installing collected packages: mysqlclient
Successfully installed mysqlclient-1.4.6

然后再执行上面代码就可以了