# snip definition of Stuff class made using declarative_base
# snip creation of session object
for c in session.query(Stuff):
c.foo = c.foo + 1
session.flush()
session.commit()
This does the right thing, but it takes just under fifty times as long as the other two approaches. I presume that's because it has to bring all the data into memory before it can work with it.
Is there any way to generate the efficient SQL using SQLAlchemy's ORM? Or using any other python ORM? Or should I just go back to writing the SQL by hand?
This question is related to
python
sqlalchemy
(IIRC, commit() works without flush()).
I've found that at times doing a large query and then iterating in python can be up to 2 orders of magnitude faster than lots of queries. I assume that iterating over the query object is less efficient than iterating over a list generated by the all() method of the query object.
[Please note comment below - this did not speed things up at all].
programming a servo thru a barometer
You could define a mapping of air pressure to servo angle, for example:
def calc_angle(pressure, min_p=1000, max_p=1200): return 360 * ((pressure - min_p) / float(max_p - min_p)) angle = calc_angle(pressure)
This will linearly convert pressure values between min_p and max_p to angles between 0 and 360 (you could include min_a and max_a to constrain the angle, too).
To pick a data structure, I wouldn't use a list but you could look up values in a dictionary:
d = {1000:0, 1001: 1.8, ...} angle = d[pressure]
but this would be rather time-consuming to type out!
Is there a way to view two blocks of code from the same file simultaneously in Sublime Text?
In the nav go View => Layout => Columns:2 (alt+shift+2) and open your file again in the other pane (i.e. click the other pane and use ctrl+p filename.py)
It appears you can also reopen the file using the command File -> New View into File which will open the current file in a new tab
python variable NameError
I would approach it like this:
sizes = [100, 250] print "How much space should the random song list occupy?" print '\n'.join("{0}. {1}Mb".format(n, s) for n, s in enumerate(sizes, 1)) # present choices choice = int(raw_input("Enter choice:")) # throws error if not int size = sizes[0] # safe starting choice if choice in range(2, len(sizes) + 1): size = sizes[choice - 1] # note index offset from choice print "You want to create a random song list that is {0}Mb.".format(size)
You could also loop until you get an acceptable answer and cover yourself in case of error:
choice = 0 while choice not in range(1, len(sizes) + 1): # loop try: # guard against error choice = int(raw_input(...)) except ValueError: # couldn't make an int print "Please enter a number" choice = 0 size = sizes[choice - 1] # now definitely valid
Why my regexp for hyphenated words doesn't work?
A couple of things:
- Your regexes need to be anchored by separators* or you'll match partial words, as is the case now
- You're not using the proper syntax for a non-capturing group. It's
(?: not (:?
If you address the first problem, you won't need groups at all.
*That is, a blank or beginning/end of string.
Comparing a variable with a string python not working when redirecting from bash script
When you read() the file, you may get a newline character '\n' in your string. Try either
if UserInput.strip() == 'List contents':
or
if 'List contents' in UserInput:
Also note that your second file open could also use with:
with open('/Users/.../USER_INPUT.txt', 'w+') as UserInputFile: if UserInput.strip() == 'List contents': # or if s in f: UserInputFile.write("ls") else: print "Didn't work"
is it possible to add colors to python output?
IDLE's console does not support ANSI escape sequences, or any other form of escapes for coloring your output.
You can learn how to talk to IDLE's console directly instead of just treating it like normal stdout and printing to it (which is how it does things like color-coding your syntax), but that's pretty complicated. The idle documentation just tells you the basics of using IDLE itself, and its idlelib library has no documentation (well, there is a single line of documentation—"(New in 2.3) Support library for the IDLE development environment."—if you know where to find it, but that isn't very helpful). So, you need to either read the source, or do a whole lot of trial and error, to even get started.
Alternatively, you can run your script from the command line instead of from IDLE, in which case you can use whatever escape sequences your terminal handles. Most modern terminals will handle at least basic 16/8-color ANSI. Many will handle 16/16, or the expanded xterm-256 color sequences, or even full 24-bit colors. (I believe gnome-terminal is the default for Ubuntu, and in its default configuration it will handle xterm-256, but that's really a question for SuperUser or AskUbuntu.)
Learning to read the termcap entries to know which codes to enter is complicated… but if you only care about a single console—or are willing to just assume "almost everything handles basic 16/8-color ANSI, and anything that doesn't, I don't care about", you can ignore that part and just hardcode them based on, e.g., this page.
Once you know what you want to emit, it's just a matter of putting the codes in the strings before printing them.
But there are libraries that can make this all easier for you. One really nice library, which comes built in with Python, is curses. This lets you take over the terminal and do a full-screen GUI, with colors and spinning cursors and anything else you want. It is a little heavy-weight for simple uses, of course. Other libraries can be found by searching PyPI, as usual.
Get Public URL for File - Google Cloud Storage - App Engine (Python)
You need to use get_serving_url from the Images API. As that page explains, you need to call create_gs_key() first to get the key to pass to the Images API.
Real time face detection OpenCV, Python
Your line:
img = cv2.rectangle(img,(x,y),(x+w,y+h),(255,0,0),2)
will draw a rectangle in the image, but the return value will be None, so img changes to None and cannot be drawn.
Try
cv2.rectangle(img,(x,y),(x+w,y+h),(255,0,0),2)
xlrd.biffh.XLRDError: Excel xlsx file; not supported
As noted in the release email, linked to from the release tweet and noted in large orange warning that appears on the front page of the documentation, and less orange, but still present, in the readme on the repository and the release on pypi:
xlrd has explicitly removed support for anything other than xls files.
In your case, the solution is to:
make sure you are on a recent version of Pandas, at least 1.0.1,
and preferably the latest release. 1.2 will make his
even clearer.
install openpyxl: https://openpyxl.readthedocs.io/en/stable/
change your Pandas code to be:
df1 = pd.read_excel(
os.path.join(APP_PATH, "Data", "aug_latest.xlsm"),
engine='openpyxl',
Could not load dynamic library 'cudart64_101.dll' on tensorflow CPU-only installation
In a conda environment, this is what solved my problem (I was missing cudart64-100.dll:
Downloaded it from
dll-files.com/CUDART64_100.DLL
Put it in my conda environment at
C:\Users\<user>\Anaconda3\envs\<env name>\Library\bin
That's all it took! You can double check if it's working:
import tensorflow as tf
tf.config.experimental.list_physical_devices('GPU')
Examples related to orm
How to select specific columns in laravel eloquent
you can also used findOrFail() method here it's good to used
if the exception is not caught, a 404 HTTP response is automatically sent back to the user. It is not necessary to write explicit checks to return 404 responses when using these method not give a 500 error..
ModelName::findOrFail($id, ['firstName', 'lastName']);
Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
You forget the @ID above the userId
How to query between two dates using Laravel and Eloquent?
I know this might be an old question but I just found myself in a situation where I had to implement this feature in a Laravel 5.7 app. Below is what worked from me.
$articles = Articles::where("created_at",">", Carbon::now()->subMonths(3))->get();
You will also need to use Carbon
use Carbon\Carbon;
Laravel - Eloquent "Has", "With", "WhereHas" - What do they mean?
with() is for eager loading. That basically means, along the main model, Laravel will preload the relationship(s) you specify. This is especially helpful if you have a collection of models and you want to load a relation for all of them. Because with eager loading you run only one additional DB query instead of one for every model in the collection.
Example:
User > hasMany > Post
$users = User::with('posts')->get();
foreach($users as $user){
$users->posts; // posts is already loaded and no additional DB query is run
has() is to filter the selecting model based on a relationship. So it acts very similarly to a normal WHERE condition. If you just use has('relation') that means you only want to get the models that have at least one related model in this relation.
Example:
User > hasMany > Post
$users = User::has('posts')->get();
// only users that have at least one post are contained in the collection
WhereHas
whereHas() works basically the same as has() but allows you to specify additional filters for the related model to check.
Example:
User > hasMany > Post
$users = User::whereHas('posts', function($q){
$q->where('created_at', '>=', '2015-01-01 00:00:00');
})->get();
// only users that have posts from 2015 on forward are returned
How to Make Laravel Eloquent "IN" Query?
If you are using Query builder then you may use a blow
DB::table(Newsletter Subscription)
->select('*')
->whereIn('id', $send_users_list)
->get()
If you are working with Eloquent then you can use as below
$sendUsersList = Newsletter Subscription:: select ('*')
->whereIn('id', $send_users_list)
->get();
How to auto generate migrations with Sequelize CLI from Sequelize models?
While it doesn't auto generate, one way to generate new migrations on a change to a model is:
(assuming that you're using the stock sequelize-cli file structure where migrations, and models are on the same level)
(Same as Manuel Bieh's suggestion, but using a require instead of an import) In your migration file (if you don't have one, you can generate one by doing "sequelize migration:create") have the following code:
'use strict';
var models = require("../models/index.js")
module.exports = {
up: function(queryInterface, Sequelize) {
return queryInterface.createTable(models.User.tableName,
models.User.attributes);
down: function(queryInterface, Sequelize) {
return queryInterface.dropTable('Users');
Make a change to the User model.
Delete table from database.
Undo all migrations: sequelize db:migrate:undo:all
Re-migrate to have changes saved in db. sequelize db:migrate
How to fix org.hibernate.LazyInitializationException - could not initialize proxy - no Session
This happened to me when I was already using @Transactional(value=...) and was using multiple transaction managers.
My forms were sending back data that already had @JsonIgnore on them, so the data being sent back from forms was incomplete.
Originally I used the anti pattern solution, but found it was incredibly slow. I disabled this by setting it to false.
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=false
The fix was to ensure that any objects that had lazy-loaded data that weren't loading were retrieved from the database first.
Optional<Object> objectDBOpt = objectRepository.findById(object.getId());
if (objectDBOpt.isEmpty()) {
// Throw error
} else {
Object objectFromDB = objectDBOpt.get();
In short, if you've tried all of the other answers, just make sure you look back to check you're loading from the database first if you haven't provided all the @JsonIgnore properties and are using them in your database query.
Select the first 10 rows - Laravel Eloquent
The simplest way in laravel 5 is:
$listings=Listing::take(10)->get();
return view('view.name',compact('listings'));
How to make join queries using Sequelize on Node.js
In my case i did following thing. In the UserMaster userId is PK and in UserAccess userId is FK of UserMaster
UserAccess.belongsTo(UserMaster,{foreignKey: 'userId'});
UserMaster.hasMany(UserAccess,{foreignKey : 'userId'});
var userData = await UserMaster.findAll({include: [UserAccess]});
What is Persistence Context?
While @pritam kumar gives a good overview the 5th point is not true.
Persistence Context can be either Transaction Scoped-- the Persistence
Context 'lives' for the length of the transaction, or Extended-- the
Persistence Context spans multiple transactions.
https://blogs.oracle.com/carolmcdonald/entry/jpa_caching
JPA's EntityManager and Hibernate's Session offer an extended Persistence Context.
Examples related to sqlalchemy
How to install mysql-connector via pip
First install setuptools
sudo pip install setuptools
Then install mysql-connector
sudo pip install mysql-connector
If using Python3, then replace pip by pip3
How to delete a record by id in Flask-SQLAlchemy
Just want to share another option:
# mark two objects to be deleted
session.delete(obj1)
session.delete(obj2)
# commit (or flush)
session.commit()
http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#deleting
In this example, the following codes shall works fine:
obj = User.query.filter_by(id=123).one()
session.delete(obj)
session.commit()
How to write DataFrame to postgres table?
This is how I did it.
It may be faster because it is using execute_batch:
# df is the dataframe
if len(df) > 0:
df_columns = list(df)
# create (col1,col2,...)
columns = ",".join(df_columns)
# create VALUES('%s', '%s",...) one '%s' per column
values = "VALUES({})".format(",".join(["%s" for _ in df_columns]))
#create INSERT INTO table (columns) VALUES('%s',...)
insert_stmt = "INSERT INTO {} ({}) {}".format(table,columns,values)
cur = conn.cursor()
psycopg2.extras.execute_batch(cur, insert_stmt, df.values)
conn.commit()
cur.close()
ImportError: No module named MySQLdb
My issue is :
return __import__('MySQLdb')
ImportError: No module named MySQLdb
and my resolution :
pip install MySQL-python
yum install mysql-devel.x86_64
at the very beginning, i just installed MySQL-python, but the issue still existed. So i think if this issue happened, you should also take mysql-devel into consideration.
Hope this helps.
sqlalchemy IS NOT NULL select
In case anyone else is wondering, you can use is_ to generate foo IS NULL:
>>> from sqlalchemy.sql import column
>>> print column('foo').is_(None)
foo IS NULL
>>> print column('foo').isnot(None)
foo IS NOT NULL
SQLAlchemy create_all() does not create tables
You should put your model class before create_all() call, like this:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://login:pass@localhost/flask_app'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
def __init__(self, username, email):
self.username = username
self.email = email
def __repr__(self):
return '<User %r>' % self.username
db.create_all()
db.session.commit()
admin = User('admin', '[email protected]')
guest = User('guest', '[email protected]')
db.session.add(admin)
db.session.add(guest)
db.session.commit()
users = User.query.all()
print users
If your models are declared in a separate module, import them before calling create_all().
Say, the User model is in a file called models.py,
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://login:pass@localhost/flask_app'
db = SQLAlchemy(app)
# See important note below
from models import User
db.create_all()
db.session.commit()
admin = User('admin', '[email protected]')
guest = User('guest', '[email protected]')
db.session.add(admin)
db.session.add(guest)
db.session.commit()
users = User.query.all()
print users
Important note: It is important that you import your models after initializing the db object since, in your models.py _you also need to import the db object from this module.
How to execute raw SQL in Flask-SQLAlchemy app
SQL Alchemy session objects have their own execute method:
result = db.session.execute('SELECT * FROM my_table WHERE my_column = :val', {'val': 5})
All your application queries should be going through a session object, whether they're raw SQL or not. This ensures that the queries are properly managed by a transaction, which allows multiple queries in the same request to be committed or rolled back as a single unit. Going outside the transaction using the engine or the connection puts you at much greater risk of subtle, possibly hard to detect bugs that can leave you with corrupted data. Each request should be associated with only one transaction, and using db.session will ensure this is the case for your application.
Also take note that execute is designed for parameterized queries. Use parameters, like :val in the example, for any inputs to the query to protect yourself from SQL injection attacks. You can provide the value for these parameters by passing a dict as the second argument, where each key is the name of the parameter as it appears in the query. The exact syntax of the parameter itself may be different depending on your database, but all of the major relational databases support them in some form.
Assuming it's a SELECT query, this will return an iterable of RowProxy objects.
You can access individual columns with a variety of techniques:
for r in result:
print(r[0]) # Access by positional index
print(r['my_column']) # Access by column name as a string
r_dict = dict(r.items()) # convert to dict keyed by column names
Personally, I prefer to convert the results into namedtuples:
from collections import namedtuple
Record = namedtuple('Record', result.keys())
records = [Record(*r) for r in result.fetchall()]
for r in records:
print(r.my_column)
print(r)
If you're not using the Flask-SQLAlchemy extension, you can still easily use a session:
import sqlalchemy
from sqlalchemy.orm import sessionmaker, scoped_session
engine = sqlalchemy.create_engine('my connection string')
Session = scoped_session(sessionmaker(bind=engine))
s = Session()
result = s.execute('SELECT * FROM my_table WHERE my_column = :val', {'val': 5})
Flask-SQLAlchemy how to delete all rows in a single table
DazWorrall's answer is spot on. Here's a variation that might be useful if your code is structured differently than the OP's:
num_rows_deleted = db.session.query(Model).delete()
Also, don't forget that the deletion won't take effect until you commit, as in this snippet:
num_rows_deleted = db.session.query(Model).delete()
db.session.commit()
except:
db.session.rollback()
SQLAlchemy default DateTime
You can also use sqlalchemy builtin function for default DateTime
from sqlalchemy.sql import func
DT = Column(DateTime(timezone=True), default=func.now())
How to count rows with SELECT COUNT(*) with SQLAlchemy?
Addition to the Usage from the ORM layer in the accepted answer: count(*) can be done for ORM using the query.with_entities(func.count()), like this:
session.query(MyModel).with_entities(func.count()).scalar()
It can also be used in more complex cases, when we have joins and filters - the important thing here is to place with_entities after joins, otherwise SQLAlchemy could raise the Don't know how to join error.
For example:
we have User model (id, name) and Song model (id, title, genre)
we have user-song data - the UserSong model (user_id, song_id, is_liked) where user_id + song_id is a primary key)
We want to get a number of user's liked rock songs:
SELECT count(*)
FROM user_song
JOIN song ON user_song.song_id = song.id
WHERE user_song.user_id = %(user_id)
AND user_song.is_liked IS 1
AND song.genre = 'rock'
This query can be generated in a following way:
user_id = 1
query = session.query(UserSong)
query = query.join(Song, Song.id == UserSong.song_id)
query = query.filter(
and_(
UserSong.user_id == user_id,
UserSong.is_liked.is_(True),
Song.genre == 'rock'
# Note: important to place `with_entities` after the join
query = query.with_entities(func.count())
liked_count = query.scalar()
Complete example is here.
