mysql py支持_pymysql常用操作

安装pymysql

pymysql是python3+的一个包(python2使用的是MySQLdb)，安装起来比较简单。

pip install pymysql

建立连接

import pymysql

conn = pymysql.connect(

host='XX.XX.XX.XX',

user='user_name',

password='passwd',

database='db_name',

charset='utf8')

测试连接

with conn.cursor() as cursor:

sql = "SELECT version()"

cursor.execute(sql)

res = cursor.fetchone()

print("MySQL version: %s" % res)

新建table(表名mytable2)

with conn.cursor() as cursor:

cursor.execute("DROP TABLE IF EXISTS mytable2") # 如果存在，删除同名table

sql = '''

create table mytable2 (

id int not null auto_increment primary key,

symbol char(6) not null,

name varchar(255) not null,

area varchar(255),

industry varchar(255),

listed_date varchar(255)

) engine=innodb default charset=utf8

'''

cursor.execute(sql)

print('create table ok!')

添加一条新记录

# 创建一条新的记录

with conn.cursor() as cursor:

sql = "INSERT INTO mytable2 (symbol,name,area,industry,listed_date) VALUES ('210099','test','浙江','电器仪表','20120127')"

cursor.execute(sql)

# 或者

# sql = "INSERT INTO mytable2 (symbol,name,area,industry,listed_date) VALUES (%s,%s,%s,%s,%s)"

# values = ('220012','test2','江苏','太阳能','20150206') # 元祖或列表

# cursor.execute(sql, values)

# 连接完数据库并不会自动提交，所以需要手动 commit 你的改动

conn.commit()

print("insert done")

添加多条新记录

# 创建多条新的记录

with conn.cursor() as cursor:

sql = "INSERT INTO mytable2 (symbol,name,area,industry,listed_date) VALUES (%s,%s,%s,%s,%s)"

values = [('220016','test6','江苏','太阳能','20150206'),

('220017','test7','江苏','太阳能','20150206'),

('220018','test8','江苏','太阳能','20150206')]

cursor.executemany(sql, values)

# 连接完数据库并不会自动提交，所以需要手动 commit 你的改动

conn.commit()

print("insert done")

获取数据

with conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:

sql = "SELECT * FROM mytable2"

cursor.execute(sql)

res = cursor.fetchall()

data = pd.DataFrame(res)

data

或者

sql = "SELECT * from mytable2"

data = pd.read_sql(sql,conn)

data

常用命令

SHOW TABLES #查看所有表

SHOW COLUMNS FROM table_name #查看表字段

DESCRIBE table_name #查看表字段(也可以DESC table_name)