SQLAlchemy增删改查基本操作,及SQL基本技能样码(join,group)

练了一天,基本的东东应该有感觉了。

#coding=utf-8

from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String, Boolean,
                        DateTime, ForeignKey, ForeignKey, create_engine)
from sqlalchemy import (insert, select, update, delete, text, desc, cast, and_, or_, not_)
from sqlalchemy.sql import func

metadata = MetaData()

cookies = Table('cookies', metadata,
                Column('cookie_id', Integer(), primary_key=True),
                Column('cookie_name', String(50), index=True),
                Column('cookie_recipe_url', String(255)),
                Column('cookie_sku', String(55)),
                Column('quantity', Integer()),
                Column('unit_cost', Numeric(12, 2))
                )

users = Table('users', metadata,
              Column('user_id', Integer(), primary_key=True),
              Column('username', String(15), nullable=False, unique=True),
              Column('email_address', String(255), nullable=False),
              Column('phone', String(20), nullable=False),
              Column('password' ,String(25), nullable=False),
              Column('created_on', DateTime(), default=datetime.now),
              Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
              )

orders = Table('orders', metadata,
               Column('order_id', Integer(), primary_key=True),
               Column('user_id', ForeignKey('users.user_id')),
               Column('shipped', Boolean(), default=False)
               )

line_items = Table('line_items', metadata,
                   Column('line_items_id', Integer(), primary_key=True),
                   Column('order_id', ForeignKey('orders.order_id')),
                   Column('cookie_id', ForeignKey('cookies.cookie_id')),
                   Column('quantity', Integer()),
                   Column('extended_cost', Numeric(12, 2))
                   )

