from sqlalchemy import create_engine
engine=create_engine("sqlite://", echo=False)

# 建表
engine.execute("create table users(userid char(10), username char(50))")

# 插入单条
resultProxy = engine.execute("insert into users (userid,username) values('user1','tony')")

# 批量插入
data = [(str(i), 'user' + str(i)) for i in range(5)]
engine.execute("insert into users (userid,username) values(?,?)", data)


# 查
resultProxy = engine.execute("select * from users")
resultProxy.fetchall()  # fetch 类的语句，只有 select 才可以用，其它报错
resultProxy.fetchmany(size=1)


resultProxy.close()  # resultProxy 用完之后, 需要close


resultProxy.rowcount  # return rows affected by an UPDATE or DELETE statement
resultProxy.returns_rows  # True if this ResultProxy returns rows.

import numpy as np
import pandas as pd
df = pd.DataFrame(np.random.rand(10, 5), columns=list('abcde'))

from sqlalchemy import create_engine
# engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/databasename?charset=utf8")
engine = create_engine('sqlite:///E:/test1.db',encoding='utf-8',echo=True)

# 写入数据库
df.to_sql("tablename",con = engine,index=False,if_exists='append',index_label=False)
# if_exists：如果存在，怎样.fail(default):报错。replace:替换。append:插入
# index:True(default),False.是否使用 df 的index ，作为index
# index_label:sql 的 index 字段名

# 读出数据库
pd.read_sql(sql='select * from tablename where e>:value', con=engine, params={'value':0.5})
# 注意，这里用params可以传递参数，好像跟format实现没什么区别

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = sqlalchemy.create_engine('sqlite:///:memory:')
Base = declarative_base()
# 创建session对象:
session = sessionmaker(bind=engine)()

# 1. 必须继承 Base
class User(Base):
    # 2. 定义表名
    __tablename__ = 'users'

    # 3. 定义字段
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String)
    fullname = sqlalchemy.Column(sqlalchemy.Integer)
    password = sqlalchemy.Column(sqlalchemy.String)

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
            self.name, self.fullname, self.password)


Base.metadata.create_all(engine)  # 创建表结构

# 创建新User对象:
new_user = User(id='5', name='Bob')
# 添加到session:
session.add(new_user)
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.close()

user = session.query(User).filter(User.id == '5').one()
# user是一个User对象

session.query(User).filter(User.id == '5').all()

# database URL 形式是 sqlite://<nohostname>/<path>
engine = create_engine('sqlite:///foo.db')

# 在Unix/Mac
engine = create_engine('sqlite:////absolute/path/to/foo.db')
# 在Windows
engine = create_engine('sqlite:///C:\\path\\to\\foo.db')
# 在Windows 中使用原始字符串
engine = create_engine(r'sqlite:///C:\path\to\foo.db')

# 使用内存
engine = create_engine('sqlite://')
engine = create_engine('sqlite:///:memory:')

# 默认情况(即使用psycopg2)
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')

# 使用psycopg2
engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')

# 使用pg8000
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')

# 默认情况（即使用mysql-python）
engine = create_engine('mysql://scott:tiger@localhost/foo')

# 使用mysql-python
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

# 使用MySQL-connector-python
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')

# 使用OurSQL
engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')

＃ 默认情况（即使用cx_oracle）
engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
# 使用cx_oracle
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')

# 使用pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

# 使用pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')