1.下载pymysql模块
pymysql模块对python3的支持比较好
安装命令:pip install pymysql
2.新建一个数据库
会用可视化工具hedisql或者navicat创建数据库则跳过此步
打开cmd或者PoweShell
打开方式是windows+R组合键
具体操作为:在按住windows键的情况,再按R键
windows键在键盘左下方,下方第一行从左往右数第二个有windows图标的键就是windows键
呼唤出窗口如下图所示:
![图片.png-7kB](https://yqfile.alicdn.com/img_a0ed3987068e686fc2856e74dc742fa4.png?x-oss-process=image/resize,w_1400/format,webp)
图片.png-7kB
在 运行窗口中输入powershell
![图片.png-6.9kB](https://yqfile.alicdn.com/img_e216bb026b1e1edfd6d8bff78844c32f.png?x-oss-process=image/resize,w_1400/format,webp)
图片.png-6.9kB
点击上图中的确定后,如下图所示。
![图片.png-4kB](https://yqfile.alicdn.com/img_aae8a04ac2067d661bbb71926718b4e3.png?x-oss-process=image/resize,w_1400/format,webp)
图片.png-4kB
在PowerShell中输入命令并运行: mysql -u root -p
![图片.png-4.6kB](https://yqfile.alicdn.com/img_823249decf3c25d8f01dd78ba2e02510.png?x-oss-process=image/resize,w_1400/format,webp)
图片.png-4.6kB
输入root用户对应的密码后,运行如下图所示:
![图片.png-11.6kB](https://yqfile.alicdn.com/img_f642e531818161371367b62030161c44.png?x-oss-process=image/resize,w_1400/format,webp)
图片.png-11.6kB
在mysql中输入命令并运行: create database pydb
![图片.png-9.5kB](https://yqfile.alicdn.com/img_6a3269f132a87a0ad3225b1cc815a28d.png?x-oss-process=image/resize,w_1400/format,webp)
图片.png-9.5kB
出现上图所示,则成功创建数据库。
3.编辑py文件
3.1插入数据
import pymysql
if __name__ == "__main__":
args = dict(
host = 'localhost',
user = 'root',
passwd = '...your password',
db = 'pydb',
charset = 'utf8'
)
conn = pymysql.connect(**args)
cursor = conn.cursor()
cursor.execute("drop table if exists test")
cursor.execute("create table test(id int,name varchar(20))")
cursor.execute('insert into test values(1,"小明")')
cursor.execute('insert into test values(2,"小红")')
cursor.execute('insert into test values(3,"小王")')
conn.commit()
conn.close()
3.2查询数据
cursor.fetchall()函数示例:
import pymysql
if __name__ == "__main__":
args = dict(
host = 'localhost',
user = 'root',
passwd = '...your password',
db = 'pydb',
charset = 'utf8'
)
conn = pymysql.connect(**args)
cursor = conn.cursor()
cursor.execute("select * from test")
data_list = cursor.fetchall()
for data in data_list:
print(data)
conn.commit()
conn.close()
上面一段代码的运行结果如下:
(1, '小明')
(2, '小红')
(3, '小王')
cursor.fetchmany()函数示例:
import pymysql
if __name__ == "__main__":
args = dict(
host='localhost',
user='root',
passwd='...your password',
db='pydb',
charset='utf8'
)
conn = pymysql.connect(**args)
cursor = conn.cursor()
cursor.execute("select * from test")
data_list = cursor.fetchmany(2)
for data in data_list:
print(data)
conn.commit()
conn.close()
上面一段代码的运行结果如下:
(1, '小明')
(2, '小红')
小结
关键是mysql的sql语句要熟练,python连接mysql数据库后对相应的数据库利用cursor.execute()方法进行操作。
完成操作后要提交,即conn.commit();关闭连接,即conn.close()