pymysql连接mysql

pymysql的连接

连接数据库

import pymysql

settings = {
    'host':'192.168.137.108',
    'port':3306,
    'user':'tornado',
    'password':'tornado',
    'db':'tornado',
    'charset':'utf8'
}
conn = pymysql.connect(**settings)

print(conn)
#<pymysql.connections.Connection object at 0x000001D3141E5C88>

cursor = conn.cursor()
cursor.execute('show databases')
# one = cursor.fetchone()              #取出一条数据
all = cursor.fetchall()              #取出所有数据
# print(one)
print(all)

table = '''
create table user(
  id int PRIMARY KEY auto_increment,
  username VARCHAR(20) not null,
  password VARCHAR(20) not null
)
'''

'''
显示sql语句的方法一
mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


显示sql语句方法二
mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| password | varchar(20) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

'''
# cursor.execute(table)  #创建完成后注释掉，否则报错表存在

插入值

### 插入值
sql = 'insert into user(username,password) values (%s,%s)'
# cursor.execute(sql,('李四','qwe123'))
# cursor.executemany(sql,[('王五','abc123'),('赵六','333444')])
# conn.commit()
'''
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | zhangsan | qwe123   |
|  3 | zhangsan | qwe123   |
|  4 | 李四     | qwe123   |
|  5 | 王五     | abc123   |
|  6 | 赵六     | 333444   |
+----+----------+----------+

'''

修改值

sql2 = 'update user SET username=%s where id=%s'
# cursor.execute(sql2,('张三',2))
# conn.commit()
'''
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | 张三     | qwe123   |
|  3 | zhangsan | qwe123   |
|  4 | 李四     | qwe123   |
|  5 | 王五     | abc123   |
|  6 | 赵六     | 333444   |
+----+----------+----------+

'''

删除值

sql3 = 'delete from user  WHERE id=%s'
cursor.executemany(sql3,[(3,),(6,)])
conn.commit()

'''
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | 张三     | qwe123   |
|  4 | 李四     | qwe123   |
|  5 | 王五     | abc123   |
+----+----------+----------+

'''