engine = create_engine('mysql+pymysql://user:password@1.2.3.4:3306/cookies')
metadata.create_all(engine)
connection = engine.connect()
'''
s = select([cookies])
rp = connection.execute(s)
results = rp.fetchall()
print results

s = cookies.select()
rp = connection.execute(s)
# print rp.first()
for record in rp:
    print(record.cookie_name)

results = rp.fetchall()
first_row = results[0]
print first_row[1], first_row.cookie_name, first_row[cookies.c.cookie_name]

s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(desc(cookies.c.quantity))
s = s.limit(4)
rp = connection.execute(s)
print(rp.keys())
for cookie in rp:
    print('{} - {}'.format(cookie.quantity, cookie.cookie_name))

s = select([func.sum(cookies.c.quantity)])
rp = connection.execute(s)
print(rp.scalar())

# s = select([func.count(cookies.c.cookie_name)])
s = select([func.count(cookies.c.cookie_name).label('inventory_count')])
rp = connection.execute(s)
record = rp.first()
print(record.keys())
print(record.inventory_count

# s = select([cookies]).where(cookies.c.cookie_name == 'chocolate chip')
s = select([cookies]).where(cookies.c.cookie_name.like('%chocolate%'))
rp = connection.execute(s)
for record in rp.fetchall():
    print(record.cookie_name)

s = select([cookies.c.cookie_name, 'SKU-' + cookies.c.cookie_sku])
for row in connection.execute(s):
    print(row)

s = select([cookies.c.cookie_name,
            cast((cookies.c.quantity * cookies.c.unit_cost),
                 Numeric(12, 2)).label('inv_cost')])

for row in connection.execute(s):
    print('{} - {}'.format(row.cookie_name, row.inv_cost))

s = select([cookies]).where(
    and_(cookies.c.quantity.between(10, 50),
         cookies.c.cookie_name.contains('chip')
         )
    )
for row in connection.execute(s):
    print(row.cookie_name)

u = update(cookies).where(cookies.c.cookie_name == "chocolate chip")
u = u.values(quantity=(cookies.c.quantity + 120))
result = connection.execute(u)
print(result.rowcount)
s = select([cookies]).where(cookies.c.cookie_name == "chocolate chip")
result = connection.execute(s).first()
for key in result.keys():
    print('{:>20}: {}'.format(key, result[key]))

u = delete(cookies).where(cookies.c.cookie_name == "dark chocolate chip")
result = connection.execute(u)
print(result.rowcount)

s = select([cookies]).where(cookies.c.cookie_name == "dark chocolate chip")
result = connection.execute(s).fetchall()
print(len(result))

customer_list = [
    {
        'username': 'cookiemon',
        'email_address': 'mon@cookie.com',
        'phone': '111-111-1111',
        'password': 'password'
        },
    {
        'username': 'cakeeater',
        'email_address': 'cakeeater@cake.com',
        'phone': '222-222-2222',
        'password': 'password'
        },
    {
        'username': 'pieguy',
        'email_address': 'guy@pie.com',
        'phone': '333-333-3333',
        'password': 'password'
        }
    ]
ins = users.insert()
result = connection.execute(ins, customer_list)

ins = insert(orders).values(user_id=1, order_id=1)
result = connection.execute(ins)
ins = insert(line_items)

order_items = [
    {
        'order_id': 1,
        'cookie_id': 1,
        'quantity': 2,
        'extended_cost': 1.00
        },
    {
        'order_id': 1,
        'cookie_id': 3,
        'quantity': 12,
        'extended_cost': 3.00
        }
    ]

result = connection.execute(ins, order_items)
ins = insert(orders).values(user_id=2, order_id=2)
result = connection.execute(ins)
ins = insert(line_items)
order_items = [
    {
        'order_id': 2,
        'cookie_id': 1,
        'quantity': 24,
        'extended_cost': 12.00
        },
    {
        'order_id': 2,
        'cookie_id': 4,
        'quantity': 6,
        'extended_cost': 6.00
        }
    ]
result = connection.execute(ins, order_items)

columns = [orders.c.order_id, users.c.username, users.c.phone,
           cookies.c.cookie_name, line_items.c.quantity,
           line_items.c.extended_cost]
cookiemon_orders = select(columns)
cookiemon_orders = cookiemon_orders.select_from(orders.join(users).join(
    line_items).join(cookies)).where(users.c.username == 'cookiemon')
result = connection.execute(cookiemon_orders).fetchall()
for row in result:
    print(row)

columns = [users.c.username, func.count(orders.c.order_id)]
all_orders = select(columns)
all_orders = all_orders.select_from(users.outerjoin(orders))
all_orders = all_orders.group_by(users.c.username)
result = connection.execute(all_orders).fetchall()
for row in result:
    print(row)

def get_orders_by_customer(cust_name, shipped=None, details=False):
    columns = [orders.c.order_id, users.c.username, users.c.phone]
    joins = users.join(orders)
    if details:
        columns.extend([cookies.c.cookie_name,
               line_items.c.quantity,line_items.c.extended_cost])
        joins = joins.join(line_items).join(cookies)
    cust_orders = select(columns)
    cust_orders = cust_orders.select_from(joins)
    cust_orders = cust_orders.where(users.c.username == cust_name)
    if shipped is not None:
        cust_orders = cust_orders.where(orders.c.shipped == shipped)
    result = connection.execute(cust_orders).fetchall()
    for row in result:
        print(row)
    return result

get_orders_by_customer('cakeeater')
get_orders_by_customer('cakeeater', details=True)
get_orders_by_customer('cakeeater', shipped=True)
get_orders_by_customer('cakeeater', shipped=False)
get_orders_by_customer('cakeeater', shipped=False, details=True)

result = connection.execute("select * from orders").fetchall()
print(result)
'''
stmt = select([users]).where(text("username='cookiemon'"))
print(connection.execute(stmt).fetchall())

 

时间: 2024-11-01 22:33:08

SQLAlchemy增删改查基本操作,及SQL基本技能样码(join,group)的相关文章

MySQL数据库学习笔记(九)----JDBC的ResultSet接口(查询操作)、PreparedStatement接口重构增删改查(含SQL注入的解释)

[正文] 首先需要回顾一下上一篇文章中的内容:MySQL数据库学习笔记(八)----JDBC入门及简单增删改数据库的操作 一.ResultSet接口的介绍: 对数据库的查询操作,一般需要返回查询结果,在程序中,JDBC为我们提供了ResultSet接口来专门处理查询结果集. Statement通过以下方法执行一个查询操作: ResultSet executeQuery(String sql) throws SQLException  单词Query就是查询的意思.函数的返回类型是ResultSe

【黑马Android】(04)数据库的创建和sql语句增删改查/LinearLayout展示列表数据/ListView的使用和BaseAdater/内容提供者创建

