使用python操作数据库

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('create table user (id int(10) primary key name varchar(20))')
cursor.close()
conn.close()

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('insert into user (id, name) values (1,"MRSOFT")')
cursor.execute('insert into user (id, name) values (2,"Andy")')
cursor.execute('insert into user (id, name) values (3,"明日科技小助手")')
cursor.close()
conn.commit()
conn.close()

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('select * from user')
result1=cursor.fetchone()
print(result1)
cursor.close()
conn.close()

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('select * from user')
result2=cursor.fetchmany(2)
print(result2)
cursor.close()
conn.close()

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('select * from user')
result3=cursor.fetchall()
print(result3)
cursor.close()
conn.close()

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('update user set name = ? where id = ?',('MR',1))
cursor.execute('select * from user')
result=cursor.fetchall()
print(result)
cursor.close()
conn.commit()
conn.close()

import sqlite3
conn=sqlite3.connect('mrsoft.db')
cursor=conn.cursor()
cursor.execute('delete from user where id =?',(1,))
cursor.execute('select * from user')
result=cursor.fetchall()
print(result)
cursor.close()
conn.commit()
conn.close()

import pymysql
db=pymysql.connect(host='localhost',user='root',password='crz13714817086',database='aaa')
cursor=db.cursor()
cursor.execute('SELECT VERSION()')
data=cursor.fetchone()
print('Database version : %s' %data)
db.close

import pymysql
db=pymysql.connect(host='localhost',user='root',password='crz13714817086',database='aaa')
cursor=db.cursor()
cursor.execute('DROP TABLE IF EXISTS books')
sql='''
CREATE TABLE book (
    id int(8) NOT NULL AUTO_INCREMENT,name varchar(50) NOT NULL,category varchar(50) NOT NULL,price decimal(10,2) DEFAULT NULL,publish_time date DEFAULT NULL,  PRIMARY KEY (id)) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
'''

cursor.execute(sql)
db.close()

import pymysql
db=pymysql.connect(host='localhost',user='root',password='crz13714817086',database='aaa',charset='utf8')
cursor=db.cursor()
data=[('零基础学Python','python','79.80','2018-5-20'),('python从入门到精通','python','69.80','2018-6-18'),('零基础学PHP','PHP','69.80','2017-5-21'),('PHP项目开发实战入门','PHP','79.80','2016-5-21'),('零基础学Java','Java','69.80','2017-5-21')]
try:
    cursor.executemany('insert into book(name, category, price, publish_time) values (%s,%s,%s,%s)',data)
    db.commit()
except:
    db.rollback()
        
db.close()