MySQL数据库操作

系统管理员 2022-12-11 03:16 130阅读 0赞

## 一、使用python调用数据库 ##

1.  导入包名
2.  创建连接
3.  创建游标对象
4.  编写sql语句并且执行
5.  输出结果
6.  关闭连接

import pymysql
    
    #一、创建链接
    user = 'root'
    host = 'localhost'
    password = 'root'
    port = 3306
    database = 'my_pymysql'
    cursorclass = pymysql.cursors.DictCursor
    db = pymysql.connect(host,user,password,database,cursorclass = cursorclass)
    
    #二、创建游标对象
    cursor = db.cursor()
    
    #三、编写sql语句并执行
    sql = 'show databases;'
    cursor.execute(sql)
    
    #四、输出结果
    # print(cursor.fetchone()) #单条输出,使用fetchone方法
    reslut = cursor.fetchall()
    print(reslut)
    
    #五、关闭链接
    cursor.close()
    db.close()

封装成类：

# -*- coding: utf-8 -*-
    import pymysql
    class Mysql:
        # 传参数
        def __init__(self,host,user,password,database,cursorclass = None):
            self.host = host
            self.user = user
            self.password = password
            self.database = database
            if cursorclass:
                self.cursorclass = cursorclass
            else:
                self.cursorclass = None
                
        def connect(self):
            self.db = pymysql.connect(self.host,self.user,self.password,self.database,cursorclass = self.cursorclass)
            self.cursor = self.db.cursor()
            
        def get_one(self,sql):
            result = 0
            try:
                self.connect()
                self.cursor.execute(sql)
                result = self.cursor.fetchone()
                self.close()
            except Exception as E:
                print("select error:",E)
            return result
            
        def get_all(self,sql):
            result = 0
            try:
                self.connect()
                self.cursor.execute(sql)
                result = self.cursor.fetchall()
                self.close()
            except Exception as E:
                print("select error:",E)
            return result
        def close(self):
            self.cursor.close()
            self.db.close()

新建一个文件demo调用刚才写的mysql类

# -*- coding: utf-8 -*-
    from Mysql import Mysql
    import pymysql
    host = 'localhost'
    user = 'root'
    password = 'root'
    database = 'my_pymysql'
    scursorclass = pymysql.cursors.DictCursor
    m = Mysql(host,user,password,database,scursorclass)
    sql = 'show databases'
    result = m.get_all(sql)
    if result:
        print(result)

## 二、创建数据表 ##

1.  导入包名
2.  创建连接
3.  创建数据表
4.  调用base.metadata.create\_all()方法

import sqlalchemy
    from sqlalchemy.ext.declarative import declarative_base
    
    #创建链接
    #mysql://scott:tiger@hostname/dbname
    db = sqlalchemy.create_engine("mysql://root:root@localhost/sqlorm")
    
    #创建基类
    base = declarative_base((db))
    
    #创建数据表
    class User(base):
        __tablename__ = 'user'
        id = sqlalchemy.Column(sqlalchemy.Integer,primary_key = True)
        name = sqlalchemy.Column(sqlalchemy.String(32))
        addree = sqlalchemy.Column(sqlalchemy.String(32))
        age = sqlalchemy.Column(sqlalchemy.Integer)
        
    class UserInfo(base):
        __tablename__ = 'userinfo'
        id = sqlalchemy.Column(sqlalchemy.Integer,primary_key = True)
        phone = sqlalchemy.Column(sqlalchemy.String(11))
        user = sqlalchemy.Column(sqlalchemy.Integer,sqlalchemy.ForeignKey('user.id'))
    
    if __name__=="__main__":
        base.metadata.create_all(db)

> 创建数据库  
> create database mysql\_name;

> 显示所有数据库  
> show databases;

> 显示表格  
> show tables;