数据库的创建和sql语句增删改查 1. 加载驱动. 2. 连接数据库. 3. 操作数据库.   创建表: create table person( _id integer primary key, name varchar(20), age integer );   添加: insert into person(name, age) values('lisi', 19);   删除: delete from person where _id = 1;   修改: update person se

sql2005-eclipse对数据库sql server2005的增删改查

问题描述 eclipse对数据库sql server2005的增删改查 package Frame; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.sql.*; import java.awt.*; import javax.swing.*; public class data extends JFrame implements ActionListener{ //priv

二、SQL语句映射文件(2)增删改查、参数、缓存

 二.SQL语句映射文件(2)增删改查.参数.缓存 2013-09-06 17:05:42 标签:配置文件 动态 元素 MyBatis学习 之 一.MyBatis简介与配置MyBatis+Spring+MySql MyBatis学习 之 二.SQL语句映射文件(1)resultMap MyBatis学习 之 二.SQL语句映射文件(2)增删改查.参数.缓存 MyBatis学习 之 三.动态SQL语句 MyBatis学习 之 四.MyBatis配置文件 2.2 select 一个select 元素

什么是Pro*C/C++,嵌入式SQL,第一个pro*c程序,pro*c++,Makefile,Proc增删改查

 1 什么是Pro*C/C++ 1.通过在过程编程语言C/C++中嵌入SQL语句而开发出的应用程序 2.什么是嵌入式SQL 1.在通用编程语言中使用的SQL称为嵌入式SQL 2.在SQL标准中定义了很多中语言的嵌入式SQL 3.各个厂商对嵌入式SQL的具体实现不同 3.什么是Pro*C/C++ 1.在C/C++语言中嵌入SQL语句而开发出的应用程序. 2.目的:使c/c++这种效率语言称为访问数据库的工具. 4.嵌入式SQL的载体是宿主语言 宿主语言          Pro程序 C/C++

C# 用Linq的方式实现对Xml文件的基本操作(创建xml文件、增删改查xml文件节点信息)

C# 用Linq的方式实现对Xml文件的基本操作(创建xml文件.增删改查xml文件节点信息)     http://www.cnblogs.com/mingmingruyuedlut/archive/2011/01/27/1946239.html    修改了一下里面一部分             try            {                //定义并从xml文件中加载节点(根节点)                XElement rootNode = XElement.L

Android编程连接MongoDB及增删改查等基本操作示例

本文实例讲述了Android编程连接MongoDB及增删改查等基本操作.分享给大家供大家参考,具体如下: MongoDB简介 Mongodb,分布式文档存储数据库,由C++语言编写,旨在为WEB应用提供可扩展的高性能数据存储解决方案.MongoDB是一个高性能,开源,无模式的文档型数据库,是当前NoSql数据库中比较热门的一种.它在许多场景下可用于替代传统的关系型数据库或键/值存储方式.Mongo使用C++开发. Mongo安装参考 1)下载安装包文件,解压到某一文件夹下. 官方下载地址:htt

asp.net mvc4 如何向mysql 中实现 增删改查操作

问题描述 asp.net mvc4 如何向mysql 中实现 增删改查操作 如题,最近在写一个基于asp.net mvc4网站,后台要求使用mysql数据库,现在的情况是数据库可以连通,单独的sql也会写,但是我不知道按照规范应该在哪写,以及如何通过方法写对应实体的增删改查这些个基本操作,还望有经验的高手能够指点一二! 解决方案 mysql也有entity provider,如果你用它,那么编程和使用sql server没有什么不同. 解决方案二: http://ju.outofmemory.c

[Android] SQLite数据库之增删改查基础操作

    在编程中经常会遇到数据库的操作,而Android系统内置了SQLite,它是一款轻型数据库,遵守事务ACID的关系型数据库管理系统,它占用的资源非常低,能够支持Windows/Linux/Unix等主流操作系统,同时能够跟很多程序语言如C#.PHP.Java等相结合.下面先回顾SQL的基本语句,再讲述Android的基本操作. 一. adb shell回顾SQL语句     首先,我感觉自己整个大学印象最深的几门课就包括<数据库>,所以想先回顾SQL增删改查的基本语句.而在Androi