Windows7环境下安装PyMySQL（已安装Anaconda）

本人用的都是最新版本。安装pymysql 和mysql二个模块

1.先安装MySql，版本mysql-installer-community-8.0.14.0

2.Anaconda Navigator 版本1.9.6

 Anaconda安装

在右边输入mysqlm,搜索，然后安装4个打钩的。

注意：mysql-python不要选择（没什么用）

这样就安装完成，网上介绍的很多办法我在最新的版本中试试，不行。

这样就安装完成。包含pymysql 和mysql二个模块

4.测试

用Jupyter QtConsole 和Pycharm

测试模块：pymysql 和mysql

====================================================================

测试1：

import mysql.connector  as con
import pandas as pd

db=con.connect(user='root',password='root',database='new_futures')
#数据库位置：C:\ProgramData\MySQL\MySQL Server 8.0\Data

cursor=db.cursor()
cursor.execute('select * from m1809')
values=cursor.fetchall()
df=pd.DataFrame(values)
print(df)

cursor.close()  #True
db.close()

   0   1      2                   3           4        5       6       7   \
0   0  豆粕  m1809 2019-01-23 14:59:44  2018-07-09 14:59:44  3100.0  3150.0   
1   1  豆粕  m1809 2019-01-23 14:59:44  2018-07-10 14:59:44  3000.0  3000.0   
2   2  豆粕  m1809 2019-01-23 14:59:44  2019-01-23 14:59:44  2900.0  3050.0   
3   3  豆粕  m1809 2019-01-23 14:59:44  2018-07-12 14:59:44  3020.0  3150.0   
4   4  豆粕  m1809 2019-01-23 14:59:44  2018-07-13 14:59:44  3105.0  3159.0   

       8       9         10  
0  3000.0  3110.0  254672.0  
1  2900.0  2950.0  154672.0  
2  2920.0  2950.0  244670.0  
3  3000.0  3192.0  354677.0  
4  3004.0  3119.0  264678.0  

df.dtypes
Out[13]: 
0               int64
1              object
2              object
3      datetime64[ns]
4              object
5     timedelta64[ns]
6             float64
7             float64
8             float64
9             float64
10            float64
dtype: object

=========================================================

测试2：

import pymysql
import datetime ,time

dt=datetime.datetime.now()
dt_now=dt.strftime('%Y-%m-%d %H:%M:%S')
date_now=dt.strftime('%Y-%m-%d')
##time_now=dt.strftime('%H:%M:%S')
time_now=time.strftime('%H:%M:%S',time.localtime())

#------------------------------------------------------------------------------
#1. 连接数据库
db_conn = pymysql.connect(host = 'localhost', user= 'root', passwd = 'root',
                          database='new_futures',port=3306,charset='utf8')

# 获取操作游标
cursor = db_conn.cursor()
# 使用 execute 方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone 方法获取一条数据库。
dbversion = cursor.fetchone()

print ("Database version : %s " % dbversion) #Database version : 8.0.11
#------------------------------------------------------------------------------
#2. 创建数据库
cursor.execute("create database if not exists new_futures")

# 选择要操作的数据库
db_conn.select_db('new_futures');
cursor.execute("drop table if exists m1809")
db_conn.commit()
# 创建数据表SQL语句
sql = """CREATE TABLE if not exists m1809(
         No  INT NOT NULL,
         name CHAR(20) NOT NULL,
         code CHAR(10) NOT NULL,
         datetime DATETIME NOT NULL,
         date DATE NOT NULL,
         time TIME NOT NULL,
         open FLOAT NOT NULL,
         high FLOAT NOT NULL,
         low FLOAT NOT NULL,
         close FLOAT NOT NULL,
         vol FLOAT NOT NULL
          )"""
try:
    cursor.execute(sql)
except Exception as e:
    # Exception 是所有异常的基类，这里表示捕获所有的异常
    print ("Error to create table:", e)
##str_db=pymysql.STRING
# 插入数据
sql = """INSERT INTO m1809(No,name,code,datetime,date,time,open,high,low,close,vol)
         VALUES ('%d','%s', '%s','%s','%s', str_to_date(\'%s\','%%H:%%i:%%S'),'%f', '%f', '%f', '%f', '%f')"""
m1809s = (
        {"No": 0, "name": "豆粕", "code": 'm1809', "datetime": dt_now,"date": "2018-7-9", "time":time_now,"open": 3100, "high": 3150, "low": 3000, "close": 3110, "vol": 254672},
        {"No": 1, "name": "豆粕", "code": 'm1809', "datetime": dt_now,"date": "2018-7-10", "time":time_now, "open": 3000, "high": 3000, "low": 2900, "close": 2950, "vol": 154672},
        {"No": 2, "name": "豆粕", "code": 'm1809',"datetime": dt_now, "date": date_now, "time":time_now, "open": 2900, "high": 3050, "low": 2920, "close": 2950, "vol": 244670},
        {"No": 3, "name": "豆粕", "code": 'm1809',"datetime": dt_now, "date": "2018-7-12", "time":time_now, "open": 3020, "high": 3150, "low": 3000, "close": 3192, "vol": 354677},
        {"No": 4, "name": "豆粕", "code": 'm1809',"datetime": dt_now, "date": "2018-7-13", "time":time_now, "open": 3105, "high": 3159, "low": 3004, "close": 3119, "vol": 264678}
        )

