python-pymysql-基本使用
1. pymysql-基本使用
-
创建表
[root@python tmp]# mysql -h 127.0.0.1 -u test -p'Test@963852' Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use test Database changed # 执行以下创建表 MariaDB [test]> show tables; Empty set (0.00 sec) MariaDB [test]> create table user( -> id bigint unsigned auto_increment, -> username varchar(100) not null, -> password varchar(100) not null, -> primary key (id) -> )ENGINE=innoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.05 sec)
-
使用pymysql连接数据库
#!/usr/bin/env python3 # _*_ coding: utf-8 _*_ # Author:shichao # File: .py import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='test', charset='utf8', cursorclass=pymysql.cursors.DictCursor) try: with conn.cursor() as cursor: # 定义执行sql变量 sql = "insert into user(username, password) values('test1', '123456')" # 执行一条sql记录 cursor.execute(sql) # 写入到数据库 conn.commit() with conn.cursor() as cursor: # 定义执行sql变量 sql = "select id,username,password from user" # 执行一条记录 cursor.execute(sql) # 读取一条记录 result = cursor.fetchone() print(result) finally: # 执行完成后,执行关闭连接 conn.close()