SQLALchemy之Python连接MySQL

weixin_ry5219775

已于 2022-11-17 16:07:49 修改

阅读量1.7k

点赞数 1

文章标签： python mysql 数据库

于 2020-03-30 14:01:00 首次发布

原文链接：https://blog.csdn.net/weixin_44080811/article/details/90030744

版权

20221117

mssql读出来是乱码的解决方案

df.商品名称 = df.商品名称.apply(lambda x: x.encode('latin-1').decode('gbk'))

https://blog.csdn.net/apple_50678962/article/details/123553645
pandas sqlalchemy 读mssql

https://blog.csdn.net/apple_50678962/article/details/123553645
sqlalchemy mssql 中文乱码

https://noxymgr5yr.feishu.cn/docx/MEZydGS2poUdQfxsc3Tckz89nFe
pymssql连接数据库

import pandas as pd
import pymysql
import pymssql

from tqdm import tqdm

import pandas as pd
import dask
import dask.dataframe as dd
from sqlalchemy import create_engine,MetaData
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects import postgresql
from sqlalchemy.dialects import mssql


 connection_format = 'mssql+pymssql://{0}:{1}@{2}/{3}?charset=utf8'
  db_user = 'sa'
  db_password = "Hzyyadmin@2020"
  from urllib import parse
  # 在连接前将特殊的密码转码再链接即可
  db_password = parse.quote_plus(db_password)
  db_host = '192.168.1.202:10009'
  db_name = 'DSJ_ZJK'
  connection_str = connection_format.format(db_user, db_password, db_host, db_name)
  engine = create_engine(connection_str, echo=True)

do = database_operate()
engine = do.connect2database('sqlserver')
connection = engine.connect()

result = connection.execute(sql_hezong)
result_df = []
for row in tqdm(result):
    result_df.append(row)

https://www.cnblogs.com/orge/p/14380405.html
sqlalchemy 读写SQL Server数据库

20220225

https://www.cnblogs.com/toheart/p/9802990.html
pymssql连接sqlserver

https://blog.csdn.net/qq_43103778/article/details/106862549
pymssql连接sqlserver中文乱码的问题

20210104

两种方式连接mysql
import pymysql
import pyodbc
class LuckyCat:
    def __init__(self):
        self.app_code = 'DEMO-1'
        self.app_name = 'A lucky cat'
  
    def connect(self):
        config={
                "host":"10.0.6.13",
                "port":3306,
                "user":"luckycat",
                "password":"bigdata",
                "database":"luckycat",
             }
        config["password"]="bigdata"
        return pymysql.connect(**config)
        # return pyodbc.connect(**config)
  
    def get_code(self):
        return self.app_code

    def get_name(self):
        return self.app_name;

    def print_me(self):
        print(self.app_code)

if __name__ == '__main__':
    me = LuckyCat()
    me.print_me()
###########################
#coding=utf-8
#author:wsy
Date: 2020 / 7 / 14

from sqlalchemy import create_engine
import pandas as pd
import numpy as np
import time
import os
import pandas.io.sql as psql
from 工程化处理.sc_server import luckyapp_luckycat as luckyapp
import time


class read_data_from_sql_cursor(object):

    def __init__(self):

        self.cat= luckyapp.LuckyCat()
        self.conn = self.cat.connect()

    def read_data(self, get_table_nr,get_table_col_name):

        try:
            # 检查连接是否断开，如果断开就进行重连
            self.conn.ping(reconnect=True)
            self.cursor = self.conn.cursor()
            sql_col_name = "SHOW COLUMNS FROM " +str(get_table_col_name)
            print('取列名sql {}'.format(sql_col_name))
            sql_nr = 'select * from ' +str(get_table_nr)
            print('取内容sql {}'.format(sql_nr))
            start=time.time()
            self.cursor.execute(sql_col_name)
            biao_col_name= self.cursor.fetchall()

            self.cursor.execute(sql_nr)
            biaonr= self.cursor.fetchall()
            biaonr = pd.DataFrame(list(biaonr))
            biaonr.columns = [i[0] for i in biao_col_name]
            end = time.time()
            print('耗费时间 {}'.format(round((end-start)/60,2)))
            return biaonr
        except Exception as e:
            print('取数失败原因是: {}'.format(e))
        finally:
            self.cursor.close()
            self.conn.close()


# rd=read_data_from_sql_cursor()
# result=rd.read_data('data_gongyinglian','data_gongyinglian')



class read_data_from_sql_pandas():

    def __init__(self,user,pwd,ip,port,db_name):

        self.user = 'luckycat'
        self.pwd = 'bigdata'
        self.ip = '10.0.6.13'
        self.port = '3306'
        self.db_name = 'luckycat'

    def duqusj(self,dulx,engine_utf8,engine_gbk):
        try:
            table = pd.read_sql(dulx, con=engine_utf8)
            return table
        except UnicodeDecodeError as u:
            try:
                table = pd.read_sql(dulx, con=engine_gbk)
                return table
            except UnicodeDecodeError as u:
                pass
        except Exception as sed:
            pass


    def begin_to_read(self,table_name):
        time1 = time.time()
        test="mysql+pymssql://" + self.user + ":" + self.pwd + "@" + self.ip + ":" + self.port + "/" + self.db_name

        os.environ["NLS_LANG"] = "GERMAN_GERMANY.UTF8"
        engine_utf8= create_engine("mysql+mysqlconnector://" + self.user + ":" + self.pwd + "@" + self.ip + ":" + self.port + "/" + self.db_name)
        engine_gbk= create_engine("mysql+mysqlconnector://" + self.user + ":" + self.pwd + "@" + self.ip + ":" + self.port + "/" + self.db_name)

        sql_biaosy = 'SELECT * FROM '+str(table_name) #上市公司
        print('sql语句 {}'.format(sql_biaosy))
        biaonr = self.duqusj(sql_biaosy, engine_utf8, engine_gbk)  # 表索引
        time2=time.time()
        print('读取时间 {0}'.format(round((time2-time1)/60),2))

        return biaonr

https://www.cnblogs.com/zenan/p/10176767.html
to_sql 参数

小白如何使用SQLALchemy简介
一、SQLALchemy简介
来自百度百科：双击跳转
SQLAlchemy是Python编程语言下的一款开源软件。提供了SQL工具包及对象关系映射（ORM）工具，使用MIT许可证发行。
SQLAlchemy“采用简单的Python语言，为高效和高性能的数据库访问设计，实现了完整的企业级持久模型”。SQLAlchemy的理念是，SQL数据库的量级和性能重要于对象集合；而对象集合的抽象又重要于表和行。因此，SQLAlchemy采用了类似于Java里Hibernate的数据映射模型，而不是其他ORM框架采用的Active Record模型。不过，Elixir和declarative等可选插件可以让用户使用声明语法。
SQLAlchemy首次发行于2006年2月，并迅速地在Python社区中最广泛使用的ORM工具之一，不亚于Django的ORM框架。

二、连接MySQL
连接数据库的语句是：‘数据库类型+数据库驱动名称://用户名:密码@IP地址:端口号/数据库名’，这是一条通用的语句。比如说，你要连接其他的数据库，只要修改数据库类型和驱动就行了，当然了用户名和密码这些也不一样。
在这里，我们首先需要手动的在数据中添加一个数据库。

create database blog;

下面的代码是使用Python语言，连接MySQL数据库的示例。

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://root:@ROOT_root_123/blog")
session = sessionmaker(bind=engine)
print(engine)
print(session)

如果出现下图结果，证明连接成功。
在这里插入图片描述
三、操作MySQL
下面，我将运用ORM技术提高我们编写SQL语句的效率，重要的是提高我们的编码效率。

新建数据库表
在开始操作之前，我们先来添加数据库表。

from sqlalchemy import String, Column, Integer
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://root:@ROOT_root_123@localhost:3306/blog?charset=utf8')
Session = sessionmaker(bind=engine)
Base = declarative_base()

class Student(Base):  # 必须继承declaraive_base得到的那个基类
    __tablename__ = "Students"  # 必须要有__tablename__来指出这个类对应什么表，这个表可以暂时在库中不存在，SQLAlchemy会帮我们创建这个表
    Sno = Column(String(10), primary_key=True)  # Column类创建一个字段
    Sname = Column(String(20), nullable=False, unique=True,
                   index=True)  # nullable就是决定是否not null，unique就是决定是否unique。。这里假定没人重名，设置index可以让系统自动根据这个字段为基础建立索引
    Ssex = Column(String(2), nullable=False)
    Sage = Column(Integer, nullable=False)
    Sdept = Column(String(20))

    def __repr__(self):
        return "<Student>{}:{}".format(self.Sname, self.Sno)

Base.metadata.create_all(engine)  # 这就是为什么表类一定要继承Base，因为Base会通过一些方法来通过引擎初始化数据库结构。不继承Base自然就没有办法和数据库发生联系了。

首先，我们就用create_engine函数新建一个连接，然后用declarative_base函数实例化一个数据库表的基类，之后所有的数据库表都要继承这个基类。上面，我们使用了String和Integer表示字符型和整型。其他常用的数据类型还有：Text、Boolean、SmallInteger 和 DateTime 。最后，调用Base类的metadata方法建立所有数据库表。其中，那个repr函数是方便测试用的，可加可不加。

会话
数据库操作的核心是新建一个会话session，或者叫做事务。在这之后，所有关于数据库的增删改查都要通过这个会话进行操作。

Session = sessionmaker(bind=engine)
session = Session()  # 实例化了一个会话（或叫事务），之后的所有操作都是基于这个对象的

既然是事务对象，session必然有以下这些方法

session.commit()  # 提交会话（事务）
session.rollback()  # 回滚会话
session.close()  # 关闭会话

其中，关于数据库中数据的对象在session中的四种状态。

session = Session()    #创建session对象
frank = Students(name='Frank')    #数据对象得到创建，此时为Transient状态
session.add(frank)    #数据对象被关联到session上，此时为Pending状态
session.commit()    #数据对象被推到数据库中，此时为Persistent状态
session.close()    #关闭session对象
print(frank.name)    #此时会报错DetachedInstanceError，因为此时是Detached状态。

一、增

student = Student(Sno='10001', Sname='Frnak', Ssex='M', Sage=22, Sdept='SFS')
session.add(student)
session.commit()  # 不要忘了commit
session.close()

还有一个添加多个数据项的方法:add_all。不过，要先自定义一个students列表。

session.add_all(students)
session.commit()
session.close()

二、查

session.query(Student).filter(Student.Sname == 'Frank').first()
session.query(Student).filter_by(Sname == 'Frank').first()

注意filter与filter_by的区别。

三、改

target = session.query(Student).filter(Student.Sname == "Kim").first()
target.Sname = "Kimmy"
session.commit()
session.close()

修改数据，先要找到目标数据。

四、删

# target = session.query(Student).get("10001")
# session.delete(target)
# session.commit()

一样，要删除数据，就要先找到目标数据。

四、一对多

from sqlalchemy import create_engine, Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('mysql+pymysql://root:@ROOT_root_123@localhost:3306/blog?charset=utf8')
Session = sessionmaker(bind=engine)
Base = declarative_base()


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(20), nullable=False)

    addresses = relationship('Address', backref='users')


class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    address = Column(String(20), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

请注意，设置外键的时候用的是表名.字段名。其实在表和表类的抉择中，只要参数是字符串，往往是表名；如果是对象则是表类对象。

五、一对一
看到这里，你可能会觉得很奇怪，为什么一对一要放在一对多后面才来介绍。其实，一对一是建立在一对多的基础之上的。

from sqlalchemy import create_engine, Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:@ROOT_root_123")
Session = sessionmaker(bind=engine)
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(20), nullable=False)

    addresses = relationship('Address', backref='users', uselist=False)


class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    address = Column(String(20), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

上面，通过一个uselist变量。把一对多的关系变成了一对一的关系。

六、多对多

from sqlalchemy import create_engine, Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('mysql+pymysql://root:@ROOT_root_123@localhost:3306/blog?charset=utf8')
Session = sessionmaker(bind=engine)
Base = declarative_base()
session = Session()


class Class(Base):
    __tablename__ = 'class'
    class_id = Column(Integer, primary_key=True)
    name = Column(String(20), nullable=False)
    class_teacher = relationship('ClassTeacher', backref='class')


class Teacher(Base):
    __tablename__ = 'teacher'
    teacher_id = Column(Integer, primary_key=True)
    name = Column(String(20), nullable=False)
    teacher_class = relationship('ClassTeacher', backref='teacher')


class ClassTeacher(Base):
    __tablename__ = 'class_teacher'  # 这就是所谓的一张视图表,没有实际存在数据，但是凭借关系型数据库的特点可以体现出一些数据关系
    teacher_id = Column(Integer, ForeignKey('teacher.teacher_id'), primary_key=True)
    class_id = Column(Integer, ForeignKey('class.class_id'), primary_key=True)
    # 这张第三表中有两个主键，表示不能有class_id和teacher_id都相同的两项

_class = session.query(Class).filter(Class.name == '三年二班').first()

for class_teacher_rel in _class.class_teacher:
    print(class_teacher_rel.teacher.name)

七、入门示例
新建MySQL数据库连接、创建基类以及创建会话

engine = create_engine('mysql+pymysql://root:@ROOT_root_123@localhost:3306/blog?charset=utf8')
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

2.创建数据库表

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(64), nullable=False, index=True)
    password = Column(String(64), nullable=False)
    email = Column(String(64), nullable=False, index=True)
    articles = relationship('Article', backref='author')
    userinfo = relationship('UserInfo', backref='user', uselist=False)

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.username)


class UserInfo(Base):
    __tablename__ = 'userinfos'

    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    qq = Column(String(11))
    phone = Column(String(11))
    link = Column(String(64))
    user_id = Column(Integer, ForeignKey('users.id'))


class Article(Base):
    __tablename__ = 'articles'

    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False, index=True)
    content = Column(Text)
    user_id = Column(Integer, ForeignKey('users.id'))
    cate_id = Column(Integer, ForeignKey('categories.id'))
    tags = relationship('Tag', secondary='article_tag', backref='articles')

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.title)


class Category(Base):
    __tablename__ = 'categories'

    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, index=True)
    articles = relationship('Article', backref='category')

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.name)


article_tag = Table(
    'article_tag', Base.metadata,
    Column('article_id', Integer, ForeignKey('articles.id')),
    Column('tag_id', Integer, ForeignKey('tags.id'))
)


class Tag(Base):
    __tablename__ = 'tags'

    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, index=True)

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.name)

其中，article_tag 表示Article中的tags的内容，主要通过一个secondary函数实现。
4. 创建数据
Faker模块，是一个不可多得的添加测试数据方面的榜首。它可以轻松的为我们添加各种各样的测试数据。

	faker = Factory.create()
    Session = sessionmaker(bind=engine)
    session = Session()

    faker_users = [User(
        username=faker.name(),
        password=faker.word(),
        email=faker.email(),
    ) for i in range(10)]
    session.add_all(faker_users)

    faker_categories = [Category(name=faker.word()) for i in range(5)]
    session.add_all(faker_categories)

    faker_tags = [Tag(name=faker.word()) for i in range(20)]
    session.add_all(faker_tags)

    for i in range(100):
        article = Article(
            title=faker.sentence(),
            content=' '.join(faker.sentences(nb=random.randint(10, 20))),
            author=random.choice(faker_users),
            category=random.choice(faker_categories)
        )
        for tag in random.sample(faker_tags, random.randint(2, 5)):
            article.tags.append(tag)
        session.add(article)

完整代码：

# coding: utf-8

import random
from faker import Factory

from sqlalchemy import create_engine, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy import Column, String, Integer, Text
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('mysql+pymysql://root:@ROOT_root_123@localhost:3306/blog?charset=utf8')
Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(64), nullable=False, index=True)
    password = Column(String(64), nullable=False)
    email = Column(String(64), nullable=False, index=True)
    articles = relationship('Article', backref='author')
    userinfo = relationship('UserInfo', backref='user', uselist=False)

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.username)


class UserInfo(Base):
    __tablename__ = 'userinfos'

    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    qq = Column(String(11))
    phone = Column(String(11))
    link = Column(String(64))
    user_id = Column(Integer, ForeignKey('users.id'))


class Article(Base):
    __tablename__ = 'articles'

    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False, index=True)
    content = Column(Text)
    user_id = Column(Integer, ForeignKey('users.id'))
    cate_id = Column(Integer, ForeignKey('categories.id'))
    tags = relationship('Tag', secondary='article_tag', backref='articles')

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.title)


class Category(Base):
    __tablename__ = 'categories'

    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, index=True)
    articles = relationship('Article', backref='category')

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.name)


article_tag = Table(
    'article_tag', Base.metadata,
    Column('article_id', Integer, ForeignKey('articles.id')),
    Column('tag_id', Integer, ForeignKey('tags.id'))
)


class Tag(Base):
    __tablename__ = 'tags'

    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, index=True)

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.name)


if __name__ == '__main__':
    Base.metadata.create_all(engine)

    faker = Factory.create()
    Session = sessionmaker(bind=engine)
    session = Session()

    faker_users = [User(
        username=faker.name(),
        password=faker.word(),
        email=faker.email(),
    ) for i in range(10)]
    session.add_all(faker_users)

    faker_categories = [Category(name=faker.word()) for i in range(5)]
    session.add_all(faker_categories)

    faker_tags = [Tag(name=faker.word()) for i in range(20)]
    session.add_all(faker_tags)

    for i in range(100):
        article = Article(
            title=faker.sentence(),
            content=' '.join(faker.sentences(nb=random.randint(10, 20))),
            author=random.choice(faker_users),
            category=random.choice(faker_categories)
        )
        for tag in random.sample(faker_tags, random.randint(2, 5)):
            article.tags.append(tag)
        session.add(article)

    session.commit()
    session.close()

八、总结
SQLAlchemy减轻了我们编写原生SQL的痛苦，但是，我们页牺牲了部分性能。还有，在将来我会继续编写Faker模块的介绍，期待读者朋友们的持续关注。

weixin_ry5219775

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQLALchemy之Python连接MySQL

engine_utf8= create_engine(“oracle://” + user + “:” + pwd + “@” + ip + “:” + port + “/” + db_name,encoding=‘utf-8’,max_overflow=5, echo=False)engine_gbk= create_engine(“oracle://” + user + “:” + pwd ...
复制链接

扫一扫