创建数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

import pymysql

# 创建连接
conn = pymysql.connect(host='120.78.75.65', port=3306,
                     user='root', passwd='root', db='test')

# 创建游标
cursor = conn.cursor()

sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

# 执行SQL，并返回受影响行数，执行多次
effect_row = cursor.execute(sql)

# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭数据库连接
conn.close()

查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='test')

cursor = conn.cursor()
cursor.execute("select * from users")
 
# 获取剩余结果的第一行数据
row_1 = cursor.fetchone()

# 获取剩余结果前n行数据
row_2 = cursor.fetchmany(3)
 
# 获取剩余结果所有数据
row_3 = cursor.fetchall()
 
conn.commit()

cursor.close()
conn.close()