python数据库-mysql

x33g5p2x  于2021-03-13 发布在 Python  
字(2.5k)|赞(0)|评价(0)|浏览(123)

第一步: python 与mysql接口

Windows 下安装:

http://www.codegood.com/downloads

Linux:

sudo aptitude install python-mysqldb -y

第二步:

安装 mysql

Linux :

    sudo aptitude install mysql-client -y
    sudo aptitude install mysql-server -y

Windows:

https://dev.mysql.com/downloads/installer/5.6.html

第三步:

安装 SQLAlchemy

Linux

sudo easy_install SQLAlchemy

Windows:

easy_install SQLAlchemy

第四步:

easy_install uliweb

Linux下在 easy_install 前加 sudo


sqlalchemy + mysql

from sqlalchemy import create_engine
engine = create_engine('mysql://username:password@127.0.0.1/foo')

sqlite

#Unix/Mac - 4 initial slashes in total
engine = create_engine('sqlite:////absolute/path/to/foo.db')
#Windows
engine = create_engine('sqlite:///C:\\path\\to\\foo.db')
#Windows alternative using raw string
engine = create_engine(r'sqlite:///C:\path\to\foo.db')

表和字段

metadata = MetaData()

user = Table('user', metadata,
    Column('user_id', Integer, primary_key = True),
    Column('user_name', String(16), nullable = False),
    Column('email_address', String(60), key='email'),
    Column('password', String(20), nullable = False)
)

user_prefs = Table('user_prefs', metadata,
    Column('pref_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
    Column('pref_name', String(40), nullable=False),
    Column('pref_value', String(100))
)

metadata.create_all(engine)
connection = engine.connect()

插入,更新

stmt = user.insert().values(name="some name")

stmt = user.update().where(user.c.id==5).values(name="some name")

stmt = user.delete(user.c.userid==1)

connection.execute(stmt)
stmt = user.select()
all = connection.execute(stmt)
for a in all:
	print a.user_name

orm

建立 engine

from sqlalchemy import create_engine
engine = create_engine('mysql://username:password@127.0.0.1/foo')

创建 Base

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(64))
     fullname = Column(String(64))
     password = Column(String(64))
Base.metadata.create_all(engine) 
#User.create(engine) #建立一个表
#User.drop(engine)   #删除一个表

session

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

新纪录

ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)
session.commit()

多条记录

session.add_all([item1, item2, item3])

查询

our_user = session.query(User)

删除

session.query(User).filter(User.id==3).delete()

更新

someobject = session.query(User).get(5)

# set 'value' attribute to a SQL expression adding one
someobject.name = "hello"

# issues "UPDATE some_table SET value=value+1"
session.commit()

相关文章