python连接mysql学习

query

import pymysql
import numpy as np
#打开数据库连接
db = pymysql.connect('localhost','root','111111','hello')

#创建游标对象
cursor = db.cursor()

# Sql预处理语句之选择收入超过1000的记录
# sql = """SELECT * FROM student"""
# realname = "lxs"
# phone = 13678101715 + 5
# email = "lxs" + "haha" +"@126.com"
# sql = """insert into table_a (realname,phone,email) values ('+realname+','+str(phone)+ ','+email)'"""
#     # f.write(sql)
# cursor.execute(sql)
# sql2 = "INSERT INTO student(id,`name`,sex)VALUES('3','{}','女')"#sql语句
sql = "insert into table_a (realname,phone,email) values (%d,{},{}})".format(realname,phone,email)
# for i in range(1,51):
#     str_i = str(i)
#     realname = "lxs"+str_i
#     phone = 13678101715 + i
#     email = "lxs" + str_i +"@126.com"
#     sql = "insert into table_a ('realname','phone','email') values ('{}','{}','{}')".format(realname,phone,email)
#     # f.write(sql)
#     cursor.execute(sql)

# 获取所有记录列表
# results = cursor.fetchall()
# print(np.array(results))

db.close()

test1

import pymysql

#打开数据库连接
db = pymysql.connect('localhost','root','111111','hello')

#创建游标对象
cursor = db.cursor()

#sql预处理语句之创建表格
# sql = """CREATE TABLE EMPLOYEE (
#          NAME  CHAR(20),
#          AGE INT,
#          SEX CHAR(1),
#          INCOME FLOAT )"""
#
# cursor.execute(sql)

#sql预处理语句之往表格中插入数据
# sql = """INSERT INTO EMPLOYEE(NAME,AGE, SEX, INCOME)
#          VALUES ('KING', 200, 'M', 5000)"""
sql = """INSERT INTO EMPLOYEE(NAME,AGE, SEX, INCOME)
            values ('{}','{}','{}','{}')"""
sql=sql.format('aaAA',22,'M',2222)
         # VALUES ('KING', 200, 'M', 5000)"""
cursor.execute(sql)

#提交到数据库执行，代表一个事务的结束
db.commit()

#关闭数据库
db.close()

test2

import pymysql

#打开数据库连接
db = pymysql.connect('localhost','root','111111','hello')

#创建游标对象
cursor = db.cursor()

#sql预处理语句之创建表格
# sql = """CREATE TABLE EMPLOYEE (
#          NAME  CHAR(20),
#          AGE INT,
#          SEX CHAR(1),
#          INCOME FLOAT )"""
#
# cursor.execute(sql)

#sql预处理语句之往表格中插入数据
# sql = """INSERT INTO EMPLOYEE(NAME,AGE, SEX, INCOME)
#          VALUES ('KING', 200, 'M', 5000)"""
sql = """INSERT INTO EMPLOYEE(NAME,AGE, SEX, INCOME)
         select 'a',10,'m',200 union 
         select 'aa',1110,'m',200 union 
         select 'aaa',110,'m',200 union 
         select 'aaaa',110,'m',200 """

         # VALUES ('KING', 200, 'M', 5000)"""
cursor.execute(sql)

#提交到数据库执行，代表一个事务的结束
db.commit()

#关闭数据库
db.close()

test3

import pymysql

#打开数据库连接
db = pymysql.connect('localhost','root','111111','hello')

#创建游标对象
cursor = db.cursor()

#sql预处理语句之创建表格
# sql = """CREATE TABLE EMPLOYEE (
#          NAME  CHAR(20),
#          AGE INT,
#          SEX CHAR(1),
#          INCOME FLOAT )"""
#
# cursor.execute(sql)

#sql预处理语句之往表格中插入数据
# sql = """INSERT INTO EMPLOYEE(NAME,AGE, SEX, INCOME)
#          VALUES ('KING', 200, 'M', 5000)"""
sql = """INSERT INTO EMPLOYEE(NAME,AGE, SEX, INCOME)
         select 'a',10,'m',200 union 
         select 'aa',1110,'m',200 union 
         select 'aaa',110,'m',200 union 
         select 'aaaa',110,'m',200 """

         # VALUES ('KING', 200, 'M', 5000)"""
cursor.execute(sql)

#提交到数据库执行，代表一个事务的结束
db.commit()

#关闭数据库
db.close()

随机生成且插入

import pymysql

#打开数据库连接
db = pymysql.connect('localhost','root','111111','hello')

#创建游标对象
cursor = db.cursor()

#sql预处理语句之创建表格


#sql预处理语句之往表格中插入数据
for i in range(1,155):
    str_i = str(i)
    realname = "lxs"+str_i
    phone = 13678101715 + i
    email = "lxs" + str_i +"@126.com"
    # sql = 'insert into table_a (realname,phone,email,sign,event_id) values ("'+realname+'",' +str(phone)+ ',"'+email+'",0,1);'

    sql = """INSERT INTO table_a(realname,phone, email)VALUES ('{}', '{}', '{}')""".format(realname,phone,email)

    cursor.execute(sql)

#提交到数据库执行，代表一个事务的结束
db.commit()

#关闭数据库
db.close()