w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Connecting to locally postgresql using sqlalchemy
You have to create that database before you can create_engine from urlparse import urlparse, urlunparse def recreate_db(url): parsed = urlparse(url) #parse url so you know host host_url = urlunparse((parsed.scheme, parsed.netloc, '/', '', '', '')) #create_engine without database name engine = create_engine(host_url, convert_unicode=True) dbname = parsed.path.strip('/') engine.execute('commit') try: #drop (and clean) database if it exists with raw query engine.execute('drop database `%s`;'%dbname) engine.execute('commit') except OperationalError: pass #create database engine.execute('create database `%s` default character set utf8 ;'%dbname) engine.execute('commit') print 'Done cleanup'

Categories : Python

How to check if PostgreSQL schema exists using SQLAlchemy?
If you want to integrate it with SQLAlchemy you could use reflection but for an easier and quicker solution: from sqlalchemy.sql import exists, select exists(select([("schema_name")]).select_from("information_schema.schemata"). where("schema_name == 'foo'")) This will return True or False.

Categories : Python

How to represent a custom PostgreSQL domain in SQLAlchemy?
This likely far from a working solution, but I think the best way to do this would be subclass sqlalchemy.schema._CreateDropBase. from sqlalchemy.schema import _CreateDropBase class CreateDomain(_CreateDropBase): '''Represent a CREATE DOMAIN statement.''' __visit_name__ = 'create_domain' def __init__(self, element, bind=None, **kw): super(CreateDomain, self).__init__(element, bind=bind, **kw) class DropDomain(_CreateDropBase): '''Represent a DROP BASE statement.''' __visit_name__ = 'drop_domain' def __init__(self, element, bind=None, **kw): super(DropDomain, self).__init__(element, bind=bind, **kw) @compiles(CreateDomain, 'postgresql') def visit_create_domain(element, compiler, **kw): text = ' CREATE DOMAIN %s AS %s' % ( compiler.

Categories : Python

Set SQLAlchemy to use PostgreSQL SERIAL for identity generation
Use alter sequence foo_id_seq restart with 7600 should give you 7601 next time you call the sequence. http://www.postgresql.org/docs/current/static/sql-altersequence.html And then subsequent values. Just make sure that you restart it with a value > the last id.

Categories : Python

SQLAlchemy PostgreSQL Pyramid update issue
You need to bind to the item, you do not need to add a new Employee() instance: _id = request.matchdict['employeeid'] employee = DBSession.query(Employee).get(_id) form = Form(request, EmployeeSchema(), obj=employee) if form.validate(): form.bind(employee) return HTTPFound(location = request.route_url('employees')) That's it.

Categories : Python

sqlalchemy, postgresql and relationship stuck in "idle in transaction"
SQLA by default always operates in a transaction (some info here). In a web context most frameworks would handle committing this transaction for you at the end of the request (e.g. pyramid_tm). If you're not using a framework, or this is another type of application, you'll want to commit or rollback when you're finished, or at an appropriate point. It may be possible to configure SQLA such that it doesn't automatically start a transaction but as far as I can see it's not how it is intended to be used so you'll probably have better luck not trying to fight it :).

Categories : Python

sqlalchemy/postgresql: database column computed default value
I found my answer here. Details are below (using sqlalchemy): create_fn_my_tran_set_num = DDL( ''' CREATE OR REPLACE FUNCTION fn_my_tran_set_num() RETURNS TRIGGER AS $$ DECLARE last_transaction_id INTEGER; BEGIN last_transaction_id := MAX(transaction_id) FROM my_tran WHERE tenant_id = NEW.tenant_id; IF last_transaction_id IS NULL THEN NEW.transaction_id := 1; ELSE NEW.transaction_id := last_transaction_id + 1; END IF; RETURN NEW; END$$ LANGUAGE plpgsql ''') event.listen(MyTran.__table__, 'after_create', create_fn_my_tran_set_trx_id) create_tg_my_tran_set_num = DDL( ''' CREATE TRIGGER tg_my_tran_set_num BEFORE INSERT ON my_tran FOR EACH ROW EXECUTE PROCEDURE fn_my_tran_set_num(); ''') event.listen(MyTran.__table__, 'after_create', create

Categories : Postgresql

How to create a unique lowercase functional index using SQLAlchemy on PostgreSQL?
I have no idea why you want to index an integer column in lower case; The problem is that the generated sql does not typecheck: LINE 1: CREATE UNIQUE INDEX banana123 ON mytable (lower(col5)) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. 'CREATE UNIQUE INDEX banana123 ON mytable (lower(col5))' {} On the other hand, if you use an actual string type: Column('col5string', String), ... Index('banana123', func.lower(mytable.c.col5string), unique=True) The index is created as expected. If, for some very strange reason, you are insistent about this absurd index, you just need to fix the types: Index('lowercasedigits', func.lower(cast(mytable.c.col5, String)), unique=True) Whi

Categories : Postgresql

Iteratively insert rows into a postgreSQL database using SQLAlchemy in python
To make sure you can write to the table, try in more raw way: i.execute( "INSERT INTO table (field1, field2, field3) VALUES (%s, %s, %s) " % (data1, data2, data3) ) Modify the above to have appropriate table name, field names and data values. Once this works, you can refine writing from sh.

Categories : Python

Is it possible to store json stuff in database as json type and doing CRUD operation other than PostgreSQL ..?
I assume you are talking about storing JSON data in PostgresSQL. You can use JSON datatype and JSON functions that are built into the latest version. Here are some links that might help you to find detailed info about this - http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2#JSON_datatype http://www.postgresql.org/docs/9.3/static/functions-json.html And here's a previously asked question about the same topic - How do I query using fields inside the new PostgreSQL JSON datatype?

Categories : Json

Postgresql - Insert into where not exists using sqlalchemy's INSERT from SELECT
from sqlalchemy import * """ INSERT INTO example_table (id, name) SELECT 1, 'John' WHERE NOT EXISTS ( SELECT id FROM example_table WHERE id = 1 ); """ m = MetaData() example_table = Table("example_table", m, Column('id', Integer), Column('name', String) ) sel = select([literal("1"), literal("John")]).where( ~exists([example_table.c.id]).where(example_table.c.id == 1) ) ins = example_table.insert().from_select(["id", "name"], sel) print(ins) output: INSERT INTO example_table (id, name) SELECT :param_1 AS anon_1, :param_2 AS anon_2 WHERE NOT (EXISTS (SELECT example_table.id FROM example_table WHERE example_table.id = :id_1))

Categories : Python

How to use JSON operators on JSON typed column in postgresql 9.3
It appears that my mistake was using the to_json() function when inserting the data. That resulted in a JSON-encoded string containing my data. I did not find anything in the postgresql docs showing how to insert JSON data, but I did eventually find this post here that showed an example. I should have done: postgres=# insert into jtest (data) values ('{"k1": 1, "k2": "two"}'); INSERT 0 1 postgres=# select * from jtest; id | data ----+------------------------ 1 | {"k1": 1, "k2": "two"} (1 row) (note the lack of quotes on the value in the data column.) Now it works: postgres=# select * from jtest where data ->> 'k2' = 'two'; id | data ----+------------------------ 1 | {"k1": 1, "k2": "two"} (1 row)

Categories : Postgresql

Can't store array in json field in postgresql (rails) can't cast Array to json
Exec Summary: This is a known issue and is addressed in this pull request, which is awaiting merge as of this writing. Long version: Well, I can see why and how it's failing/succeeding based on Array/Hash. Here's the Rails method that's doing the type conversion (from quoting.rb) and it clearly doesn't support casting from Ruby Array to Postgres json while it does support casting from Hash. Also, I put some debugging code at the beginning of this method and found that it doesn't matter if you use flights or flights.to_json as the value, as the latter gets converted to the former for purposes of this casting. I'm going to do some more digging because I had no problem inserting the value of flights.to_json into a json column using psql. def type_cast(value, column, array_member = fals

Categories : Ruby On Rails

PostgreSQL: insert data into table from json
There's no easy way for json_populate_record to return a marker that means "generate this value". PostgreSQL does not allow you to insert NULL to specify that a value should be generated. If you ask for NULL Pg expects to mean NULL and doesn't want to second-guess you. Additionally it's perfectly OK to have a generated column that has no NOT NULL constraint, in which case it's perfectly fine to insert NULL into it. If you want to have PostgreSQL use the table default for a value there are two ways to do this: Omit that row from the INSERT column-list; or Explicitly write DEFAULT, which is only valid in a VALUES expression Since you can't use VALUES(DEFAULT, ...) here, your only option is to omit the column from the INSERT column-list: regress=# create table test (id serial primary k

Categories : Json

How do I modify fields inside the new PostgreSQL JSON datatype?
Sadly, I've not found anything in the documentation, but you can use some workaround, for example you could write some extended function. For example, in Python: CREATE or REPLACE FUNCTION json_update(data json, key text, value json) returns json as $$ from json import loads, dumps if key is None: return data js = loads(data) js[key] = value return dumps(js) $$ language plpython3u and then update test set data=json_update(data, 'a', to_json(5)) where data->>'b' = '2';

Categories : Json

Is it possible to transform a settings table with unique names into a json hash object in postgresql?
This should work in postgres-9.3. (untested, since I don't have 9.3 available here yet) DROP SCHEMA tmp CASCADE; CREATE SCHEMA tmp ; SET search_path=tmp; CREATE table pipo (name varchar NOT NULL PRIMARY KEY , value varchar); INSERT INTO pipo (name, value ) VALUES ('memory' , '10Mb'), ('disk' , '1Gb'), ('video' , '10Mpix/sec'), ('sound' , '100dB'); SELECT row_to_json( ROW(p.name,p.value) ) FROM pipo p ;

Categories : Json

SQLAlchemy-like relationships without SQLAlchemy
Not knowing the full context of the project that you are working on, but you can use SQLAlchemy to a sqlite database or even easier hold everything in memory. The only issue if you hold things in memory is that once you restart your process you will lose all the data. http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#connecting

Categories : Python

How to retrieve Postgresql Sequence-cache value from Postgresql Catalog tables?
You can query a sequence with its name as if it was a table. For example: CREATE SEQUENCE s CACHE 10; SELECT cache_value FROM s; Result: cache_value ------------- 10 (1 row) Or x SELECT * FROM s; Result: -[ RECORD 1 ]-+-------------------- sequence_name | s last_value | 1 start_value | 1 increment_by | 1 max_value | 9223372036854775807 min_value | 1 cache_value | 10 log_cnt | 0 is_cycled | f is_called | f

Categories : Postgresql

How can i solve PostgreSQL JDBC org.postgresql.util.PSQLException Error:
You are missing a space between del.libelledelegationar and FROM. This is causing the query to be parsed as: SELECT ...., del.libelledelegationarFROM dossier d .... In other words it sees a column del.libelledelegationarFROM with alias dossier and when the parser then sees d it trips, because it either expects a , or FROM, so it returns an error that there is a syntax error near d.

Categories : Java

Heroku PostgreSQL Crane DB vs Linode 1GB with PostgreSQL installed
Lower Heroku plans are on a shared server partitioned into containers with LXC. The details are on Heroku's site. Your plan appears to be one of them. This can actually be a win as discussed in this question if you happen to be on an instance where other users aren't putting much load on the server. It makes your performance less predictable though. The only good way to characterize performance is to benchmark with a simulation of your production workload. Whether the RAM actually matters or not depends on your data. If your frequently accessed data and indexes fit in RAM on one machine, but not on another, then the RAM difference will make a huge difference. If the data fits in RAM on both hosts there's little benefit to adding RAM. If the hot data won't fit in RAM on either machine th

Categories : Postgresql

Getting first row from sqlalchemy
Use query.one() to get one, and exactly one result. In all other cases it will raise an exception you can handle: from sqlalchemy.orm.exc import NoResultFound from sqlalchemy.orm.exc import MultipleResultsFound try: user = session.query(User).one() except MultipleResultsFound, e: print e # Deal with it except NoResultFound, e: print e # Deal with that as well There's also query.first(), which will give you just the first result of possibly many, without raising those exceptions. But since you want to deal with the case of there being no result or more than you thought, query.one() is exactly what you should use.

Categories : Python

json database get image setting data for json using while loop carousel type jquery json
Look for a jquery carousel plugin. http://vandelaydesign.com/blog/tools/jquery-carousel-plugins/ http://www.webdesigntunes.com/freebies/30-amazing-free-jquery-slider-and-carousel-plugins-of-2013/ Edit: does this help? $data = array(); $x = 0; while ($test = mysql_fetch_array($query)) { $data[$x]['content'] = "<div class='slide_inner'><a class='photo_link' href='#'><img class='photo' src='{$test['image']}' alt='Bike'></a><a class='caption' href='#'>Sample Carousel Pic Goes Here And The Best Part is that...</a></div>"; $data[$x]['content_button'] = "<div class='thumb'><img src='{$test['thumb']}' alt='bike is nice'></div><p>Agile Carousel Place Holder</p>"; $x++; } echo json_encode($data);

Categories : PHP

Using OR in SQLAlchemy ORM query
from sqlalchemy.sql import or_ query = MyTable.q.filter( or_(MyTable.id == some_number, MyTable.id == some_other_number) ) Of course, there's no point in this case since you can solve it with in_. query = MyTable.q.filter(MyTable.id.in_([some_number, some_other_number])

Categories : Python

SQLAlchemy and query
Use and_ from sqlalchemy import and_ variants = variants.filter_by(and_(cond_1,cond_2....cond_n)).distinct() See this page for further reference: http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html

Categories : Python

SQLAlchemy Query Possible?
Anything before the .all is reusable, but .all will trigger evaluation and set the result into stone. My advice is never store the .all if you intend to reuse the query, use it only when you need the data (this is the whole point of lazy evaluation, only fetch the data when you really need it). Instead of: list_of_games = session.query(Game_Table).all() list_of_games.filter(name=="stuff").all() Use: list_of_games = session.query(Game_Table) list_of_games.all() list_of_games.filter(name=="stuff").all() [update] So it sounds like there is no way to do what I want to do? – appleLover AFAIK you can only method-chain queries, not results. And it makes sense: why pay a round-trip to the database if the result is already in memory? Filtering/ordering it in pure Python will be less

Categories : Python

Example of what SQLAlchemy can do, and Django ORM cannot
This is dangerously close to being non-constructive, but I'll bite. Suppose we need to maintain inventories of certain items for a number of different, let's say, accounts. DDL follows: CREATE TABLE account ( id serial PRIMARY KEY, ... ); CREATE TABLE item ( id serial PRIMARY KEY, name text NOT NULL, ... ); CREATE TABLE inventory ( account_id integer NOT NULL REFERENCES account(id), item_id integer NOT NULL REFERENCES item(id), amount integer NOT NULL DEFAULT 0 CHECK (amount >= 0), PRIMARY KEY (account_id, item_id) ); First of all, Django ORM can't work with composite primary keys. Yes, you can always add a surrogate key and unique constraint, but that's one more column and one more index than you actually need. For a big table with a small numb

Categories : SQL

sqlalchemy and how to count row
Do you just want to get the count of final result? In that case, in fact, you just need only one query object instead of both query and rowCounter. query = self.dbObj.session.query(MaterialsTable) for attr , val in interfaceCodesObject.filterNameDict.items(): query = query.filter(and_(getattr(MaterialsTable,attr).like("%%%s%%" % val))) # all result rows query.all() # count of rows rowCounter = query.count() Hope this helps.

Categories : Python

SQLAlchemy many-to-many AND filter
I see two possibilites: Either combine two EXIST clauses that check for each element separately or specify a where clause like this: WHERE (SELECT COUNT(*) FROM ... WHERE tags.tag in ("foo", "bar")) = 2 Both solutions are kinda ugly but I see a problem here more with the way SQL is than with SQLAlchemy. In both cases I recommend you build a normal SQL query first, test it and them build that in SQLAlchemy equivalently. By the way, I could imagine both solutions being very inefficient on large sets of data, but I don't see a solution here. Initially, I would have tried something like WHERE (SELECT tags.tag FROM ...) = ("foo", "bar") but that does not seem to be valid SQL (at least MySQL threw an error at me) because the subquery in WHERE must return a scalar result.

Categories : Python

how to get backref name in sqlalchemy?
we're trying to encourage use of the inspect() api new in 0.8: >>> from sqlalchemy import inspect >>> inspect(User).attrs.keys() ['addresses', 'id'] >>> User.addresses.property.backref 'user'

Categories : Python

SQLAlchemy and Normalization
Table inheritance is what you are looking for: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine, Column, Integer, ForeignKey, String Base = declarative_base() class Account(Base): __tablename__ = 'account' id = Column(Integer, primary_key=True) person_id = Column(Integer, ForeignKey('person.id')) person = relationship("Person") class Person(Base): __tablename__ = 'person' id = Column(Integer, primary_key=True) name = Column(String(50)) type = Column(String(20)) __mapper_args__ = { 'polymorphic_on':type, 'polymorphic_identity':'base' } class NaturalPerson(Person): __mapper_args__ = { 'polymorphic_identity':'natural'

Categories : Python

python json.loads / json.load truncates nested json objects?
JSON doesn't care about the keys for objects, but the json.load and json.loads convert to a Python object using this conversion table. JSON objects are turned into python dict's, which means you can't have repeated keys.

Categories : Python

SQLAlchemy count followers to a tag
Change join() to outerjoin() and use coalesce() to change NULLs to 0s: tags = db.session.query( db.coalesce(db.func.count(models.followers.c.follower_id), 0).label('total'), models.Tag.name, models.Tag.title ).outerjoin(models.followers).group_by( models.Tag.name, models.Tag.title ).order_by('total DESC') That should do the trick.

Categories : Python

SQLAlchemy primary key assignement
You don't need to worry about the primary-key to create the Notification just pass the Event object to the Notification, and commit. You're good to go. SQLAlchemy doesn't assign the primary-key, it is the database that usually and implicitly does it for you, provided you have declared the table with something like this: id = Column(Integer, primary_key = True). class Event(Base): __tablename__ = "events" id = Column(Integer, primary_key = True) ... class Notification(Base): __tablename__ = "notifications" id = Column(Integer, primary_key = True) event_id = Column(Integer, ForeignKey("events.id")) event = relationship("Event") ... def __init__(self, event): self.event = event notification = Notification(Event()) session.add(notification) sess

Categories : Python

SQLAlchemy with on connection (python)
Here is a quote from docs: Most web frameworks include infrastructure to establish a single Session, associated with the request, which is correctly constructed and torn down corresponding torn down at the end of a request. Such infrastructure pieces include products such as Flask-SQLAlchemy, for usage in conjunction with the Flask web framework, and Zope-SQLAlchemy, for usage in conjunction with the Pyramid and Zope frameworks. SQLAlchemy strongly recommends that these products be used as available. In those situations where integration libraries are not available, SQLAlchemy includes its own “helper” class known as scoped_session. So, looks like scoped_session is what you are talking about. Documentation on how to use scoped sessions is pretty good. Basical

Categories : Python

how to query sqlalchemy by month name
You want to call the PostgresSQL function to_char, not the Python datetime function strftime: db_session.query(PaymentsMade) .filter(func.to_char(PaymentsMade.created_at, "FMMonth") == "August")

Categories : Python

Dynamic join sqlalchemy
tables_to_join = [..] if (len(tables_to_join) > 1): query = session.query(eval(tables_to_join[0])) for table in tables_to_join[1:]: query = query.join(eval(table))

Categories : Python

Can a just one sqlalchemy Session be a bottleneck for app?
The session machinery takes care of providing you with one connection per thread; since you can only execute sequential code within one thread that connection cannot become a bottleneck. Different parts of the code can request their own session; the session machinery will reuse session connections as required. This is not something you generally have to worry about, that's all handled for you by z3c.saconfig and its dependencies.

Categories : SQL

SQLAlchemy Insert w/ Dictionary
Try to replace: s.add(Game(n)) with: s.add(Game(**n)) Let's assume you have function like this: def foo(**kwargs): print [item for item in kwargs.items()] It expects keyword not positional arguments so this will work: foo(**{'foo': 1, 'bar': 2}) and this will fail: foo({'foo': 1, 'bar': 2}) For a better explanation you should read *args and **kwargs?

Categories : Python

How can I use SQLAlchemy to do "mysql explain"
Your filter call can be turned into a string: query = str(DBSession().query(Model).filter(***)) You can then use that to ask for a MySQL explanation: DBSession().execute('EXPLAIN ' + query) You may have to include any bound parameters the filters added to your query as a dictionary: DBSession().execute('EXPLAIN ' + query, {'param_1': 'value1', 'param_2': 'value2'})

Categories : Python

can't insert into one-to-many relationship in sqlalchemy
From the SQLAlchemy relationship API in the docs: That is, if the primaryjoin condition of this relationship() is a.id == b.a_id, and the values in b.a_id are required to be present in a.id, then the “foreign key” column of this relationship() is b.a_id. In your example, child.parent_id is required to be present in parent.id. So your "foreign key" column is child.parent_id. Therefore, changing: foreign_keys=[__table__.c.id] to this: foreign_keys=[Child.__table__.c.parent_id] should solve your problem.

Categories : Python



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.