mySQLdb - Library has to be pip'ed' before and now to be imported
In [4]:
import MySQLdb
Database Connection Properties and Connect to the database
In [46]:
#Enter the values for you database connection
dsn_database = "verein" # e.g. "MySQLdbtest"
dsn_hostname = "localhost" # e.g.: "mydbinstance.xyz.us-east-1.rds.amazonaws.com"
dsn_port = 3306 # e.g. 3306
dsn_uid = "steinam" # e.g. "user1"
dsn_pwd = "steinam" # e.g. "Password123"
conn = MySQLdb.connect(host=dsn_hostname, port=dsn_port, user=dsn_uid, passwd=dsn_pwd, db=dsn_database)
Let's create a sample table and insert some data into it.
In [6]:
conn.query("""DROP TABLE IF EXISTS Cars""")
conn.query("""CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(20), Price INT)""")
conn.query("""INSERT INTO Cars VALUES(1,'Audi',52642)""")
conn.query("""INSERT INTO Cars VALUES(2,'Mercedes',57127)""")
conn.query("""INSERT INTO Cars VALUES(3,'Skoda',9000)""")
conn.query("""INSERT INTO Cars VALUES(4,'Volvo',29000)""")
conn.query("""INSERT INTO Cars VALUES(5,'Bentley',350000)""")
conn.query("""INSERT INTO Cars VALUES(6,'Citroen',21000)""")
conn.query("""INSERT INTO Cars VALUES(7,'Hummer',41400)""")
conn.query("""INSERT INTO Cars VALUES(8,'Volkswagen',21600)""")
conn.cursor will return a cursor object, you can use this cursor to perform queries.
In [53]:
cursor=conn.cursor()
cursor.execute("""SELECT * FROM Cars""")
cursor.fetchone()
Now you can print out the result set using pretty print: You should see the following results:
In [48]:
print("\nShow me the records:\n")
rows = cursor.fetchall()
import pprint
pprint.pprint(rows)
Am Ende schließen wir die Verbindung
In [41]:
conn.close()
'from: https://github.com/catherinedevlin/ipython-sql pip install ipython-sql has to be done
In [ ]:
%load_ext sql
In [54]:
%sql mysql://steinam:steinam@localhost/verein
Out[54]:
In [57]:
%sql select * from spieler;
Out[57]:
In [16]:
result = _
In [19]:
print(result[3])
In [29]:
%sql describe strafen;
Out[29]:
In [35]:
result = %sql SELECT Betrag, spielernr from strafen
In [36]:
%matplotlib inline
In [ ]:
result.bar()
Man kann auch in Markdown-Zellen mit Syntax-Highlightning arbeiten
SELECT *
FROM table_a AS a
LIMIT 10;