Converting SQL commands to Python's ORM

Question

How would you convert the following codes to Python's ORM such as by SQLalchemy?

#1 Putting data to Pg

import os, pg, sys, re, psycopg2                                                       

#conn = psycopg2.connect("dbname='tkk' host='localhost' port='5432' user='noa' password='123'")

conn = psycopg2.connect("dbname=tk user=naa password=123")
cur = conn.cursor()
cur.execute("""INSERT INTO courses (course_nro)
            VALUES ( %(course_nro)s )""", dict(course_nro='abcd'))
conn.commit()

#2 Fetching

cur.execute("SELECT * FROM courses")
print cur.fetchall()

Examples about the two commands in SQLalchemy

insert

sqlalchemy.sql.expression.insert(table, values=None, inline=False, **kwargs)

select

sqlalchemy.sql.expression.select(columns=None, whereclause=None, from_obj=[], **kwargs)

When you read the SQLAlchemy site, what examples did they give of INSERT and SELECT. Please update this question to include the example code from the SQLAlchemy site. — S.Lott
– S.Lott, Commented Nov 22, 2009 at 17:40
So, you have the SQLAlchemy code. What -- specifically -- are you asking? You have the code. What's wrong with the code you have? — S.Lott
– S.Lott, Commented Nov 22, 2009 at 22:51

Markus Jarderot · Accepted Answer · 2009-11-22 17:40:23Z

1

After the initial declarations, you can do something like this:

o = Course(course_nro='abcd')
session.add(o)
session.commit()

and

print session.query(Course).all()

The declarations could look something like this:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import session_maker

# create an engine, and a base class
engine = create_engine('postgre://naa:123@localhost/tk')
DeclarativeBase = declarative_base(bind=engine)
metadata = DeclarativeBase.metadata

# create a session
Session = session_maker(engine)
session = Session()

# declare the models
class Cource(DelcarativeBase):
    __tablename__ = 'courses'

    course_nro = Column('course_nro', CHAR(12))

This declarative method is just one way of using sqlalchemy.

edited Nov 22, 2009 at 17:40

answered Nov 22, 2009 at 17:13

Markus Jarderot

89.7k23 gold badges141 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aaron Meier · Accepted Answer · 2013-04-30 13:51:03Z

0

Even though this is old, more examples can't hurt, right? I thought I'd demonstrate how to do this with PyORMish.

from pyormish import Model

class Course(Model):
    _TABLE_NAME = 'courses'
    _PRIMARY_FIELD = 'id' # or whatever your primary field is
    _SELECT_FIELDS = ('id','course_nro')
    _COMMIT_FIELDS = ('course_nro',)

Model.db_config = dict(
    DB_TYPE='postgres',
    DB_CONN_STRING='postgre://naa:123@localhost/tk'
)

To create:

new_course = Course().create(course_nro='abcd')

To select:

# return the first row WHERE course_nro='abcd'
new_course = Course().get_by_fields(course_nro='abcd')

answered Apr 30, 2013 at 13:51

Aaron Meier

9509 silver badges22 bronze badges

Collectives™ on Stack Overflow

Converting SQL commands to Python's ORM

#1 Putting data to Pg

#2 Fetching

Examples about the two commands in SQLalchemy

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

#1 Putting data to Pg

#2 Fetching

Examples about the two commands in SQLalchemy

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related