Python – 使用SQLAlchemy操作MySQL

Contents

1 安装环境
- 1.1 安装mysql-server
- 1.2 安装SQLAlchemy
2 一个demo的代码

安装环境

安装mysql-server

sudo apt-get update
sudo apt-get install -y mysql-server # 安装过程中将root密码设置为root
sudo apt-get -y install libmysqlclient-dev # 安装mysql的python库
/etc/init.d/mysql start # 启动mysql服务
mysql -u root -proot -e "set password for root@localhost = password('123456');"
# 设置mysql root密码

sudo apt-get update

sudo apt-get install -y mysql-server # 安装过程中将root密码设置为root

sudo apt-get -y install libmysqlclient-dev # 安装mysql的python库

/etc/init.d/mysql start # 启动mysql服务

mysql -u root -proot -e "set password for root@localhost = password('123456');"

# 设置mysql root密码

安装SQLAlchemy

pip install SQLAlchemy

1 2	pip install SQLAlchemy

一个demo的代码

#encoding=utf-8
import sys
import socket

reload(sys)
sys.setdefaultencoding("utf8")


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

SQLALCHEMY_DATABASE_URI = 'mysql://root:123456@localhost/test?charset=utf8'

engine = create_engine(SQLALCHEMY_DATABASE_URI, encoding='utf8', echo=False)

Base = declarative_base()

# 这句SQL可以更改表的编码方式
# ALTER TABLE students CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

class Students(Base):
    __tablename__ = "students"
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    sex = Column(Integer)
    number = Column(Integer)
    score = Column(Integer)

    def __init__(self, name, sex, number, score):
        self.sex = sex
        self.name = name
        self.number = number
        self.score = score

    __table_args__ = {
        'mysql_charset': 'utf8'
    }


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

newstu = Students('name', 1, 1001, 99)
session.add(newstu)
session.commit()
session.close()

#encoding=utf-8

import sys

import socket

reload(sys)

sys.setdefaultencoding("utf8")

from sqlalchemy import create_engine, Text

from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import String, Column, Integer

SQLALCHEMY_DATABASE_URI = 'mysql://root:123456@localhost/test?charset=utf8'

engine = create_engine(SQLALCHEMY_DATABASE_URI, encoding='utf8', echo=False)

Base = declarative_base()

# 这句SQL可以更改表的编码方式

# ALTER TABLE students CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

class Students(Base):

__tablename__ = "students"

id = Column(Integer, primary_key=True)

name = Column(String(20))

sex = Column(Integer)

number = Column(Integer)

score = Column(Integer)

def __init__(self, name, sex, number, score):

self.sex = sex

self.name = name

self.number = number

self.score = score

__table_args__ = {

'mysql_charset': 'utf8'

}

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

session = Session()

newstu = Students('name', 1, 1001, 99)

session.add(newstu)

session.commit()

session.close()