python学习之MySQL

目录

 

创建数据库

创建一个表

插入数据

更新数据

删除数据

查询数据

---

创建数据库

import pymysql
#连接数据库
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database version:',data)
#创建数据库
cursor.execute('CREATE DATABASE spiders DEFAULT CHARACTER SET utf8')
db.close()

创建一个表

import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor = db.cursor()
sql = 'create table if not exists students (id varchar(255) not null,name varchar(255) not null,age int not null,primary key(id))'
#实行sql语句
cursor.execute(sql)
cursor.close()

插入数据

import pymysql
id = '20190228'
name = 'caiercong'
age = 105
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor = db.cursor()
sql = 'insert into students(id,name,age) values(%s,%s,%s)'
try:
    cursor.execute(sql,(id,name,age))
    db.commit()
except:
    print("fault")
    db.rollback()
db.close()

更新数据

import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor = db.cursor()
sql = 'update students set age=%s where name=%s'
try:
    cursor.execute(sql,(22,'caiercong'))
    db.commit()
except:
    print("fault")
    db.rollback()
db.close()

删除数据

import pymysql
db=pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor=db.cursor()
table='students'
condition='age>30'
sql='delete from {table} where {condition}'.format(table=table,condition=condition)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

查询数据

import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor=db.cursor()
sql = 'select * from students  where age> 10'
try:
    print('sd')
    cursor.execute(sql)
    print("Count:",cursor.rowcount)
    one=cursor.fetchone()
    print("one:",one)
    results = cursor.fetchall()
    print("results:",results)
    print('Results Type:',type(results))
    for row in results:
        print(row)
except:
    print("error")
db.close()