python3数据库框架_python3之pymysql

pymsql是Python中操作MySQL的模块并且只有在Python3.0版本中才存在，其使用方法和MySQLdb几乎相同。

下载安装pymsql模块

pip3 install pymysql

操作前准备

#1.创建数据库

mysql> create database mydb;

mysql> use mydb;

#2.创建表

create table students

(

id int not null auto_increment primary key,

name char(8) not null,

sex char(4) not null,

age tinyint unsigned not null,

tel char(13) null default "-"

);

#3.插入两条数据

mysql> insert into students values(1,"jack","M",20,"stu");

mysql> insert into students values(2,"xander","M",20,"stu");

1.执行SQL

import pymysql

# 创建mysql连接(socket)，client --> server

"""

host = "Server端IP"

port = "Server端口"

user = "Server端用户"

passwd = "Server端密码"

db = "Server端数据库名"

"""

conn = pymysql.connect(host="10.0.0.51",port=3306,user="root",passwd="123456",db="mydb")

#创建游标(光标位置)，相当于是socket上的实例

cursor = conn.cursor()

# 执行SQL语句，并返回受影响的行数

"""

cursor.execute("需要执行的sql语句")

"""

effect_row = cursor.execute("select * from students")

print(cursor.fetchone()) # 获取第一条数据

print(cursor.fetchone()) # 获取第二条数据

print("------")

print(cursor.fetchall()) # 获取所有数据(从未被获取的数据中读出来)

# 提交SQL语句执行结果，不然无法保存新建或者修改的数据

conn.commit()

# 关闭游标

cursor.close()

# 关闭MySQL连接

conn.close()

2.插入数据

import pymysql

# 创建mysql连接(socket)，client --> server

conn = pymysql.connect(host="10.0.0.51",port=3306,user="root",passwd="123456",db="mydb")

#创建游标(光标位置)，相当于是socket上的实例

cursor = conn.cursor()

# 定义需要插入的数据

data = [

("Daniel","M","21","stu"),

("Noah","M","25","stu"),

("David", "M", "25", "stu")

]

# 执行插入SQL语句

cursor.executemany("insert into students(name,sex,age,tel) values(%s,%s,%s,%s)" ,data)

# 提交SQL语句执行结果，不然无法保存新建或者修改的数据

conn.commit()

# 关闭游标

cursor.close()

# 关闭MySQL连接

conn.close()