from django.db import load_backend, transaction, connection
#manipular datos de una bd externa
def sincronizeDB(self):
myBackend = load_backend('mysql') # or 'mysql', 'sqlite3', 'oracle'
myConnection = myBackend.DatabaseWrapper({
'DATABASE_HOST': '192.168.1.11',
'DATABASE_NAME': 'agenciaperu_local',
'DATABASE_OPTIONS': {},
'DATABASE_PASSWORD': "",
'DATABASE_PORT': "",
'DATABASE_USER': "root",})
# Now we can do all the standard raw sql stuff with myConnection.
myCursor = myConnection.cursor()
id = 22
name = "tecnologia para jos"
slug = "tecnologia_para_jos"
row = myCursor.execute("INSERT INTO category(name, slug ) values(%s,%s);", [name, slug])
row = myConnection._commit()
#row = transaction.rollback_unless_managed() -----> sería cuando trabajamos en local
# select simple
#row = myCursor.execute("select *from category where id = %s and highlight = %s;",[id,0])
myCursor.fetchall()
Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/2056"></script>
#terminal
C:\>mysql -uroot -p
mysql> SELECT version();
# cliente mysql
SELECT version();
Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1787"></script>
agregar una columna despues de otra
ALTER TABLE media add COLUMN status VARCHAR (20) after component
Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1780"></script>
#dump
mysqldump -hlocalhost -uuser -ppassword nomdatabase> nomdatabase.sql
#restore
mysql -hlocalhost -uuser -ppassword nomdatabase < nomdatabase
Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1775"></script>
a partir de mysql 5.0
SELECT TIMESTAMPDIFF(DAY,'2005-01-02','2006-01-08');
Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1772"></script>
mysqldump -h localhost -u user -ppassword -r/tmp/mysql_backup.data nomdatabase
mysql -h localhost -u user -ppassword nomdatabase < /tmp/mysql_backup.data
Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1761"></script>
mysql -u root -p
mysql> show databases;
Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1693"></script>
mysql -u root -p -D nom_de_la_base
mysql> status;
Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1692"></script>
Great circle distance
Great circle distance
Find the distance in kilometres between two points on the surface of the earth. This is just the sort of problem stored functions were made for. For a first order approximation, ignore deviations of the earth's surface from the perfectly spherical. Then the distance in radians is given by a number of trigonometric formulas. ACOS and COS behave reasonably:
COS(lat1-lat2)*(1+COS(lon1-lon2)) - COS(lat1+lat2)*(1-COS(lon1-lon2))
rads = ACOS( --------------------------------------------------------------------- )
2
We need to convert degrees latitude and longitude to radians, and we need to know the length in km of one radian on the earth's surface, which is 6378.388. The function:
set log_bin_trust_function_creators=TRUE;
DROP FUNCTION IF EXISTS GeoDistKM;
DELIMITER |
CREATE FUNCTION GeoDistKM( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
BEGIN
DECLARE pi, q1, q2, q3 FLOAT;
DECLARE rads FLOAT DEFAULT 0;
SET pi = PI();
SET lat1 = lat1 * pi / 180;
SET lon1 = lon1 * pi / 180;
SET lat2 = lat2 * pi / 180;
SET lon2 = lon2 * pi / 180;
SET q1 = COS(lon1-lon2);
SET q2 = COS(lat1-lat2);
SET q3 = COS(lat1+lat2);
SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );
RETURN 6378.388 * rads;
END;
|
DELIMITER ;
-- toronto to montreal (505km):
select geodistkm(43.6667,-79.4167,45.5000,-73.5833);
+----------------------------------------------+
| geodistkm(43.6667,-79.4167,45.5000,-73.5833) |
+----------------------------------------------+
| 505.38836669921875 |
+----------------------------------------------+Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1555"></script>
<?php
$dbhost = "servidor"; $dbuser = "usuario"; $dbpassword = "clave"; $dbname = "base_de_datos";
} ?>
Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1378"></script>
