Created
June 14, 2019 06:37
-
-
Save marcbln/ea740c64e9843662f2fa1054f2a5ea69 to your computer and use it in GitHub Desktop.
testing mysql connector in python
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 06/2019 | |
# test mysql connector | |
# install with: pip3 install mysql-connector | |
import mysql.connector | |
mydb = mysql.connector.connect( | |
host="localhost", | |
user="root", | |
passwd="11111", | |
database="my_test_database" | |
) | |
print(mydb) | |
mycursor = mydb.cursor() | |
# ---- drop table | |
sql = "DROP TABLE IF EXISTS customers" | |
mycursor.execute(sql) | |
# ---- create table | |
mycursor.execute("CREATE TABLE `customers` (`name` VARCHAR(255), `address` VARCHAR(255))") | |
# ---- insert one | |
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" | |
val = ("John", "Highway 21") | |
mycursor.execute(sql, val) | |
mydb.commit() | |
print("1 record inserted, ID:", mycursor.lastrowid) | |
# ---- insert many | |
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" | |
val = [ | |
('Peter', 'Lowstreet 4'), | |
('Amy', 'Apple st 652'), | |
('Hannah', 'Mountain 21'), | |
('Michael', 'Valley 345'), | |
('Sandy', 'Ocean blvd 2'), | |
('Betty', 'Green Grass 1'), | |
('Richard', 'Sky st 331'), | |
('Susan', 'One way 98'), | |
('Vicky', 'Yellow Garden 2'), | |
('Ben', 'Park Lane 38'), | |
('William', 'Central st 954'), | |
('Chuck', 'Main Road 989'), | |
('Viola', 'Sideway 1633') | |
] | |
mycursor.executemany(sql, val) | |
mydb.commit() | |
print(mycursor.rowcount, "was inserted.") | |
# ---- fetch many | |
mycursor.execute("SELECT * FROM customers") | |
myresult = mycursor.fetchall() | |
for x in myresult: | |
print(x) | |
# ---- fetch one | |
mycursor.execute("SELECT * FROM customers LIMIT 1") | |
myresult = mycursor.fetchone() | |
print("one fetched: ", myresult) | |
# ---- delete | |
sql = "DELETE FROM customers WHERE address = %s" | |
adr = ("Yellow Garden 2", ) | |
mycursor.execute(sql, adr) | |
mydb.commit() | |
print(mycursor.rowcount, "record(s) deleted") | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment