Discuss / Python / code

船长杰克

#1 Created at ... [Delete] [Delete and Lock User]
# 导入:
import json

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

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    book = relationship('Book')

    def __str__(self):
        return 'user: id = %s,name = %s,books=%s' % (self.id, self.name, self.book)

    __repr__ = __str__


def tojson(self):
    if isinstance(self, User):
        return {
            'id': self.id,
            'name': self.name,
            'book': self.book
        }
    else:
        return {
            'id': self.id,
            'name': self.name,
            'user_id': self.user_id
        }

class Book(Base):
    __tablename__ = 'book'
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    user_id = Column(String(20), ForeignKey('user.id'))

    def __str__(self):
        return 'book: id = %s,name = %s,user_id = %s' % (self.id, self.name, self.user_id)

    __repr__ = __str__


engine = create_engine('mysql+mysqlconnector://root:@localhost:3306/test')
DBSession = sessionmaker(bind=engine)

# session=DBSession()
# new_user=User(id = '5',name = 'Bob')
# session.add(new_user)
# session.commit()
# session.close()


session = DBSession()

users = session.query(User).all()
print('user type :', type(users))
# print('id :',user.id)
# print('name :',user.name)
for u in users:
    # print('book type :',type(u.book))
    print(u)
print(users)
print('---------------------------')
u = session.query(User).filter(User.id == '5').one()
print(u)
print(json.dumps(users, default=tojson,indent=4))

==============

输出:
user type : <class 'list'>
user: id = 1,name = Michael,books=[book: id = 1,name = book1,user_id = 1, book: id = 2,name = book2,user_id = 1]
user: id = 2,name = Tracy,books=[book: id = 3,name = book3,user_id = 2, book: id = 4,name = book4,user_id = 2]
user: id = 3,name = Lily,books=[book: id = 5,name = book2,user_id = 3, book: id = 6,name = book2,user_id = 3, book: id = 7,name = book2,user_id = 3]
user: id = 5,name = Bob,books=[book: id = 10,name = book2,user_id = 5, book: id = 11,name = book2,user_id = 5, book: id = 8,name = book2,user_id = 5, book: id = 9,name = book2,user_id = 5]
[user: id = 1,name = Michael,books=[book: id = 1,name = book1,user_id = 1, book: id = 2,name = book2,user_id = 1], user: id = 2,name = Tracy,books=[book: id = 3,name = book3,user_id = 2, book: id = 4,name = book4,user_id = 2], user: id = 3,name = Lily,books=[book: id = 5,name = book2,user_id = 3, book: id = 6,name = book2,user_id = 3, book: id = 7,name = book2,user_id = 3], user: id = 5,name = Bob,books=[book: id = 10,name = book2,user_id = 5, book: id = 11,name = book2,user_id = 5, book: id = 8,name = book2,user_id = 5, book: id = 9,name = book2,user_id = 5]]
---------------------------
user: id = 5,name = Bob,books=[book: id = 10,name = book2,user_id = 5, book: id = 11,name = book2,user_id = 5, book: id = 8,name = book2,user_id = 5, book: id = 9,name = book2,user_id = 5]
[
    {
        "id": "1",
        "name": "Michael",
        "book": [
            {
                "id": "1",
                "name": "book1",
                "user_id": "1"
            },
            {
                "id": "2",
                "name": "book2",
                "user_id": "1"
            }
        ]
    },
    {
        "id": "2",
        "name": "Tracy",
        "book": [
            {
                "id": "3",
                "name": "book3",
                "user_id": "2"
            },
            {
                "id": "4",
                "name": "book4",
                "user_id": "2"
            }
        ]
    },
    {
        "id": "3",
        "name": "Lily",
        "book": [
            {
                "id": "5",
                "name": "book2",
                "user_id": "3"
            },
            {
                "id": "6",
                "name": "book2",
                "user_id": "3"
            },
            {
                "id": "7",
                "name": "book2",
                "user_id": "3"
            }
        ]
    },
    {
        "id": "5",
        "name": "Bob",
        "book": [
            {
                "id": "10",
                "name": "book2",
                "user_id": "5"
            },
            {
                "id": "11",
                "name": "book2",
                "user_id": "5"
            },
            {
                "id": "8",
                "name": "book2",
                "user_id": "5"
            },
            {
                "id": "9",
                "name": "book2",
                "user_id": "5"
            }
        ]
    }
]

Process finished with exit code 0

船长杰克

#2 Created at ... [Delete] [Delete and Lock User]

需要提前创建book表.并设置外键.

# 导入MySQL驱动:
import mysql.connector
# 注意把password设为你的root口令:
conn = mysql.connector.connect(user='root', password='', database='test')
cursor = conn.cursor()
# 创建user表:
cursor.execute('create table if not exists user (id varchar(20) primary key, name varchar(20))')
# 创建book表:
cursor.execute('create table if not exists book (id varchar(20) PRIMARY KEY, name varchar(20),user_id VARCHAR (20),FOREIGN KEY (user_id) REFERENCES user(id))')
# 插入一行记录,注意MySQL的占位符是%s:
# cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])
cursor.rowcount
# 提交事务:
conn.commit()
cursor.close()
# 运行查询:
cursor = conn.cursor()
cursor.execute('select * from user ')
values = cursor.fetchall()
print(values)

# 关闭Cursor和Connection:
cursor.close()
conn.close()
session.execute() #可以执行 sql 语句

  • 1

Reply