【python】sqlAlchemy

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.

resultProxy.fetchone()
resultProxy.first() # 返回第一行，然后关闭这次查询
resultProxy.scalar() # 返回第一行第一列，然后关闭这次查询

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实现没什么区别

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

from sqlalchemy import Column, Integer, String
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
                 self.name, self.fullname, self.password)   
# 注意事项：
# User继承之前创建的Base
# __tablename__指定表名
# Column 指定字段

DBSession = sessionmaker(bind=engine)
# 创建session对象:
session = DBSession()
# 创建新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对象

# 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:[email protected]: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')