python-pymysql-基本使用

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()