python连接数据库速查示例

python连接数据库

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '123456'
DBNAME = 'dbtest'

db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)

新建一张新表

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '123456'
DBNAME = 'dbtest'

db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
cur = db.cursor()
cur.execute("DROP TABLE IF EXISTS Student")
cur.execute("CREATE TABLE Student(Name CHAR(20) NOT NULL, Email CHAR(20),Age int)")

向表中插入一条数据

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '123456'
DBNAME = 'dbtest'

db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
cur = db.cursor()
cur.execute("INSERT INTO Student(Name, Email, Age) VALUE (%s,%s,%s)",('Mike','123@163.com','20'))
db.commit()

查询表中的数据

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '123456'
DBNAME = 'dbtest'

db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
cur = db.cursor()
cur.execute("SELECT * FROM Student")
results = cur.fetchall()
for row in results:
    name=row[0]
    email=row[1]
    age=row[2]
    print('Name:%s,Email:%s,Age:%s'%(name,email,age))

更新表中的数据

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '123456'
DBNAME = 'dbtest'

db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
cur = db.cursor()
cur.execute("update Student set Name=%s where Name=%s", ('John', 'Mike'))
db.commit()