python 数据库的连接与封装

数据库连接方法

mysql连接方法

pymysql包

import pymysql
conn=pymysql.connect(host='localhost',user='root',passwd='123',
                     charset='utf8',database='gethtml')
cursor=conn.cursor()
sql=' select * from stock_info limit 100'
cursor.execute(sql) #执行的sql
#conn.commit()  #提交至数据库，数据库数据的写入：updata,insert into
#a=cursor.fetchone() #等同于cursor.fetchmany(1)
a=cursor.fetchall() #得到的数据提取
print(a)

sqlserver连接

pymssql包

import pymssql
conn=pymysql.connect('localhost','sa','111','database')
cursor=conn.cursor()
sql='select * from table'
cursor.execute(sql)
a=cursor.fetchall()
print(a)

pymssql已停止更新服务
https://github.com/pymssql/pymssql/issues/668

pyodbc包连接sqlserver数据库

import pyodbc
conn=pyodbc.connect(driver='{sql server}',server='0.0.0.0',user='sa',pwd='xxx')
cursor = conn.cursor()
sql='Select * from sys.databases'
cursor.execute(sql)
a=cursor.fetchall()
print(a)

python 数据库封装

使用python的with来实现mysql数据库的操作
利用上下文管理器来封装的数据库，只需要传入密码和连接的数据库

from pymysql import connect
class db(object):
    def __init__(self,user,password,database):
        self.conn=connect(host='localhost',port=3306,user=str(user),password=str(password),database=str(database),charset='utf8')
        self.cs=self.conn.cursor()
    def __enter__(self):
        return self.cs
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cs.close()
        self.conn.close()

with db('root','123','python_data') as db:
    db.execute('select * from city_copy limit 10')
    data=db.fetchall()
    for content in data:
        print(content)