如何在Navicat Premium的MySQL数据库中导入外部*.sql文件

刺骨的言语ヽ痛彻心扉 2022-05-14 14:50 275阅读 0赞

1.sql文件的导入

参考：[https://blog.csdn.net/itas109/article/details/41979311?utm\_source=blogxgwz0][https_blog.csdn.net_itas109_article_details_41979311_utm_source_blogxgwz0]

[https://blog.csdn.net/Kobe\_WEZ/article/details/82690847][https_blog.csdn.net_Kobe_WEZ_article_details_82690847]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bGlhcmp1bGlhcg_size_27_color_FFFFFF_t_70][]

总结：（1）新建数据库  （2）明确连接属性 （3）“运行SQL文件” 导入文件地址，确认编码信息等  （4）刷新

2.pycharm中读取数据库中的sql文件时出错：

File "C:\\Users\\Administrator\\AppData\\Roaming\\Python\\Python35\\site-packages\\sqlalchemy\\engine\\default.py", line 412, in connect  
    return self.dbapi.connect(\*cargs, \*\*cparams)  
  File "D:\\应用软件\\python\\lib\\site-packages\\pymysql-0.9.2-py3.5.egg\\pymysql\\\_\_init\_\_.py", line 94, in Connect  
  File "D:\\应用软件\\python\\lib\\site-packages\\pymysql-0.9.2-py3.5.egg\\pymysql\\connections.py", line 327, in \_\_init\_\_  
  File "D:\\应用软件\\python\\lib\\site-packages\\pymysql-0.9.2-py3.5.egg\\pymysql\\connections.py", line 598, in connect  
  File "D:\\应用软件\\python\\lib\\site-packages\\pymysql-0.9.2-py3.5.egg\\pymysql\\connections.py", line 856, in \_request\_authentication  
  File "D:\\应用软件\\python\\lib\\site-packages\\pymysql-0.9.2-py3.5.egg\\pymysql\\connections.py", line 683, in \_read\_packet  
  File "D:\\应用软件\\python\\lib\\site-packages\\pymysql-0.9.2-py3.5.egg\\pymysql\\protocol.py", line 220, in check\_error  
  File "D:\\应用软件\\python\\lib\\site-packages\\pymysql-0.9.2-py3.5.egg\\pymysql\\err.py", line 109, in raise\_mysql\_exception  
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1045, "Access denied for user 'root'@'localhost' (using password: YES)") (Background on this error at: http://sqlalche.me/e/e3q8)

其原因是：建立数据库连接的代码中密码的地方写错，记得要改成自己的密码哦。

3.[python3.\*报“ImportError: No module named ‘MySQLdb'”][python3._ImportError_ No module named _MySQLdb]

[https://www.cnblogs.com/TaleG/p/6735099.html][python3._ImportError_ No module named _MySQLdb]

不过最后代码实现时并未加

import pymysql

pymysql.install\_as\_MySQLdb()

也是可以实现的！

4.读取数据库中数据的代码演示

import pandas as pd
    import numpy as np
    #import pymysql
    #pymysql.install_as_MySQLdb()
    
    from sqlalchemy import create_engine   #需下载Flask-SQLAlchemy
    #创建一个mysql连接器，用户名为root，密码为1234，地址为127.0.0.1，数据库名称为testdb，编码为utf8  +pymysql
    engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:\
    3306/testdb?charset=utf8')
    detail = pd.read_sql_table('meal_order_detail1',con = engine)
    detailGroup = detail[['order_id','counts',
          'amounts']].groupby(by = 'order_id')
    print('分组后的订单详情表为：',detailGroup)

[https_blog.csdn.net_itas109_article_details_41979311_utm_source_blogxgwz0]: https://blog.csdn.net/itas109/article/details/41979311?utm_source=blogxgwz0
[https_blog.csdn.net_Kobe_WEZ_article_details_82690847]: https://blog.csdn.net/Kobe_WEZ/article/details/82690847
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bGlhcmp1bGlhcg_size_27_color_FFFFFF_t_70]: /images/20220504/ce4c4e41cdaa42beb99ccc0e94666ad8.png
[python3._ImportError_ No module named _MySQLdb]: https://www.cnblogs.com/TaleG/p/6735099.html