连接postgresql
2024-08-28 10:40:57
# psycopg2
engine=create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')#
python 连接postgresql使用psycopg2作为默认的DBAPI
The first time a method like
Engine.execute()
orEngine.connect()
is called, the Engine
establishes a real DBAPI connection to the database, which is then used to emit the SQL. 1 from sqlalchemy.engine import create_engine
2 from sqlalchemy.schema import MetaData, Table, Column, ForeignKey, Sequence
3 from sqlalchemy.types import *
4
5 engine = create_engine('postgres://test:test@localhost/test', echo=True)
6
7 metadata = MetaData()
8 metadata.bind = engine
9
10 book_table = Table('book', metadata,
11 Column('id', Integer, Sequence('seq_pk'), primary_key=True),
12 Column('title', Unicode(255), nullable=False),
13 )
14
15 author_table = Table('author', metadata,
16 Column('id', Integer, Sequence('seq_pk'), primary_key=True),
17 Column('name', Unicode(255), nullable=False),
18 )
19
20 bookauthor_table = Table('bookauthor', metadata,
21 Column('book_id', Integer, ForeignKey('book.id'), nullable=False),
22 Column('author_id', Integer, ForeignKey('author.id'), nullable=False),
23)
24
25metadata.create_all(checkfirst=True)
2 from sqlalchemy.schema import MetaData, Table, Column, ForeignKey, Sequence
3 from sqlalchemy.types import *
4
5 engine = create_engine('postgres://test:test@localhost/test', echo=True)
6
7 metadata = MetaData()
8 metadata.bind = engine
9
10 book_table = Table('book', metadata,
11 Column('id', Integer, Sequence('seq_pk'), primary_key=True),
12 Column('title', Unicode(255), nullable=False),
13 )
14
15 author_table = Table('author', metadata,
16 Column('id', Integer, Sequence('seq_pk'), primary_key=True),
17 Column('name', Unicode(255), nullable=False),
18 )
19
20 bookauthor_table = Table('bookauthor', metadata,
21 Column('book_id', Integer, ForeignKey('book.id'), nullable=False),
22 Column('author_id', Integer, ForeignKey('author.id'), nullable=False),
23)
24
25metadata.create_all(checkfirst=True)
首先我们还是create_engine,然后新建一个MetaData对象,把engine绑上去,接下来,开始在metadata中定义表结构(metadata由Table构造函数传入),我们这里定义了3张表,分别是book、author和bookauthor关系表(“多对多”),其中新建一个Sequence对象,专门处理主键生成。最后我们通过执行metadata.create_all()创建数据库表,参数checkfirst=True表示如果数据库相关对象已经存在,则不重复执行创建。
对于已经存在于数据库中的表,我们可以通过传入autoload=True参数到Table构造函数的方式来加载现有的表结构到metadata中,而不必挨个儿再写一遍Column清单。
看到这儿,你也许觉得挺麻烦,不是么?Django和RoR都是可以直接定义数据model类,顺带就把schema也定义了,而不是像这样单独去写表结构的schema,显得很"底层"。确实,这样用SQLAlchemy并不是最优化的,SQLAlchemy本身并不会自动的帮你做很多事,但它基础打得很牢。如果你感兴趣,也可以先去看一下SQLAlchemy的扩展模块Elixir,通过Elixir,你可以像Ruby on Rails那样定义出实体和关系("Active Record")。
文/人世间(简书作者)
原文链接:http://www.jianshu.com/p/e6bba189fcbd
著作权归作者所有,转载请联系作者获得授权,并标注“简书作者”。
# -*- coding: utf-8 -*-
__author__ = 'ghost'
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
# 连接数据库
engine = create_engine("mysql://root:@localhost:3306/webpy?charset=utf8",encoding="utf-8", echo=True)
# 获取元数据
metadata = MetaData()
# 定义表
user = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(20)),
Column('fullname', String(40)),
)
address = Table('address', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', None, ForeignKey('user.id')),
Column('email', String(60), nullable=False)
)
# 创建数据表,如果数据表存在,则忽视
metadata.create_all(engine)
# 获取数据库连接
conn = engine.connect()
sqlalchemy连接postgresql数据库
import sqlalchemy
import pnosql
class Confsql:
def __init__(self,dbstr="postgresql+psycopg2://postgres:root@localhost:5432/Usermodel"):
self.engine = sqlalchemy.create_engine(dbstr, echo=True)
self.metadata = sqlalchemy.MetaData()
self.metadata.bind = self.engine
def runquery(self, sqlstr):
s = sqlstr
result = self.engine.execute(sqlstr)
rows = result.fetchall()
result.close()
需要对返回的数据进行修改才行
def runsp(self,sqlstr):
s = sqlstr
result = self.engine.execute(sqlstr)
rows = result.fetchall()
result.close()
result = []
for row in rows:
x = {}
x["barcode"] = row[0]
x["spcode"] = row[1]
x["spname"] = row[2]
x["spformat"] = row[3]
x["height"] = row[4]
x["width"] = row[5]
x["thickness"] = row[6]
x["comp"] = 'youke'
x["parentcomp"] = 'yz'
x["_id"] = str(uuid.uuid1())
result.append(x)
return result
SqlAlchemy应用
from sqlalchemy import create_engine,MetaData,Table,select
engine = create_engine('postgresql+psycopg2://postgres:root@localhost:5432/blogdb')
metadata = MetaData()
metadata.bind = engine
auth_permission = Table('auth_permission',metadata,autoload = True)
查询操作
def query_code(codename):
info = {'name':'','codename':''}
s = select([auth_permission.c.codename, auth_permission.c.name, ]).where(auth_permission.c.codename == codename)
codename_query = engine.execute(s)
for row in codename_query:
info['codename'] = row[0]
info['name'] = row[1]
codename_query.close()
return info
修改操作
#修改权限
def updata(codename,name):
s = auth_permission.update().where(auth_permission.c.codename == codename).values(name=name,codename=codename)
c = engine.execute(s)
c.close()
添加操作
# 添加权限
def add(codename,name,content_type_id):
s = auth_permission.insert().values(name=name,codename=codename,content_type_id=content_type_id)
c = engine.execute(s)
c.close()
删除操作
# 删除权限
def delete(codename):
s = auth_permission.delete().where(auth_permission.c.codename == codename)
c = engine.execute(s)
c.close()
最新文章
- angular-select绑定之后option不能更新问题
- jQuery学习总结
- KVC与KVO的实现原理
- 给大一的学弟学妹们培训java web的后台开发讨论班计划
- NYOJ题目98成绩转换
- ps命令使用 进程查看
- LPTHW 笨方法学python 18章
- SQL Server存储机制
- ZOJ 1002 Fire Net
- bpl
- js获取css属性方法
- EasyUI layout动态设置Split属性
- 工厂方法配置jdbc连接
- Git 命令速查表
- JavaSE 教程的选择
- Java IO流学习总结八:Commons IO 2.5-IOUtils
- java基础:学员状态查询
- Azure Devops/Tfs 编译的时候自动修改版本号
- python selenium 模块
- ASP.NET MVC - Entity Framework
热门文章
- boa web服务器
- 海康威视监控设备的Yv12视频在XNA框架中播放
- zabbix 源码安装
- lstrcpyn
- 蓝桥杯 第三届C/C++预赛真题(6) 大数乘法(数学题)
- jQuery左右选择框
- Redis "MISCONF Redis is configured to save RDB snapshots, but is currently not able to persist on disk"问题
- redis问题集
- :nth- 从1开始计数,其他如:eq()、 index()从0开始计数
- 170213、亿级Web系统搭建——单机到分布式集群