try:
    # 清空表中数据
    cursor.execute("delete from m1809")
    # 执行 sql 插入语句
    for m1809 in m1809s:
        cursor.execute(sql % (m1809["No"], \
            m1809["name"], \
            m1809["code"], \
            m1809["datetime"], \
            m1809["date"], \
            m1809["time"], \
            m1809["open"], \
            m1809["high"], \
            m1809["low"], \
            m1809["close"], \
            m1809["vol"]))
    # 提交到数据库执行
    db_conn.commit()
    # 对于支持事务的数据库当游标建立时自动开始一个隐形数据库事务。
    # 用 commit 方法能够提交事物
except Exception as  e:
    # Rollback in case there is any error
    print ("Error to insert data:", e)
    #b_conn.rollback()

print( "Insert rowcount:", cursor.rowcount )
# rowcount 是一个只读属性，并返回执行execute(方法后影响的行数。)

# 数据库查询操作:
#    fetchone()      得到结果集的下一行
#    fetchmany([size=cursor.arraysize])  得到结果集的下几行
#    fetchall()      返回结果集中剩下的所有行
try:
    # 执行 SQL
    cursor.execute("select * from m1809")
    # 获取一行记录
    rs = cursor.fetchone()
    print( rs )
    # 获取余下记录中的 2 行记录
    rs = cursor.fetchmany(2)
    print( rs)
    # 获取剩下的所有记录
    ars =  cursor.fetchall()
    for rs in ars:
        print( rs)
    # 可以用 fetchall 获得所有记录，然后再遍历
except Exception as e:
    print ("Error to select:", e)

# 数据库更新操作
sql = "UPDATE m1809 SET vol = vol + 100000 WHERE date = '%s'" % ('2018-7-11')
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交到数据库执行
    db_conn.commit()
    cursor.execute("select * from m1809")
    ars =  cursor.fetchall()
    print ("After update: ------" )
    for rs in ars:
        print (rs )
except Exception as  e:
    # 发生错误时回滚
    print ("Error to update:", e )
    db_conn.rollback()

# 关闭数据库连接

db_conn.close()

Database version : 8.0.14 
Insert rowcount: 1
(0, '豆粕', 'm1809', datetime.datetime(2019, 1, 23, 17, 37, 28), datetime.date(2018, 7, 9), datetime.timedelta(0, 63448), 3100.0, 3150.0, 3000.0, 3110.0, 254672.0)
((1, '豆粕', 'm1809', datetime.datetime(2019, 1, 23, 17, 37, 28), datetime.date(2018, 7, 10), datetime.timedelta(0, 63448), 3000.0, 3000.0, 2900.0, 2950.0, 154672.0), (2, '豆粕', 'm1809', datetime.datetime(2019, 1, 23, 17, 37, 28), datetime.date(2019, 1, 23), datetime.timedelta(0, 63448), 2900.0, 3050.0, 2920.0, 2950.0, 244670.0))
(3, '豆粕', 'm1809', datetime.datetime(2019, 1, 23, 17, 37, 28), datetime.date(2018, 7, 12), datetime.timedelta(0, 63448), 3020.0, 3150.0, 3000.0, 3192.0, 354677.0)
(4, '豆粕', 'm1809', datetime.datetime(2019, 1, 23, 17, 37, 28), datetime.date(2018, 7, 13), datetime.timedelta(0, 63448), 3105.0, 3159.0, 3004.0, 3119.0, 264678.0)
After update: ------
(0, '豆粕', 'm1809', datetime.datetime(2019, 1, 23, 17, 37, 28), datetime.date(2018, 7, 9), datetime.timedelta(0, 63448), 3100.0, 3150.0, 3000.0, 3110.0, 254672.0)
(1, '豆粕', 'm1809', datetime.datetime(2019, 1, 23, 17, 37, 28), datetime.date(2018, 7, 10), datetime.timedelta(0, 63448), 3000.0, 3000.0, 2900.0, 2950.0, 154672.0)
(2, '豆粕', 'm1809', datetime.datetime(2019, 1, 23, 17, 37, 28), datetime.date(2019, 1, 23), datetime.timedelta(0, 63448), 2900.0, 3050.0, 2920.0, 2950.0, 244670.0)
(3, '豆粕', 'm1809', datetime.datetime(2019, 1, 23, 17, 37, 28), datetime.date(2018, 7, 12), datetime.timedelta(0, 63448), 3020.0, 3150.0, 3000.0, 3192.0, 354677.0)
(4, '豆粕', 'm1809', datetime.datetime(2019, 1, 23, 17, 37, 28), datetime.date(2018, 7, 13), datetime.timedelta(0, 63448), 3105.0, 3159.0, 3004.0, 3119.0, 264678.0)
C:\ProgramData\Anaconda3\envs\tcy_p36\lib\site-packages\pymysql\cursors.py:170: Warning: (1007, "Can't create database 'new_futures'; database exists")
  result = self._query(query)

5.pycharm配置