Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import simplejson
- from sqlobject import *
- # Replace this with the URI for your actual database
- connection = connectionForURI('sqlite:/:memory:')
- sqlhub.processConnection = connection
- # This defines the columns for your database table. See SQLObject docs for how it
- # does its conversions for class attributes <-> database columns (underscores to camel
- # case, generally)
- class Song(SQLObject):
- name = StringCol()
- artist = StringCol()
- album = StringCol()
- # Create fake data for demo - this is not needed for the real thing
- def MakeFakeDB():
- Song.createTable()
- s1 = Song(name="B Song",
- artist="Artist1",
- album="Album1")
- s2 = Song(name="A Song",
- artist="Artist2",
- album="Album2")
- def Main():
- # This is an iterable, not a list
- all_songs = Song.select().orderBy(Song.q.name)
- songs_as_dict = []
- for song in all_songs:
- song_as_dict = {
- 'name' : song.name,
- 'artist' : song.artist,
- 'album' : song.album}
- songs_as_dict.append(song_as_dict)
- print simplejson.dumps(songs_as_dict)
- if __name__ == "__main__":
- MakeFakeDB()
- Main()
- import json
- import psycopg2
- def db(database_name='pepe'):
- return psycopg2.connect(database=database_name)
- def query_db(query, args=(), one=False):
- cur = db().cursor()
- cur.execute(query, args)
- r = [dict((cur.description[i][0], value)
- for i, value in enumerate(row)) for row in cur.fetchall()]
- cur.connection.close()
- return (r[0] if r else None) if one else r
- my_query = query_db("select * from majorroadstiger limit %s", (3,))
- json_output = json.dumps(my_query)
- >>> json_output
- '[{"divroad": "N", "featcat": null, "countyfp": "001",...
- >>> j2 = query_db("select * from majorroadstiger where fullname= %s limit %s",
- ("Mission Blvd", 1), one=True)
- >>> j2 = json.dumps(j2)
- >>> j2
- '{"divroad": "N", "featcat": null, "countyfp": "001",...
- qry = "Select Id, Name, Artist, Album From MP3s Order By Name, Artist"
- # Assumes conn is a database connection.
- cursor = conn.cursor()
- cursor.execute(qry)
- rows = [x for x in cursor]
- cols = [x[0] for x in cursor.description]
- songs = []
- for row in rows:
- song = {}
- for prop, val in zip(cols, row):
- song[prop] = val
- songs.append(song)
- # Create a string representation of your array of songs.
- songsJSON = json.dumps(songs)
- import sqlite3
- import json
- DB = "./the_database.db"
- def get_all_users( json_str = False ):
- conn = sqlite3.connect( DB )
- conn.row_factory = sqlite3.Row # This enables column access by name: row['column_name']
- db = conn.cursor()
- rows = db.execute('''
- SELECT * from Users
- ''').fetchall()
- conn.commit()
- conn.close()
- if json_str:
- return json.dumps( [dict(ix) for ix in rows] ) #CREATE JSON
- return rows
- print get_all_users()
- [(1, u'orvar', u'password123'), (2, u'kalle', u'password123')]
- print get_all_users( json_str = True )
- [{"password": "password123", "id": 1, "name": "orvar"}, {"password": "password123", "id": 2, "name": "kalle"}]
- from contextlib import closing
- from datetime import datetime
- import json
- import MySQLdb
- DB_NAME = 'x'
- DB_USER = 'y'
- DB_PASS = 'z'
- def get_tables(cursor):
- cursor.execute('SHOW tables')
- return [r[0] for r in cursor.fetchall()]
- def get_rows_as_dicts(cursor, table):
- cursor.execute('select * from {}'.format(table))
- columns = [d[0] for d in cursor.description]
- return [dict(zip(columns, row)) for row in cursor.fetchall()]
- def dump_date(thing):
- if isinstance(thing, datetime):
- return thing.isoformat()
- return str(thing)
- with closing(MySQLdb.connect(user=DB_USER, passwd=DB_PASS, db=DB_NAME)) as conn, closing(conn.cursor()) as cursor:
- dump = {}
- for table in get_tables(cursor):
- dump[table] = get_rows_as_dicts(cursor, table)
- print(json.dumps(dump, default=dump_date, indent=2))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement