pysql.py

import os
from pprint import pprint
try:
	os.remove('pysqlite.db3')
except:
	pass
from sqlalchemy.schema import CreateTable

from sqlalchemy import create_engine,MetaData

engine = create_engine("sqlite:///pysqlite.db3",echo=False)
metadata = MetaData(engine)


from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Table

Base = declarative_base()


class User(Base):
	__tablename__ = "users"

	id = Column(Integer, primary_key=True)
	name = Column(String(64), unique=True)
	email = Column(String(64))

	def __init__(self, name, email):
		self.name = name
		self.email = email

query = Base.metadata.create_all(engine)
print(str(query))

from sqlalchemy.orm import sessionmaker

# 创建session
DbSession = sessionmaker(bind=engine)
session = DbSession()

user_table = Table('users', metadata, autoload=True)
add_user = User("test", "test123@qq.com")
query = dir(user_table)
pprint(query)
query = user_table.tablesample
query =CreateTable(user_table)
pprint(str(query))
query = user_table.insert()
print(str(query))
print(str(add_user))
session.add(add_user)
print(str(query))

query =  session.query(User).filter_by(id=1)
print(str(query))
users =query.all()
for item in users:
    print(item.name)

users = session.query(User).filter(User.id == 1).all()


session.query(User).filter_by(id=1).update({'name': "Jack"})