python连接MySQL

一、通过 mysql-connector 连接

安装 pip install mysql-connector-python

import mysql.connector
cnx = mysql.connector.connect(
    user='root', password='',
    host='127.0.0.1', database='mysql')

1. 用cursor使用sql语句

cursor = cnx.cursor()  # 实例化
cursor.execute('select user, host from user') # sql语句
result = cursor.fetchall() # 查询结果
print(result)
cnx.close()

2. pandas读取

query=pd.read_sql_query(
    'select user, host from user',cnx)  # sql语句
query 
print(query) # pandas的dataframe
cnx.close()

二、通过 ipython-sql 和pymysql连接

在Anaconda环境下运行， 安装完成后，可以用%sql或%%sql在jupyter-notebook中使用sql语句

conda install -c conda-forge ipython-sql
conda install pymysql # 和mysql-connector-python一样也是连接器

1. 非jupyter环境

import pymysql
conn = pymysql.connect(host='localhost',
    port=3306, user='root',
    passwd='1234', db='mysql')

a. 用游标访问

cursor=conn.cursor() #创建游标
# 执行SQL, 并返回行数, 用游标执行
num_rows = cursor.execute("select user, host from user")
print(num_rows)
print(cursor.fetchall())

b.用pandas

df=pd.read_sql('select user, host from user', conn)
print(type(df))
print(df.loc[:,"user"])

2. jupyter环境，魔法函数

%load_ext sql
%sql mysql+pymysql://root:1234@localhost/mysql
%config SqlMagic.feedback=False # 打开 feedback，不提示表的变化
# 定义别名
%alias_magic -p "root@localhost" s sql

# 运行单行sql语句
%s select user, host from user;
## 运行多行sql语句
%%s
select user, host from user;
select count(*) as 用户数 from user;

## 用pandas
import pandas as pd
rs = %s select user, host from user;
print(type(rs))
df=rs.DataFrame()
print(type(df))
print(df.iloc[:,1])